VBA to Delete Columns based on Blank Cell in first row of data range

Question

I have a data range F9:AC40

Cells in F9:AC9 contains either a 1 or blank value

I need to delete the columns for that range where the value of any cells in F9:AC9 is blank

I appreciate anyone taking a look

 

Answer 1

Try this macro:

Sub DeleteCols()
    On Error Resume Next
    Range("F9:AC9").SpecialCells(xlCellTypeBlanks) _
        .Resize(RowSize:=32).Delete Shift:=xlShiftToLeft
End Sub

The On Error Resume Next suppresses the error message you'd get if there are no blank cells.

---

Regards, Hans Vogelaar

Answer 2

I've tried it but columns H and I which contain a blank cell in H9 and I9 respectively were not deleted and the data for those columns still present. I used it exactly as typed. Thanks.

 On Error Resume Next
    Range("FJ9:AC9").SpecialCells(xlCellTypeBlanks) _
        .Resize(RowSize:=32).Delete Shift:=xlShiftToLeft

Answer 3

Are H9 and I9 really blank, or do they contain a formula that returns an empty string "" ?

SpecialCells(xlCellTypeBlanks) returns only cells that are really blank, i.e. cells that contain neither a value nor a formula.

---

Regards, Hans Vogelaar

Answer 4

They are really blank. I used VBA to move the range from one range to another so there would be no formula's in the data set. I even manually hit delete on those cells to double check.

What if I used a 0 as the qualifier for a deleted column.  Would that be easier?

Answer 5

Sorry, it's my error. I hadn't really tested the code. The Resize doesn't work on a discontiguous range.

Try this version:

Sub DeleteCols()
    On Error Resume Next
    Dim rng As Range
    Dim i As Long
    Application.ScreenUpdating = False
    Set rng = Range("F9:AC9").SpecialCells(xlCellTypeBlanks)
    For i = rng.Areas.Count To 1 Step -1
        rng.Areas(i).Resize(RowSize:=32).Delete Shift:=xlShiftToLeft
    Next i
    Application.ScreenUpdating = True
End Sub

---

Regards, Hans Vogelaar

Answer 6

Works like a charm. Thanks for the help.