PDA

View Full Version : Rows in selection



KentNoble
08-17-2007, 07:23 AM
HI all,
I am new to Excel VBA but have done a fair bit in Access. I need to iterate through each row in the current selection.
Do Until lngRow > lngLastRow
lngRow = lngRow + 1
loop
How can I get the first row and last row number of the selection?

RichardSchollar
08-17-2007, 07:36 AM
Hi

You could instead use a For Each Next construct:


For Each rw in Selection.Rows
'do something eg rw.ColorIndex = 3
Next rw

otherwise Selection.Row will return the first row in the selection, and Selection.Rows.Count will return the number of rows in the selection (so add that to the first row less one gives you the final row).

Richard

rory
08-17-2007, 07:38 AM
Hi,
You can use something like:
Dim rngArea As Range, rngRow As Range
For Each rngArea In Selection.Areas
For Each rngCode In rngArea.Rows
' Debug.Print rngCode.Row
Next rngRow
Next rngArea

KentNoble
08-17-2007, 07:49 AM
Thanks guys -
lngRow = Selection.Row
lngLastRow = lngRow + Selection.Rows.Count
did the job quite nicely thankyou.

rory
08-17-2007, 07:58 AM
Just be aware that that will only work for sure if the selection consists of one contiguous block of cells.