Excel VBA select range using cells and xlDown

Question

I am looping through a range in column A to find a heading, once found I need to select the next cell down to the last used cell.

Can't for the life of me select this range using Cells and End(xlDown)

For k = 1 To lastCell
    If Cells(k, 1).Value = rangeName Then
        Range(Range(Cells(k + 1, 1)), Range(Cells(k + 1, 1)).End(xlDown)).Select
    End If
Next k

I have tried Range(Cells(k + 1, 1), Cells(k + 1, 1).End(xlDown)), but no combination will work.

There are blank cells in column A, an example of the data is as so:

MONTH
Jan
Feb

AGE
18-21
22+

GENDER
Male
Female
Horse

How would I go about selecting this range, if rangeName was to equal GENDER for example.

Ralph Ralph · Accepted Answer · 2016-05-13T12:22:17

The following should work:

For k = 1 To lastCell
    If Cells(k, 1).Value = rangeName Then
        Range(Cells(k + 1, 1), Cells(k + 1, 1).End(xlDown)).Select
    End If
Next k

Yet, I'd suggest that you code a bit more explicitly to ensure it works:

With Worksheets("SheetYouAreWorkingOn")
    For k = 1 To lastCell
        If .Cells(k, 1).Value = rangeName Then
            .Range(.Cells(k + 1, 1), .Cells(k + 1, 1).End(xlDown)).Select
        End If
    Next k
End With

Tested with your sample data on an empty / new file:

Public Sub tmpSO()

Dim lastCell As Long
Dim rangeName As String

rangeName = "AGE"

With Worksheets("Sheet1")
    lastCell = .Cells(.Rows.Count, 1).End(xlUp).Row
    For k = 1 To lastCell
        If .Cells(k, 1).Value = rangeName Then
            .Range(.Cells(k + 1, 1), .Cells(k + 1, 1).End(xlDown)).Select
        End If
    Next k
End With

End Sub

Excel VBA select range using cells and xlDown

1 Answers