Did this thread that you have marked [SOLVED] not address this issue?
How do I remove "empty" cells in a range?

Then continued, as requested, in
Lookup list needs to change with new sheet

You also seem to have other related threads ...