CODEWITHSUNDEEP
excelvba
sai krishna
sai krishna

Reputation: 33

How to delete Entire Column in excel using vba

I want to remove empty columns in excel and please look into below code and suggest corrections.

Sub RemoveEmptyCol()
Dim lc As Double
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To lc
If Cells(1, i).Value = "" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
End Sub

I tried above code to remove empty column but it didn't removed all empty columns in single run.

Upvotes: 1

Views: 5763

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

Scott Craner is on point with the looping backward, and I will add another valuable piece of information that will assist in processing time, especially if you have many, many rows of data, as deleting entire columns can be resource heavy.

The below code will remove the blank columns for only rows where the actual data is stored. It assumes column A has the entire row set fill in. If not, adjust based on whatever column does.

Sub RemoveEmptyCol()

    Dim lc As Long, lr as Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    lr = Cells(Rows.Count,1).End(xlUp).Row

    For i = lc To 1 Step -1

        If Cells(1, i).Value = "" Then 

            Range(Cells(1, i),Cells(lr,i).Delete Shift:=xlToLeft

        End If

    Next i

End Sub

Upvotes: 1

Related Questions