Reputation: 19
I am creating a workbook with 17 sheets and each one has a product list that will change from month to month. If a product has errors, it shows up as CMB in the values but the product is still there. I was to delete the product rows. This code works on a sheet by sheet basis, but once I try to loop it, it does not work.
Sub wsLoop()
Dim ws as Worksheet
For Each ws In Worksheets
'Seeing if there are new products added to list
countcells = Range(Range("F8").End(xlDown).Offset(, -4), Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count
'if no products added, then see if there in CMB in any row and delete that row
If countcells = 1 Then
If Not Range("E:E").Find("CMB") Is Nothing Then
Range(Range("E:E").Find("CMB"), Range("E8").End(xlDown)).Rows.EntireRow.Delete
End If
End If
Next ws
End Sub
Upvotes: 1
Views: 679
Reputation: 42394
You have to actually get the range of the current worksheet. E.g.,
countcells = ws.Range(ws.Range("F8").End(xlDown).Offset(, -4), ws.Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count
Otherwise, you will always be just grabbing ranges off the currently selected worksheet (which will generally be the first worksheet).
Note that you will need to repeat this for all instances of Range
.
By the way, one thing you can do to make this easier is to use With
:
With ws
countcells = .Range(.Range("F8").End(xlDown).Offset(, -4), .Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count
'repeat for all lines
End With
With
eliminates the need to repeat the name of the object over and over. You just type .property
, and it automatically knows you mean ws.property
.
Upvotes: 2