Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Narrow Range based on Selection in Another Range | Excel Discussion (Misc queries) | |||
Identifying a selection of a selection of a range | Excel Worksheet Functions | |||
Range selection | Excel Programming | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming | |||
Creating range name for a range selection | Excel Programming |