CODEWITHSUNDEEP
vbaexcel
nir020
nir020

Reputation: 97

VBA codes only works on first sheet

I am trying to edit a workbook with 17 sheets and each one has list of cities in the column A and I want to delete rows where they don't equal values in an array I have created in the code below. This code works on a sheet by sheet basis, but once I try to loop it, it does not work.

    Sub myDeleteRows()

Dim MyCol As String
Dim lRow As Long
Dim iCntr As Long
Dim i As Integer
Dim core_cities As Variant
Dim sh As Worksheet

core_cities = Array("Bristol", "Birmingham", "Cardiff", "Leeds", "Liverpool", "Manchester", "Newcastle-upon-Tyne", "Nottingham", "Sheffield")

lRow = 140

For Each sh In ActiveWorkbook.Sheets

For i = lRow To 4 Step -1

    If IsError(Application.Match(Range("A" & i).Value, core_cities, False)) Then
    Rows(i).Delete
    End If

Next i
Next sh

MsgBox ("complete")
End Sub

Upvotes: 1

Views: 317

Answers (3)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You need to qualify the range with the Sheet reference otherwise the range used in the code will always refer to the active sheet only.

For Each sh In ActiveWorkbook.Sheets
    For i = lRow To 4 Step -1
        If IsError(Application.Match(sh.Range("A" & i).Value, core_cities, False)) Then
            sh.Rows(i).Delete
        End If
    Next i
Next sh

Upvotes: 4

SJR
SJR

Reputation: 23081

It's a simple fix. Your loop was not referencing sh which means only the active sheet was being changed. The only changes below (aside from indentation) are the additions of two shs.

To make your code more efficient, you could vary lRow so it picks up only rows containing entries (unless all your sheets will only ever have 140 entries).

Sub myDeleteRows()

Dim MyCol As String
Dim lRow As Long
Dim iCntr As Long
Dim i As Long
Dim core_cities As Variant
Dim sh As Worksheet

core_cities = Array("Bristol", "Birmingham", "Cardiff", "Leeds", "Liverpool", "Manchester", "Newcastle-upon-Tyne", "Nottingham", "Sheffield")

lRow = 140

For Each sh In ActiveWorkbook.Sheets
    For i = lRow To 4 Step -1
        If IsError(Application.Match(sh.Range("A" & i).Value, core_cities, False)) Then
            sh.Rows(i).Delete
        End If
    Next i
Next sh

MsgBox ("complete")

End Sub

Upvotes: 4

user4039065
user4039065

Reputation:

You have to reference each worksheet as you loop through them.

Sub myDeleteRows()

    Dim MyCol As String
    Dim lRow As Long
    Dim iCntr As Long
    Dim i As Integer
    Dim core_cities As Variant
    Dim sh As Worksheet

    core_cities = Array("Bristol", "Birmingham", "Cardiff", "Leeds", "Liverpool", "Manchester", "Newcastle-upon-Tyne", "Nottingham", "Sheffield")

    lRow = 140

    For Each sh In ActiveWorkbook.Sheets

        with sh
            For i = lRow To 4 Step -1

                If IsError(Application.Match(.Range("A" & i).Value, core_cities, False)) Then
                    .Rows(i).Delete
                End If

            Next i
        end with

    Next sh

    MsgBox ("complete")

End Sub

Note .Range and .Rows when inside the With ... End With and not simply Range or Rows. The prefix period (e.g. . ) provides the associated parent worksheet reference to sh.

Upvotes: 4

Related Questions