Excel VBA Select all cells down

Question

I'm looking to select every cell below a certain cell (not only in my list). I know of xldown, but this doesn't work in the instance of blanks, which occur in my list.

Is this possible?

This will get you to the last cell with data in a particular column, row, or the whole sheet: http://www.rondebruin.nl/win/s9/win005.htm you can use that combined with whatever code you are using to identify the starting cell to define the whole range you want selected. — CactusCake, Aug 06 '14 at 15:41
This should help: http://stackoverflow.com/questions/6301665/row-number-of-last-cell-with-data — Colonel Beauvel, Aug 06 '14 at 15:41

score 0 · Accepted Answer · answered Aug 06 '14 at 16:48

0

The follow will select all cells from A1 down to the last cell in column A that's not empty:

Sub select_all()
  Range(Cells(1, 1), Cells(Range("A1000000").End(xlUp).Row, 1)).Select
End Sub

You can modify the column / range to fit in your situation. Noted that it is hardcoded to assume 1000000 or less rows being used.

answered Aug 06 '14 at 16:48

Alex

1,632
1
12
28

@pnuts Pnuts I assume it would, it select the first cells down to the last used cell in the column. Are you suggesting andrejr might means something different? i.e. select all columns with mentioned requirement? Thanks! – Alex Aug 06 '14 at 17:35

score 0 · Answer 2 · answered Feb 17 '15 at 01:19

This will select A9 to column A the last row with data:

Range("A9:A" & range("A" & rows.Count).End(xlUp).Row).select

Just change A and 9 to whatever column and row you want.

I am interested to know what your next line of code is though as .select can most probably be ommited and the command can be performed directly without a select.

Excel VBA Select all cells down

2 Answers2