How to Delete a Row, on a hidden worksheet, that contains a "0" using VBA

[Santude]

I have written the following code, that works like a champ, however I have to have the Excel sheet (Sheet2) active. If I run my macro while on Sheet1 it completely ignores sheet2 and deletes rows on sheet1. I have racked my brain trying to figure this out....I am declaring everything to point towards Sheet2...I just can not figure it out! When I am completed with this project, I am going to hide Sheet2, so it is important that the focus stays on Sheet1, while performing the functions on Sheet2....

Sub DeleteValue0()

Dim QuotegroupLast As Long, QuoteGroupDW As Worksheet, Remove0 As Long

Set QuoteGroupDW = ThisWorkbook.Worksheets("Sheet2")

QuotegroupLast = QuoteGroupDW.Range("A" & Rows.Count).End(xlUp).Row

'Find and Remove columns with 0
For Remove0 = QuotegroupLast To 1 Step -1
If Cells(Remove0, 1) = 0 Then
Rows(Remove0).Delete
End If
Next

End Sub


Thank you for your help!!

 

[Herakles]

I have written the following code, that works like a champ, however I have to have the Excel sheet (Sheet2) active. If I run my macro while on Sheet1 it completely ignores sheet2 and deletes rows on sheet1. I have racked my brain trying to figure this out....I am declaring everything to point towards Sheet2...I just can not figure it out! When I am completed with this project, I am going to hide Sheet2, so it is important that the focus stays on Sheet1, while performing the functions on Sheet2....

Sub DeleteValue0()

Dim QuotegroupLast As Long, QuoteGroupDW As Worksheet, Remove0 As Long

Set QuoteGroupDW = ThisWorkbook.Worksheets("Sheet2")

QuotegroupLast = QuoteGroupDW.Range("A" & Rows.Count).End(xlUp).Row

'Find and Remove columns with 0
For Remove0 = QuotegroupLast To 1 Step -1
If Cells(Remove0, 1) = 0 Then
Rows(Remove0).Delete
End If
Next

End Sub


Thank you for your help!!

You were deleting rows on the active sheet because you did not tell it to delete the rows on Sheet2.

Also, if row 1 in Sheet2 is a header row then your loop needs to stop at row 2.

Try this on a copy of your data.

Public Sub DeleteValue0()
Dim QuotegroupLast As Long
Dim QuoteGroupDW As Worksheet
Dim Remove0 As Long

    Set QuoteGroupDW = ThisWorkbook.Worksheets("Sheet2")
    
    QuotegroupLast = QuoteGroupDW.Range("A" & Rows.Count).End(xlUp).Row
    
    ' Find and Remove columns with 0
    With QuoteGroupDW
        For Remove0 = QuotegroupLast To 1 Step -1
            If .Cells(Remove0, 1) = 0 Then
                .Rows(Remove0).EntireRow.Delete
            End If
        Next
    End With

End Sub

 

[Santude]

Thank you for your help! Works...