Dynamic column selection based on column header in VBA Excel

Question

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.

Answer 1

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