CODEWITHSUNDEEP
excelvba
PrinceofSaxony
PrinceofSaxony

Reputation: 1

VBA Excel ForEach Worksheet Loop

First time poster, beginner level VBA scripter here. I have looked into the related questions here on StackOverflow and the answer to my question is just assumed, so I ask for some clarification. The following Excel VBA script is intended to remove all empty rows from the tops of a 90-Sheet Workbook. When I run the script, it only affects the Worksheet I have open. Why won't it loop through all of the Sheets?

Sub Macro1()

    Dim j As Long
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For j = 10 To 1 Step -1
            If Cells(j, 3) = "" Then
                Rows(j).Delete
            End If
        Next j
    Next

End Sub

Upvotes: 0

Views: 502

Answers (2)

Ranjitha
Ranjitha

Reputation: 51

Sub Macro1()

Dim j As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Activate
    For j = 10 To 1 Step -1
        If ws.Cells(j, 3) = "" Then
            ws.Rows(j).Delete
        End If
    Next j
Next

End Sub

Upvotes: 0

Anastasia_DVG
Anastasia_DVG

Reputation: 46

You need to tell it to look at the required worksheet in the If statement, and in the delete command:

Sub Macro1()

Dim j As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For j = 10 To 1 Step -1
        If ws.Cells(j, 3) = "" Then
            ws.Rows(j).Delete
        End If
    Next j
Next

End Sub

Upvotes: 2

Related Questions