Variable Row Numbers
 

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

Variable Row Numbers
 

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

Glynn Taylor wrote:

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

Variable Row Numbers
 

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

"Glynn Taylor" wrote in message
...
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

Variable Row Numbers
 

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" wrote in message
...
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

Variable Row Numbers
 

A suggestion?

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

"Glynn Taylor" wrote in message
...
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

Variable Row Numbers
 

Thanks Dave

"Dave Peterson" wrote:

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

Glynn Taylor wrote:

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

Variable Row Numbers
 

Thanks Bob

"Bob Phillips" wrote:

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

"Glynn Taylor" wrote in message
...
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

Variable Row Numbers
 

Thanks Rick

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Variable Row Numbers
 

Thanks Don

"Don Guillett" wrote:

A suggestion?

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

"Glynn Taylor" wrote in message
...
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