Difficulty selecting a range with variables

[Aelyn]

Hi all,

Sorry for asking such a simple question, but I'm trying to increase the efficiency of a macro I've written in Excel.

It's designed to copy an unknown number of rows from one sheet to another, and currently does so with a for...next loop:

For slann = Saurus To Skink Step -1
Sheet1.Select
Rows(slann).Select
Selection.Copy
Sheet6.Select
Rows("9").Select
Selection.Insert Shift:=x1Down
Next

The problem is that this is hugely inefficient, often taking over a minute to run, yet it would take almost no time if I could copy it without the loop, as a range of rows; effectively, something like this:

Rows("Saurus:Skink").Select
Selection.Copy
Sheet6.Select
Rows("9").Select
Selection.Insert Shift:=x1Down

Unfortunately, the line

Rows("saurus:Skink").Select

produces a type mismatch error, and I can't figure out the syntax to make it select a range in the way it does if I use code like

Rows("1:2").Select

I've tried every form of syntax I can think of, and none of it seems to work - everything either gives a type mismatch error when I run the macro, or a compile error.

EDIT: I have Saurus, Skink and Slann all declared as Long currently, if that makes a difference.

Any ideas on how I could get the effect I'm after? Should I be using an entirely different system to copy the lines across?

Thanks in advance.

 

[Pat Hartman]

You are more likely to get a useful answer if you post this to an Excel forum. Access and VBA have different object models and so Access people are unlikely to be able to help you with this problem.

 

[Aelyn]

My apologies, I found this forum through a search engine and I guess I didn't check the forum details properly.

 

[Jimmy the Hand]

Aelyn, you need to convert those row indices into strings. Try this

Rows(Cstr(saurus) & ":" & Cstr(Skink)).Select

 

[Pat Hartman]

I am proved wrong within hours. Yet again, this has proven to be the best forum anywhere We solve everyone's problems.