blank cells that have been pasted being counted as useddata

so i've got a sheet where i'm copying and pasting data from another sheet to form a list.

The problem is that the selection (a 5x3 area) could have from 1 entry per row to 5 so could have as much as 4 blank rows. Then when I come to using the macro again, using xlDown it counts those blank cells as if they have data in them and pastes the new data further down the column rather than the end of the current data.

Here's the code I have but I don't think this is the problem.

Please Login or Register  to view this content.

If you copied cells with formulas that returned "" (empty strings), they aren't actually blank. Select the top left cell manually, then press CTRL+SHIFT+DOWN ARROW. If this selects the "blanks", that's the problem. You can avoid this in the future by using a variant variable to move your data. Assign you range to the variable, which contains the values as an array. Then assign those values to any range of the same shape as the original. The blanks will be true blanks.
NOTE: LeoTaxi's post #2 is a good demonstration of this. ('arr' is the variant variable).
Now, if you need to get rid of a bunch of "not-blank" blank rows, try this macro:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Thanks, that worked! just had to very slighty modify the range from this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

haha