MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting a given number of columns using VBA


Posted by Phil on February 12, 2002 2:25 AM

Having selected to the bottom of a range, using:

Range(Selection, Selection.End(xlDown)).Select

I need to select ten columns. I guess I will need to use relative references as the range will be a different size (in terms of rows) each time.

I know this should be easy but can't find out how to do it!

A slap on the back from me to the first person with the answer.


Posted by JL on February 12, 2002 3:17 AM

I would find the end of the range (rows) using the following then select the desired columns. The code blow shows how.

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
lastrow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
EndofList = lastrow

location$="A1:J"&lastrow
range(location$).select

Posted by Phil on February 12, 2002 4:58 AM

Thanks JL.

Posted by Phil on February 12, 2002 4:58 AM

Thanks JL.

Posted by Parolles on February 12, 2002 5:13 AM

It depends upon what range of cells is required to be selected.
JL's suggestion assumes that you want to select a range starting from A1 down to the last row of the sheet's used range in column J.
If this is what you are lookimg for, then OK.
However, you may be looking for one of the following alternatives :-

Range(ActiveCell, ActiveCell.End(xlDown)).Resize(, 10).Select

OR

Intersect(ActiveSheet.UsedRange, Range(ActiveCell, Cells(65536, 256))).Resize(, 10).Select

OR

Dim lastrow#
lastrow = Range(ActiveCell, Cells(65536, ActiveCell.Column + 9)). _
Find(What:="*", After:=Cells(65536, ActiveCell.Column + 9), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range(ActiveCell, Cells(lastrow, ActiveCell.Column + 9)).Select

OR

Dim rng As Range, cell As Range, c%, x%
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
Set cell = rng(1, 1)
For x = 2 To 10
Set rng = Union(rng, Range(cell(1, x), cell(1, x).End(xlDown)))
Next
rng.Select

OR

Perhaps none of the above!

I would find the end of the range (rows) using the following then select the desired columns. The code blow shows how.

Posted by Phil on February 13, 2002 7:14 AM

Parolles - Your first (and simplest) solution is spot-on. Thanks very much. It depends upon what range of cells is required to be selected.