Hi,

I'm trying to automate some tasks to run on a data set that is
imported in Excel via SQL.

I recorded a macro that Autofills a formula down through the set of
data, however I don't know how to deal with a variable number of rows
within the data.
The macro was recorded on a small sample set, (approx 400 rows) but
when I then tried the full data set it was closer to 40000 rows.

Looking at the code I find this code snippit:
Selection.AutoFill Destination:=Range("I2:I431"), Type:=xlFillDefault

Is there a way in which in which I can autofill to the extent of the
data in, say, column B?

TIA
Keith

The line below will return the last used row in a range.

LastRowColB = Range("B65536").End(xlUp).Row

"keith" wrote:

> Hi,
>
> I'm trying to automate some tasks to run on a data set that is
> imported in Excel via SQL.
>
> I recorded a macro that Autofills a formula down through the set of
> data, however I don't know how to deal with a variable number of rows
> within the data.
> The macro was recorded on a small sample set, (approx 400 rows) but
> when I then tried the full data set it was closer to 40000 rows.
>
> Looking at the code I find this code snippit:
> Selection.AutoFill Destination:=Range("I2:I431"), Type:=xlFillDefault
>
> Is there a way in which in which I can autofill to the extent of the
> data in, say, column B?
>
> TIA
> Keith
>
>