Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
excelmacros-vba
MS Office Version: 2013
posted by: carroll (rep: 16) on Tue Feb 12, 2019 at 3:53 am
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
posted by: Variatus (rep: 4889) on Tue Feb 12, 2019 at 6:08 am
Discuss


Answer the Question

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