CODEWITHSUNDEEP
excelvba
Zerrets
Zerrets

Reputation: 53

Apply filter on the second worksheet of the workbook VBA

I already have a filter macro that cleanses the data of the first sheet("RO") of the workbook, now i want to clean the second worksheet ("RSSI"), i thought that by declaring the second sheet as the actual worksheet might work but it still works on the first sheet.

Dim wsToFilter As Worksheet
Dim wbToFilter As Workbook
Set wbToFilter = Workbooks("2. Detalle_Transacciones_pendientes_rechazadas_MDM_27Ene20.xlsx")
Set wsToFilter = wbToFilter.Worksheets("Rechazos_SSI_2019")

Dim RowToTest2 As Long

For RowToTest2 = Cells(Rows.Count, 2).End(xlUp).row To 2 Step -1

With Cells(RowToTest2, 1)
   If .Value <> "BATCH" _
   Then _
   Rows(RowToTest2).EntireRow.Delete
End With

Next RowToTest2

Upvotes: 0

Views: 40

Answers (2)

Variatus
Variatus

Reputation: 14373

The code below first looks at one worksheet, then at another. I thought that's what you wanted. It retains your method of determining the rows to delete.

Dim wbToFilter As Workbook
Dim wsToFilter As Worksheet
Dim RowToTest2 As Long
Dim WsCounter As Integer

Set wbToFilter = Workbooks("2. Detalle_Transacciones_pendientes_rechazadas_MDM_27Ene20.xlsx")
' here the first worksheet is assigned to the variable WsToFilter
Set wsToFilter = wbToFilter.Worksheets("Rechazos_SSI_2019")
For WsCounter = 1 To 2
    With wsToFilter         ' all the following is executed on WsToFilter
                            ' observe the leading periods which create the link
        For RowToTest2 = .Cells(.Rows.Count, 2).End(xlUp).Row To 2 Step -1
            With .Cells(RowToTest2, 1)
               If .Value <> "BATCH" Then _
                Rows(RowToTest2).EntireRow.Delete
            End With
        Next RowToTest2
    End With
    ' now, for the second loop, the other worksheet is assigned
    ' to the variable WsToFilter
    Set wsToFilter = wbToFilter.Worksheets("Rechazos_RO_2019")
Next WsCounter

Upvotes: 1

Zerrets
Zerrets

Reputation: 53

Dim wsToFilter As Worksheet
Dim wbToFilter As Workbook
Set wbToFilter = Workbooks("2. Detalle_Transacciones_pendientes_rechazadas_MDM_27Ene20.xlsx")
Set wsToFilter = wbToFilter.Worksheets("Rechazos_SSI_2019")

With wsToFilter
   .Range("A1").AutoFilter 1, "<>Batch"
   .AutoFilter.Range.Offset(1).EntireRow.Delete
   .AutoFilterMode = False
End With

Changed it into autofilter and worked perfectly, i took what Variatus wrote as a hint, so i modified my code like this.

Upvotes: 0

Related Questions