Simple but dynamic Range...Select statement

[Ivan Howard]

Hi All,

In Cell A1, I have a count of the number of rows within a dataset, starting in A3. For this example, lets say that there is 1000 rows of data.

What I need to do is be able to create VBA code that allows the syntax to change as and when the number of rows does. The reason for this is that I am working with different size datasets and formatting 20000 rows when I only need to format 1000 can take a lot of processing time. I want to be as efficient as I can.

For example, the following code:

Range("A3:Z1000").Select

...needs to be work like this (although this doesn't work):

Dim TOTALROWS as Double
TOTALROWS = Sheets("Sheet1").Range("A1").Value
Range("A3:Z & TOTALROWS & ").Select

Would anyone be able to help me?

Thanks very much.

 

[kpark91]

Something like this

Range("A3:Z" & TOTALROWS).Select

Just make sure you initialize the value of TOTALROWS.

 

[Ivan Howard]

Thanks very much kpark91.

I tried it, but thought some quote marks needed to be on the right of the variable.

Thanks very much.