Selecting rows using defined reference as end row

[jk]

Hi everyone,

I need to select and copy a group of cells starting at row 3 and
continuing to the last row in my table. Since the last row can vary
between spreadsheets, I'm using a Loop function which finds the row
with value "Grand Total" (the last row) and defines that row number as
"RowCounter". Here's what it looks like:

Account = "Empty"
RowCounter = 0
Cells(RowCounter, 1).Activate

Do Until Account = "Grand Total"
Account = Worksheets("SOE All Roles").Cells(RowCounter,1).Value
If Account = "Grand Total" Then
RowCounter = RowCounter
Else
RowCounter = RowCounter + 1
End If

Cells(RowCounter, 1).Activate
Account = Worksheets("SOE All Roles").Cells(RowCounter,1).Value
Loop


This is leaving me with "RowCounter" defined as the last row, which is
what I wanted. Can anyone tell me how to now select all rows between
row 3 and my "RowCounter" row? I tried this, but I'm getting an
error:

Rows("3:RowCounter").Select

 

[Jim Cone]

Rows("3:" & RowCounter).Select
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"jk" <[email protected]>
wrote in message
Hi everyone,
I need to select and copy a group of cells starting at row 3 and
continuing to the last row in my table. Since the last row can vary
between spreadsheets, I'm using a Loop function which finds the row
with value "Grand Total" (the last row) and defines that row number as
"RowCounter". Here's what it looks like:

Account = "Empty"
RowCounter = 0
Cells(RowCounter, 1).Activate
Do Until Account = "Grand Total"
Account = Worksheets("SOE All Roles").Cells(RowCounter,1).Value
If Account = "Grand Total" Then
RowCounter = RowCounter
Else
RowCounter = RowCounter + 1
End If
Cells(RowCounter, 1).Activate
Account = Worksheets("SOE All Roles").Cells(RowCounter,1).Value
Loop

This is leaving me with "RowCounter" defined as the last row, which is
what I wanted. Can anyone tell me how to now select all rows between
row 3 and my "RowCounter" row? I tried this, but I'm getting an
error:
Rows("3:RowCounter").Select