remove blank

A

aswathy0001

New member
Joined
Nov 29, 2016
Messages
24
Reaction score
0
Points
0
Location
india
Excel Version(s)
2010
i am using the following code to delete blank cells but it take too much time and some time takes hang any other solution for delete blank cells?
Code: 
Application.StatusBar = "Blank"    Range("A1", Range("A65536").End(xlUp)).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
 
It won't save much time but you can avoid selecting with:
Code: 
Application.StatusBar = "Blank"
Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Range("A1").Select
Otherwise all I can suggest (because it's the deletion of lots of non-contiguous areas which takes the time) is to sort the data first, then all the blanks will be together at the bottom, then delete them.
 
thank you for the suggestion but its not practical for me because i cant sort the data.(the possition is important)
thanks for the code..
 
You could add a temporary column, numbering each row, sort by column A, delete the rows with blanks at the bottom, sort by the temporary column to put back in the original order, remove the temporary column.
It would be quick.
 
You must log in or register to reply here.
Back
Top