Blank Rows in Dynamic Range after .delete of row

[IknowMe]

Im using this definition to dynamically name my data range. (Credit Skip)

=OFFSET
(
  INDIRECT("Sheet1!$A$1"),
  1, 
  0,
  CountA(Sheet1!$A:$A)-1,
  CountA(Sheet1!$1:$1)
)

I'm then using the name to populate a listbox via RowSource.

The problem I am having is when I use the following code to delete the listbox selected row from my worksheet I end up with a blank line at the bottom of my listbox for each row deleted.

Worksheets("IceDataSheet").Rows(f_IceMain.l_Inventory.ListIndex + 2).Delete

Any ideas?
I tried the following:

f_icemain.l_Inventory.RemoveItem
(didn't work because I'm using rowsource to populate the listbox)

also tried refreshing my form as I thought it may be something residual as opposed to actually being there.

I'm also wondering if the range isn't getting refreshed after the row is deleted. Although I would suspect a blank row in place of the deleted one which isn't the case on the worksheet or form.

I'm using excel 2003 SP2.



[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

Hi,

I'm not exactly sure what you are trying to accomplish. But take a look at the AddItem method of loading the Listbox. You can loop thru the Name range to load.

The alternative, using the RangeName in the RowSource, is the delete values from the RANGE on the sheet. This could also resize the RANGE, eliminating the blanks at the bottom.



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[IknowMe]

Can I post now?

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[AnotherHiggins]

I'll allow it.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Fire One!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[IknowMe]

Apparently it requires more authority than you've granted me higgins


[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[IknowMe]

Doh, is there a restriction to the size of a post I'm unaware of?

I'll try it in segements.

1.

What I have done is to create a form that allows multiple users to input/modify data in a worksheet via a Form. The problem was non standard data being input into the sheet. They now have a user form with input masks to ensure this doesn't continue to happen.

So I have a listbox in the form that allows them to search/select exsisting items in the data sheet or add new items. I know this screams for a true database but due to liscensing it's not possible and the limited nature of the inventory are well within tolerances of excel.

The issue with blank rows at the bottom of the listbox causes no functionality issues but sure drives me nuts.

On open the named data range properly sizes as well as expanding properly when an item is added to the worksheet via the form. So I am assuming the blank lines have something to do with the delete method I am using.


[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[IknowMe]

In summary user opens the workbook and the range and listbox are both sized correctly. User adds a record and both the range and listbox resize appropriately. User deletes a line via the code in the op and the index row associated to the listbox selection is deleted from the worksheet. Everything then shifts up to account for the deleted row in both the worksheet and the listbox. But I end up with a blank row at the end of the listbox for each deleted row.

With the limited size of the lists looping through the range and using the addrow methoid is a possability but I sure like the cleanliness of the way It's working now aside from the mentioned side effect.

Maybe there's a better way of doing the above that I haven't thought of.

As always thanks for your consideration and time Skip.

If this post doesn't make sense or seems off it's due to the fact I had to type it three different times to get TT to allow it to post. I promise the first draft was much more intelegent than this one


[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

"User deletes a line via the code in the op and the index row associated to the listbox selection is deleted from the worksheet. Everything then shifts up to account for the deleted row in both the worksheet and the listbox. "

Are you BOTH deleting a line in the ListBox AND the List Range on the sheet? You should ONLY be deleting data from the sheet, using

[yourcellobject].Delete [b]xlshiftup[/b].

If you select the range name in the NAME BOX, is the correct range selected on the sheet?





Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}