Answered by:
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
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:=xlShiftToLeftTuesday, 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