Results 1 to 4 of 4

Thread: remove blank

  1. #1
    Seeker aswathy0001's Avatar
    Join Date
    Nov 2016
    Location
    india
    Posts
    13
    Articles
    0
    Excel Version
    2010

    remove blank



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,654
    Articles
    0
    Excel Version
    365
    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.

  3. #3
    Seeker aswathy0001's Avatar
    Join Date
    Nov 2016
    Location
    india
    Posts
    13
    Articles
    0
    Excel Version
    2010
    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..

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,654
    Articles
    0
    Excel Version
    365
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •