deleting rows in sheet3 with code in sheet2

0

I am running this code from sheet2 and want to delete

the rows with "dupe" in column 4. in sheet3.  I can delete the rows

if I am in sheet 3, but not if I run the code from sheet 2.

Thanks  Carroll

Private Sub CommandButton1_Click()
Dim lrow As Long
Workbooks("excel formulas.xlsm").Sheets("sheet3").Select
  lrow = 7
    With Worksheets("sheet3")
        For iCntr = lrow To 3 Step -1
            If Cells(iCntr, 4) = "dupe" Then
                Rows(iCntr).Delete
            End If
        Next
    End With
End Sub
Answer
Discuss

Answers

0

This variation of your code should work as expected

Private Sub CommandButton1_Click() 
    Dim lrow As Long
    Dim iCntr As Long                       ' declare your variables

'    Don't Select anything. VBA doesn't need it.
'    Workbooks("excel formulas.xlsm").Sheets("sheet3").Select 
    lrow = 7
    With Worksheets("sheet3")
        For iCntr = lrow To 3 Step -1
            ' Cells(iCntr, 4) and Rows(iCntr) are on the ActiveSheet
            ' because no sheet is specified
            ' .Cells(iCntr, 4) and .Rows(iCntr) are on tWorksheets("sheet3")
            ' observe the leading period which tells VBA to "connect"
            ' to the object in the With statement
            If .Cells(iCntr, 4) = "dupe" Then
                .Rows(iCntr).Delete
            End If
        Next
    End With
End Sub
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login