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

Question

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

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

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

ActiveChart.SeriesCollection(5).XValues = rng1

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

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

Answer 1

试试这个

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

但是请注意。如果从第 2 行开始没有数据，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