Deleting blank cells

[sebas35]

Hi there,

Maybe this is an easy task for you guys, but I've been struggling with it. Basically, I need to converter the table 1 into the table 2.

Thank you for any help!

---

Table 1
A1					NO
A2	YES
A3		NO
A4			YES		NO		YES
A5	NO										YES
A6	YES				YES
Table 2
A1	NO
A2	YES
A3	NO
A4	YES	NO	YES
A5	NO	YES
A6	YES	YES

<colgroup><col style="mso-width-source:userset;mso-width-alt:3868;width:82pt" width="109"> <col style="width:48pt" width="64" span="10"> <col style="mso-width-source:userset;mso-width-alt:3157;width:67pt" width="89"> </colgroup><tbody>
</tbody>

 

[MARK858]

If your cells are truly blank/empty then try...

Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

or manually

Select all cells
Ctrl + G
Click Special
Check Blanks
Ok
Right Click
Delete
Check Shift cells left
Ok

 

[kweaver]

If Table 1 appears just like your illustration with it starting in A1 cell (with "A1", etc. through "L1") and is in A1:L8,

Table 2 can be anywhere with the A column repeated (e.g., A8 to A13), then put this formula in B8 and fill across to L and down to row 13:

NOTE: It's an array formula, so you have to enter it using CTRL+SHIFT+Enter

=IFERROR(INDEX($B1:$L1,SMALL(IF($B1:$L1<>"",COLUMN($B1:$L1)-COLUMN($B1)+1),COLUMN()-1)),"")

HTH

 

[sebas35]

Hi kweaver,

Thank you for advising. Your formula worked only for one row in my matrix. Actually, I'd want the same procedure for every single row in the matriz (table 1) because my real matrix has about 2500 rows!

Thank you!

 

[MARK858]

So did the code I posted not work?

 

[kweaver]

Did you put the ARRAY formula in B8 and FILL across and down?

 

[sebas35]

Ok guys, yes, I tested both methods carefully, and they're working perfectly. Thank you!!!