Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row with macro
Hi,
i have a sheet set out with a text box. the ListFillRrange is Contacts!A3:G35 When i goto the contacts sheet and add a row at the bottom, the ListFill Range increases to Contacts!A3:G36 which is good. however, when i insert the row through a macro, the ListFillRange does not change. I am using a routine that finds the next available cell on the Contacts sheet as supplied to me through this site ( supplied he) iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.rows(iRow).Insert Can something be done to insert a row but let the ListFillRange increase automatically? this is required as i am entering alot of information through this text box and i either have to increase the range to A3:A1000( which gives me a amssive dropdown box) or keep updating theListFillRange. Any help appreciated Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row with macro
Perhaps
iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.rows(iRow).Insert ws.Textbox1.ListFillRrange ="Contacts!A3:G" & iRow -- HTH RP (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Hi, i have a sheet set out with a text box. the ListFillRrange is Contacts!A3:G35 When i goto the contacts sheet and add a row at the bottom, the ListFill Range increases to Contacts!A3:G36 which is good. however, when i insert the row through a macro, the ListFillRange does not change. I am using a routine that finds the next available cell on the Contacts sheet as supplied to me through this site ( supplied he) iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.rows(iRow).Insert Can something be done to insert a row but let the ListFillRange increase automatically? this is required as i am entering alot of information through this text box and i either have to increase the range to A3:A1000( which gives me a amssive dropdown box) or keep updating theListFillRange. Any help appreciated Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row with macro
Hi Bob,
Sorry, i forgot to mention that the Textbox is actually a combobox and it is on another sheet called "Contacts Page". Everything works from here and the "Contacts" sheet is hidden. the next cell is found and row inserted without actually opening the sheet. Any Ideas? Nigel "Bob Phillips" wrote: Perhaps iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.rows(iRow).Insert ws.Textbox1.ListFillRrange ="Contacts!A3:G" & iRow -- HTH RP (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Hi, i have a sheet set out with a text box. the ListFillRrange is Contacts!A3:G35 When i goto the contacts sheet and add a row at the bottom, the ListFill Range increases to Contacts!A3:G36 which is good. however, when i insert the row through a macro, the ListFillRange does not change. I am using a routine that finds the next available cell on the Contacts sheet as supplied to me through this site ( supplied he) iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.rows(iRow).Insert Can something be done to insert a row but let the ListFillRange increase automatically? this is required as i am entering alot of information through this text box and i either have to increase the range to A3:A1000( which gives me a amssive dropdown box) or keep updating theListFillRange. Any help appreciated Nigel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row with macro
"Nigel" wrote in message ... Hi Bob, Sorry, i forgot to mention that the Textbox is actually a combobox :-) and it is on another sheet called "Contacts Page". Everything works from here and the "Contacts" sheet is hidden. the next cell is found and row inserted without actually opening the sheet. Presumably ws points to 'Contacts Page' and the combo is on some other page? iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.Rows(iRow).Insert ActiveSheet.ComboBox1.ListFillRange = "'Contacts Page'!A3:G" & iRow should work, it did in my tests |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a new row with a macro | Excel Discussion (Misc queries) | |||
Adding to a Macro | Excel Discussion (Misc queries) | |||
Adding a Tab in a Macro | Excel Discussion (Misc queries) | |||
Adding Row to this macro | Excel Discussion (Misc queries) | |||
Adding row Macro | Excel Programming |