Deleting Blank Cells in a column using special cells?

hrithik

Active Member
Joined
Jul 26, 2010
Messages
336
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 -->
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
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

Active Member
Joined
Jul 26, 2010
Messages
336
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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906

ADVERTISEMENT

How about
Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Code:
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

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top