CODEWITHSUNDEEP
vbarangedelete-row
Asim Ahmed
Asim Ahmed

Reputation: 83

Delete column if range is blank

I am looking to find guidance on building a macro to delete columns depending if the range is empty. I am having trouble defining my range so it changes with every loop, i'm new to VBA so information on setting up this range would be helpful for this code and building code in the future!

Sub sbDelete_Columns_IF_Cell_Is_empty()
Dim lColumn As Long
Dim iCntr As Long
lColumn = 103
For iCntr = lColumn To 1 Step -1
    If IsEmpty(Range()
        Columns(iCntr).Delete
    End If
Next
End Sub

second attempt, this is being weird and deleting columns that i don't want it to delete

Sub sbDelete_Columns_IF_Cell_Is_Blank()
Dim lColumn As Long
Dim iCntr As Long
Dim i As Integer
Dim f As Integer
i = 4
f = 100

lColumn = 103
For iCntr = lColumn To 1 Step -1
    If IsEmpty(Range(Cells(f, i), (Cells(f, i)))) = True Then
        Columns(iCntr).Delete
    End If
Next
End Sub

Upvotes: 2

Views: 4152

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Try this

Sub sbDelete_Columns_IF_Cell_Is_empty()
Dim lColumn As Long
Dim iCntr As Long
Dim ws As Worksheet
Dim rwsToCheck As Long

Set ws = ActiveSheet
rwsToCheck = 1000 'Change to what ever you want
lColumn = 103
For iCntr = lColumn To 1 Step -1
    If WorksheetFunction.CountA(ws.Range(ws.Cells(4, iCntr), ws.Cells(rwsToCheck, iCntr))) = 0 Then
        ws.Columns(iCntr).Delete
    End If
Next
End Sub

Upvotes: 3

Related Questions