VBA delete column based on cell

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
Hi, I got the below code by searching online. I would like to delete columns based on a range of cells E12 to I12. If any of the cells are empty, then only that column should delete. The code only deletes one column at a time. Your help is appreciated.

VBA Code:
Sub Macro1()
    Dim rngCell As Range
    For Each rngCell In Range("I12:E12").Cells
        If rngCell.Value = "" Then
             rngCell.EntireColumn.Delete
        End If
    Next
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try this:
VBA Code:
Sub Macro1()
    Dim i as Long
    For i=9 to 5 step -1
        If Cells(12, i).Value = "" Then  Columns(i).Delete
    Next
End Sub
 
Upvote 0
Try this one-liner:
VBA Code:
Sub DeleteColumns()
    Range("E12:I12").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
End Sub
 
Upvote 0
Try this one-liner:
VBA Code:
Sub DeleteColumns()
    Range("E12:I12").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
End Sub
Hi Mumps,

Can the "on-liner" be adapted to find a specific word in a range of cells? If not I can use the code in post #2.

Range("E12:I12") to find the word "SUM".

Thanks
 
Upvote 0
I'm not sure that can be done with a one-liner.
 
Upvote 0
Not a one liner, but this avoids looping
VBA Code:
Sub Shadkng()
   With Range("E12:I12")
      .Replace "Sum", True, xlWhole, , False, , False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireColumn.Delete
      On Error GoTo 0
   End With
End Sub
 
Upvote 0
Solution
Not a one liner, but this avoids looping
VBA Code:
Sub Shadkng()
   With Range("E12:I12")
      .Replace "Sum", True, xlWhole, , False, , False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireColumn.Delete
      On Error GoTo 0
   End With
End Sub
Works good. Thanks
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top