I have no other solution than copying Text1, Text2..., values (say in column
A) into a separate helper column (say column C) with such a macro:
Sub CopyNonEmpty()
Columns("A:A").Select 'copy from column A
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Selection.Copy
Columns("C:C").Select 'copy into column C
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
Adjust column references to your real needs!
Regards,
Stefi
€žslim€ť ezt Ă*rta:
Hi, Thanks for replying.
I can't just hide the cells because they are part of a data range too
so hiding the cells won't help.
The reason I need to get the values in this order is so that I can put
them into a listbox without there being massive spaces between each
entry.
Hope this clarifies.
--
slim
------------------------------------------------------------------------
slim's Profile: http://www.excelforum.com/member.php...o&userid=28643
View this thread: http://www.excelforum.com/showthread...hreadid=543221