Variable Row Numbers

[Glynn Taylor]

Hello

Using Excel 2000

Having determined the first row with a blank cell A1 I calculate the first
and last rows which I need to copy to another sheet. see below

When I try to select these rows with Rows("first_row:last_row").Select
the macro fails.

Row = 3
While Cells(Row, 1) <> ""
Row = Row + 1
Wend

last_row = Row - 1
first_row = last_row - 11

Rows("first_row:last_row").Select

Any ideas?

Thank you

 

[Dave Peterson]

Try:
Rows(first_row & ":" & last_row).Select

Hello

Using Excel 2000

Having determined the first row with a blank cell A1 I calculate the first
and last rows which I need to copy to another sheet. see below

When I try to select these rows with Rows("first_row:last_row").Select
the macro fails.

Row = 3
While Cells(Row, 1) <> ""
Row = Row + 1
Wend

last_row = Row - 1
first_row = last_row - 11

Rows("first_row:last_row").Select

Any ideas?

Thank you

 

[Bob Phillips]

Rows(first_row & ":" & last_row).Select

 

[Rick Rothstein \(MVP - VB\)]

Anything between quote marks is considered text, even if those characters
spell out the name of a variable. The key is to concatenate the variable
names to the other fixed text...

Rows(first_row & ":" & last_row).Select

Rick

 

[Don Guillett]

A suggestion?

Sub copyrows()
Rows("3:" & Cells(Rows.Count, "a").End(xlUp).Row).Copy
End Sub

 

[Glynn Taylor]

Thanks Dave

Try:
Rows(first_row & ":" & last_row).Select

 

[Glynn Taylor]

Thanks Bob

Rows(first_row & ":" & last_row).Select

 

[Glynn Taylor]

Thanks Rick

Anything between quote marks is considered text, even if those characters
spell out the name of a variable. The key is to concatenate the variable
names to the other fixed text...

Rows(first_row & ":" & last_row).Select

Rick

 

[Glynn Taylor]

Thanks Don

A suggestion?

Sub copyrows()
Rows("3:" & Cells(Rows.Count, "a").End(xlUp).Row).Copy
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)