skipping blank lines

[Megatron-Lives]

I have a vertical list of names, with some blanks between them, and i want the list to be displayed elsewhere with no spaces (dynamically, not just once, or i could use paste special) Is there any way to do this?

 

[DonkeyOte]

If you sort the data by one of the fields the blanks will move to the bottom of the list.

Say data = sheet1 range a1:f1000

Sheets("sheet1").range("a1:f1000").select
Selection.Sort KEY1:=Sheets("sheet1").Range("a1"), ORDER1:=xlAscending,HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

Hope that helps

 

[Juan Pablo González]

Chip Pearson has a formula to do this posted on his site. Check it out. http://www.cpearson.com