Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Delete All Empty Cells From Range Using VBA / Remove Blank Cells Using Formula

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0

    Delete All Empty Cells From Range Using VBA / Remove Blank Cells Using Formula

    HI
    I want this table to be like this
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Mahmoud, the sheet is in filter mode. You cannot expect to have the macro do what you what with the filter mode active. If you remove the filter, you can achieve what you want to do with a simple line of code

    Code:
    Range("A3:E23").SpecialCells(xlCellTypeBlanks).Delete xlUp
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I need a formula

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just use this A26=IFERROR(INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1))),"") as an array formula, and drag down and across
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    If the user office 2003

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    =IF(ISERROR(INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1)))),"",INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1))))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I'm sorry
    FORMULA GIVEN EMPTY CELL

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Please avoid statements like "Thank you very much/You are great/etc". If you like a post, just click on the Like button on the post. You can also like the Facebook button on the top of each thread, and post it to your facebook account.

    You mention the formula is giving empty cell. Did you confirm the formula as an array formula?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    YES
    YOU MEAN LIKE THIS
    {=IF(ISERROR(INDEX(A$3:A$23;SMALL(IF(LEN(A$3:A$23) ;ROW (INDIRECT("1:"&ROWS(A$3:A$23)));"");ROW(A2))));""; INDEX(A$3:A$23;SMALL(IF(LEN(A$3:A$23);ROW (INDIRECT("1:"&ROWS(A$3:A$23)));"");ROW(A2))))}
    Last edited by mahmoud-lee; 06-09-2013 at 09:41 PM.

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Post the workbook where you've used this array formula. By the way, why does your formula start with Row(A2), when I used Row(A1)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Delete Entire Row For All Empty Cells In Column
    By johnreid7477 in forum Excel Help
    Replies: 4
    Last Post: 06-15-2013, 05:50 AM
  2. Replies: 2
    Last Post: 02-11-2013, 08:13 PM
  3. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  4. Highlighting Blank Cells
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 08-13-2012, 07:56 AM
  5. Checking if range of cells=VbNullstring
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 02-03-2012, 07:24 AM

Posting Permissions

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