ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Adding a row with macro (https://www.excelbanter.com/excel-programming/328803-adding-row-macro.html)

Nigel

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

Bob Phillips[_6_]

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




Nigel

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





Bob Phillips[_7_]

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




All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com