CODEWITHSUNDEEP
vbaexcel
user1632262
user1632262

Reputation: 79

Selecting first and last row at the same time in VBA

I'm trying to make a macro to select the first and the last row in my data range but I don't know how to make it so that the selection for both rows stay. This is what I tried to do:

Sub Macro6()
'
' Macro6 Macro

    ActiveWindow.SmallScroll Down:=15
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Rows(lr).Select
    Rows(1).Select


End Sub

I dont't really need a 'first row' code since my data always starts in Row 1. I should probably mention that I have never learned VBA before, but I'm kinda forced to learn it for work XD (got the above code by Googling). Just wondering if there is a method that will make both selections be highlighted at the same time. Thanks a lot!

Upvotes: 1

Views: 1863

Answers (1)

L42
L42

Reputation: 19737

Try this:

Dim lr As Long

With Sheets("Sheet1") '~~> change to suit
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1," & "A" & lr).EntireRow.Select
End With

Or like this:

.Range("1:1," & lr & ":" & lr).Select

To select non-contiguous ranges, you separate the string address by comma (within the string).

Range("A1,A5,A10").Select

If you use comma outside the string address:

Range("A1", "A5").Select

That is the same as:

Range("A1:A5").Select

Upvotes: 2

Related Questions