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

Hi

Another way to trap the error...

=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23);INDEX(A$3:A$2 3;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

Quote:

---

Originally Posted by Admin

Hi

Another way to trap the error...

=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23);INDEX(A$3:A$2 3;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:

Code:

---

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

---

Thank you all

Quote:

---

Originally Posted by LalitPandey87

Formula will look like this:

Code:

---

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