CODEWITHSUNDEEP
excelvba
user12059
user12059

Reputation: 733

Select range from A1 to end of file using VBA in Excel

I want to select all values in Excel 2007 worksheet between A1 and end of file (effect of ctrl End). There are always 4 columns but the rows will range from 2 to possibly hundreds. There will possibly be lots of blank cells throughout the selection, including the last cell.

The following just goes to the last cell to be selected, not the entire range. How can I modify this to accomplish what I want?

ActiveSheet.Range("A1", SpecialCells(xlLastCell)).Select

Many thanks.

Upvotes: 2

Views: 33711

Answers (3)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

You almost have it. The SpecialCells method needs a qualifier:

ActiveSheet.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96753

If you always want the first four columns, then perhaps:

Sub dural()
    Intersect(ActiveSheet.UsedRange, Range("A:D")).Select
End Sub

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Record a macro doing it then review the code:

Something like this may work.

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Running this macro selected the below in my example: enter image description here

Upvotes: 0

Related Questions