基于VBA Excel中的列标题的动态列选择 [英] Dynamic column selection based on column header in VBA Excel

问题描述

  Dim rng1 As Range 
设置rng1 =范围（范围（A1：Z1）Find（Name），Range（A1：Z1）Find（Name）。End（xlDown））
  

尝试使用此范围并在图表上设置XValue时

  ActiveChart.SeriesCollection（5）.XValues = rng1 
  

我也看到了标题来自列表。

想要知道一种基于标题选择列的方法，然后从中删除标题元素。

解决方案

尝试这个

 设置rng1 =范围（_ 
范围（A1：Z1）Find（Name）偏移量（1）_ 
范围（A1：Z1）Find（Name）偏移量（1） （xlDown））
  

但是一个谨慎的话。如果没有第二行的数据， xlDown 可以给您意想不到的结果。而且，如果找不到名称，那么你所采取的方法会给你一个错误。

说完了，这里有一个替代方案

  Sub Sample（）
 Dim ws As Worksheet 
 Dim lRow As Long 
 Dim aCell As Range，rng1 As Range 
 
'~~>将此设置为相关工作表
设置ws = ThisWorkbook.Sheets（Sheet1）
 
带有ws 
'~~>找到名为
的单元格设置aCell = .Range（A1：Z1）。Find（Name）
 
'~~>如果找到单元格
如果不是aCell是没有
'~~>获取该列中的最后一行，并检查最后一行是否为> 1 
 lRow = .Range（Split（.Cells（，aCell.Column）.Address，$）（1）& .Rows.Count）.End（xlUp）.Row 
 
如果lRow> 1然后
'~~>设置你的范围
设置rng1 = .Range（aCell.Offset（1），.Cells（lRow，aCell.Column））
 
'~~>这将给你的地址
 Debug.Print rng1.Address 
如果
结束如果
结束
结束Sub 
  

I have the following code to select a column based on header.

Dim rng1 As Range
Set rng1 = Range(Range("A1:Z1").Find("Name"), Range("A1:Z1").Find("Name").End(xlDown))

When trying to use this range and set the XValue's on a chart

ActiveChart.SeriesCollection(5).XValues = rng1

I see the header too comes in the list.

Wanted to know a way to select a column based on header and then remove the header element from it.

解决方案

Try this

Set rng1 = Range( _
                 Range("A1:Z1").Find("Name").Offset(1), _
                Range("A1:Z1").Find("Name").Offset(1).End(xlDown))

However a word of caution. xlDown can give you unexpected results if there is no data from the 2nd Row onwards. Also the approach that you are taking will give you an error if the name is not found.

Having said that, here is an alternative

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim aCell As Range, rng1 As Range

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find the cell which has the name
        Set aCell = .Range("A1:Z1").Find("Name")

        '~~> If the cell is found
        If Not aCell Is Nothing Then
            '~~> Get the last row in that column and check if the last row is > 1
            lRow = .Range(Split(.Cells(, aCell.Column).Address, "$")(1) & .Rows.Count).End(xlUp).Row

            If lRow > 1 Then
                '~~> Set your Range
                Set rng1 = .Range(aCell.Offset(1), .Cells(lRow, aCell.Column))

                '~~> This will give you the address
                Debug.Print rng1.Address
            End If
        End If
    End With
End Sub

这篇关于基于VBA Excel中的列标题的动态列选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！