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 · Accepted Answer · 2016-05-13 12:22:17Z

3

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

answered May 13, 2016 at 12:22

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Wilkinson Over a year ago

Thank you for the answer and extra info, this is working and I implimented the explicit sample you provided.

Collectives™ on Stack Overflow

Excel VBA select range using cells and xlDown

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related