Range Selection
 

Hi,

I have a line of code :
Selection.AutoFill Destination:=Range("B2:B34461")

The number of rows refers to data which increases on a daily basis - I have
to manually increase the row number (e.g. B34461) each time the data
increases.

Is there a way I can change the line so it automatically picks up the last
row of the spreadsheet that has data on it ?

Many Thanks
Pete

Range Selection
 

lastrow=cells(rows.count,"b").end(xlup).row
range("b2").AutoFill Destination:=Range("B2:B"&lastrow)


--
Don Guillett
SalesAid Software

"Pete" wrote in message
...
Hi,

I have a line of code :
Selection.AutoFill Destination:=Range("B2:B34461")

The number of rows refers to data which increases on a daily basis - I
have
to manually increase the row number (e.g. B34461) each time the data
increases.

Is there a way I can change the line so it automatically picks up the last
row of the spreadsheet that has data on it ?

Many Thanks
Pete

Range Selection
 

Hi
you can create a dynamic Range which increases in size automatically
In Excel click on the B2 cell (I assume B1 is a header?) of the sheet
(I'll call it Data) and type this in the refers to box:
=offset(Data!$B$2,0,0,COUNTA(Data!$B$B)-1,1)

and in the Names in workbook box type a name like myRange. Click Add
and OK.
Open the Insert name dialog box again and click the mouse inside this
formula - you should see a dotted line around your range, regardless of
how many new entries you add to the bottom.

Now your code becomes
Selection.AutoFill Destination:=Range("myRange")

I'm assuming here that there are no blank entries in myRange. If there
are, you will need a slightly different offset formula which will
depend on the location of the column with no blanks in it. Post back if
that is the case.
regards
Paul

Pete wrote:
Hi,

I have a line of code :
Selection.AutoFill Destination:=Range("B2:B34461")

The number of rows refers to data which increases on a daily basis - I have
to manually increase the row number (e.g. B34461) each time the data
increases.

Is there a way I can change the line so it automatically picks up the last
row of the spreadsheet that has data on it ?

Many Thanks
Pete

Range Selection
 

Great !
Thanks Paul.

" wrote:

Hi
you can create a dynamic Range which increases in size automatically
In Excel click on the B2 cell (I assume B1 is a header?) of the sheet
(I'll call it Data) and type this in the refers to box:
=offset(Data!$B$2,0,0,COUNTA(Data!$B$B)-1,1)

and in the Names in workbook box type a name like myRange. Click Add
and OK.
Open the Insert name dialog box again and click the mouse inside this
formula - you should see a dotted line around your range, regardless of
how many new entries you add to the bottom.

Now your code becomes
Selection.AutoFill Destination:=Range("myRange")

I'm assuming here that there are no blank entries in myRange. If there
are, you will need a slightly different offset formula which will
depend on the location of the column with no blanks in it. Post back if
that is the case.
regards
Paul

Pete wrote:
Hi,

I have a line of code :
Selection.AutoFill Destination:=Range("B2:B34461")

The number of rows refers to data which increases on a daily basis - I have
to manually increase the row number (e.g. B34461) each time the data
increases.

Is there a way I can change the line so it automatically picks up the last
row of the spreadsheet that has data on it ?

Many Thanks
Pete