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.
