vba 基于VBA Excel中列标题的动态列选择

Dynamic column selection based on column header in VBA Excel

提问by angshuman sengupta

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

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

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.

想知道一种根据标题选择列然后从中删除标题元素的方法。

回答by Siddharth Rout

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. xlDowncan 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.

不过要注意一点。xlDown如果从第 2 行开始没有数据，可能会给您带来意想不到的结果。如果未找到名称，您所采用的方法也会给您一个错误。

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

回答by Shrout1

Just a revision to Siddharth's Answer (which was excellent). This code will iterate through all the rows in the specified sheet until it finds the row with the specified column header:

只是对悉达多的答案的修订（非常好）。此代码将遍历指定工作表中的所有行，直到找到具有指定列标题的行：

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

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

'Iterate through the rows until the target name is found
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
        With ws
            '~~> Find the cell which has the name - build range with current iterator
            Set aCell = .Range("A" & i & ":Z" & i).Find("Name")

            '~~> If the cell is found
            If Not aCell Is Nothing Then
                'Set iterator equal to rows to satisfy For...Next
                i = ActiveSheet.UsedRange.Rows.Count
                 '~~> 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
    Next i
End Sub

Just wanted to slightly improve on the previous answer! This works quite well.

只是想稍微改进一下之前的答案！这很有效。