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
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.
Just be aware that that will only work for sure if the selection consists of one contiguous block of cells.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.