[Solved] Run same macro in two worksheets

[Tam]

Hi, I want to delete the empty columns in my excel workbook, so i have found the coding in internet as follow, but i have 2 worksheets need to do the same macro

how can i add one more worksheets in the coding below

Dim col As Long
With Worksheets("sheet1").UsedRange
For col = .Columns.Count To 1 Step -1
If WorksheetFunction.CountA(.Columns(col)) = 0 Then
.Columns(col).EntireColumn.Delete
End If

Next
End With

 

[Lutfor Rahman Shimanto]

Hi, I want to delete the empty columns in my excel workbook, so i have found the coding in internet as follow, but i have 2 worksheets need to do the same macro

how can i add one more worksheets in the coding below

Dim col As Long
With Worksheets("sheet1").UsedRange
For col = .Columns.Count To 1 Step -1
If WorksheetFunction.CountA(.Columns(col)) = 0 Then
.Columns(col).EntireColumn.Delete
End If

Next
End With

Dear Tam

Thanks for reaching out and sharing your question. You want to delete the empty columns from two sheets in your Excel workbook. But you want to run the macro once. You want a macro that deletes empty columns from two sheets.

Don't worry! I have improved the sub-procedure you provided to fulfil your goal. Assuming you have two sheets named Sheet1 and Sheet2. Please check the following GIF:

​

Excel VBA Code:

Sub DeleteEmptyColumns()

    Dim col As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With ws.UsedRange
        For col = .Columns.Count To 1 Step -1
            If WorksheetFunction.CountA(.Columns(col)) = 0 Then
                .Columns(col).EntireColumn.Delete
            End If
        Next col
    End With
    
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    With ws.UsedRange
        For col = .Columns.Count To 1 Step -1
            If WorksheetFunction.CountA(.Columns(col)) = 0 Then
                .Columns(col).EntireColumn.Delete
            End If
        Next col
    End With

End Sub

I have the improved sub-procedure that will fulfil your goal. I have attached the solution workbook as well; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy