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

  • 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

     

    Tuesday, March 6, 2012 5:02 PM

Answers

  • 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

    • Marked as answer by Qaspec Tuesday, March 6, 2012 9:02 PM
    Tuesday, March 6, 2012 8:47 PM

All replies

  • 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

    Tuesday, March 6, 2012 5:50 PM
  • 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

    Tuesday, March 6, 2012 6:49 PM
  • 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

    Tuesday, March 6, 2012 8:00 PM
  • 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?

    Tuesday, March 6, 2012 8:27 PM
  • 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

    • Marked as answer by Qaspec Tuesday, March 6, 2012 9:02 PM
    Tuesday, March 6, 2012 8:47 PM
  • Works like a charm. Thanks for the help.
    Tuesday, March 6, 2012 9:02 PM