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!

---

YES
	NO
		YES		NO		YES
NO
Table 2
YES
NO
YES	NO	YES
NO	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>
[TD="width: 109"]Table 1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 89"][/TD]

[TD="class: xl63"]A1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]NO[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl66"]A2[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A3[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A4[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A5[/TD]

[TD="class: xl67"]YES[/TD]

[TD="class: xl68"]A6[/TD]
[TD="class: xl69"]YES[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"]YES[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl63"]A1[/TD]
[TD="class: xl64"]NO[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl66"]A2[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A3[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A4[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl66"]A5[/TD]

[TD="class: xl67"] [/TD]

[TD="class: xl68"]A6[/TD]
[TD="class: xl69"]YES[/TD]
[TD="class: xl69"]YES[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]

</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!!!