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

[mahmoud-lee]

HI
I want this table to be like this

[Excel Fox]

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


Range("A3:E23").SpecialCells(xlCellTypeBlanks).Delete xlUp

[mahmoud-lee]

I need a formula

[Excel Fox]

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

[mahmoud-lee]

If the user office 2003

[Excel Fox]

=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))))

[mahmoud-lee]

I'm sorry
FORMULA GIVEN EMPTY CELL

[Excel Fox]

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 (http://office.microsoft.com/en-in/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx)?

[mahmoud-lee]

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))))}

[Excel Fox]

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)

[Admin]

Hi

Another way to trap the error...

=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23);INDEX(A$3:A$23;SMALL(IF(LEN(A$3: A$23);ROW(A$3:A$23)-ROW(A$3)+1);ROWS(A$26:A26)));"")

again it's an array formula. confirmed with CTRL+SHIFT+ENTER

[LalitPandey87]

Hi

Another way to trap the error...

=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23);INDEX(A$3:A$23;SMALL(IF(LEN(A$3: A$23);ROW(A$3:A$23)-ROW(A$3)+1);ROWS(A$26:A26)));"")

again it's an array formula. confirmed with CTRL+SHIFT+ENTER

Formula will look like this:


=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23),INDEX(A$3:A$23,SMALL(IF(LEN(A$3: A$23),ROW(A$3:A$23)-ROW(A$3)+1),ROWS(A$26:A26))),"")

[mahmoud-lee]

Thank you all

[Admin]

Formula will look like this:


=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23),INDEX(A$3:A$23,SMALL(IF(LEN(A$3: A$23),ROW(A$3:A$23)-ROW(A$3)+1),ROWS(A$26:A26))),"")

That's intentional. I guess the OP is using European version of Office.