[Solved] Run same macro in two worksheets

Tam

New member
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
 
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:

Run same macro in two worksheets to delete the empty columns.gif

Excel VBA Code:

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
 

Attachments

  • Tam (SOLVED).xlsm
    16.7 KB · Views: 0

Online statistics

Members online
0
Guests online
56
Total visitors
56

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top