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

Question

我有以下代码可以根据标题选择列。

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

尝试使用此范围并在图表上设置X轴值时

ActiveChart.SeriesCollection(5).XValues = rng1

我看到标题也出现在列表中。

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

- angshuman sengupta

Answer 1

试试这个

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

然而需要注意的是，如果从第二行开始没有数据，xlDown 可能会给出意外结果。另外，如果未找到名称，则您所采用的方法将会出错。

话虽如此，这里有一种替代方法。

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

- Siddharth Rout

Answer 2

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

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

只是想稍微改进一下之前的答案！这个方法非常有效。

- Shrout1

---

或者，Set aCell 行也可以按照下面的方式编写，以便从当前工作表中更加动态地提取： Set aCell = .Range(Cells(i, 1), Cells(i, ActiveSheet.UsedRange.Columns.Count)).Find(What:=Name, LookAt:=xlWhole, MatchCase:=True) - Shrout1