Delete blank spaces from a named range

True, but how to remove the blank rows from the named range? When I add a new entry, it adds to the bottom of the list, past all the blank spaces. Even if I have the listbox or named range sorted, the named range will still be filled erroneously with blank spaces. Perhaps I can set the named range to exclude blanks somehow in Excel instead of using vba, such as:

Refers To: =some code to remove blank spaces ("a1:a500")

Or with vba. I just need some help knowing where to start. Been reading internet posts the last 2-hours without a clue.

Interesting. It only seems to work on the first use. After that, it gives the following error rather the range has blank spaces or not:

Run-time error '1004':
No cells were found.

I tried adding this bit to verify if there were blank cells or not before running the code and to clear the set variables, but it didn't help when blank lines were added back to the list:

Please Login or Register  to view this content.

I think I may have discovered part of the issue... even with "With Worksheets", this code runs off the active sheet which seems odd to me. When I went to another sheet in my workbook and inadvertently tested this code, it altered that sheet instead of the sheet it was supposed to. No matter, I Activated and Selected the proper worksheet and the code still only runs once. To get it to run again, I have to completely close the worksheet and reopen it. I've never seen this before.