Deleting Blank Cells in a column using special cells?

[hrithik]

The following code does not work in my Macro:

Code:

For Each MyCol In Array("F", "G", "H") Columns(MyCol).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp Next MyCol</PRE>
Any thoughts? I'm using excel 2010, Win 7
<!-- / message --><!-- edit note -->

 

[meldoc]

Hello

It works if written like this.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> MyCol <SPAN style="color:#00007F">In</SPAN> Array("F", "G", "H")<br>    Columns(MyCol).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp<br>  <SPAN style="color:#00007F">Next</SPAN> MyCol<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[hrithik]

It works if written like this.

I have the code written that way....it does not work.

I even tried doing it manually by using "Edit -> Go to Special -> blank cells -> delete", which did not work. The message I got was "No cells were Found", when columns F,G and H have multiple blank cells.

 

[kpark91]

Columns(MyCol).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete Shift:=xlToLeft

Not tested.

So are you trying to delete the entire column if the whole column is empty?

 

[mikerickson]

How about

With Range("F:H")
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error Goto 0
End With

The message I got was "No cells were Found", when columns F,G and H have multiple blank cells.

If a cell contains a formula that evaluates to "", SpecialCells does not consider it blank (it contains a formula).

 

[JackDanIce]

Brian's method just worked fine for me (xl2003), perhaps if you give an example of the data you start with and what you expect the end result to be?

 

[Jonmo1]

I have the code written that way....it does not work.

I even tried doing it manually by using "Edit -> Go to Special -> blank cells -> delete", which did not work. The message I got was "No cells were Found", when columns F,G and H have multiple blank cells.

That pretty much means that your "Blanks" are not TRUELY blank.

You'll have to use a loop to do it.

Try

LR = Range("F" & Rows.Count).End(xlup).Row
For i = LR to 1 Step -1
    If Len(Cells(i,"F")) = 0 OR Len(Cells(i,"G")) = 0 OR Len(Cells(i,"H")) = 0 Then
        Rows(i).EntireRow.Delete
    End If
Next i

 

[meldoc]

I have the code written that way....it does not work.

Mikerickson's has probably spotted the error, I tried it on a range containing proper blanks.
I thought that you were asking about the syntax error, that was generated by the way the code was written.