The answer to your current problem could be that you are using a reference to the currently active sheet. You came as far as declaring a parent sheet (sh
) but never used it as such. You can overcome that with a simple With
:
Dim sh As Worksheet: Set sh = Sheets("RAW DATA FILE")
Dim x As Long, lastrow As Long
With sh
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If .Cells(x, 2).Value = "customer account" And .Cells(x, 13) <= 0 Then
.Rows(x).Delete
End If
Next x
End with
That leaves the question wheather or not there are better, faster ways in getting your result. As per @BigBen, you should look into using a filter instead. You could try:
Dim sh As Worksheet: Set sh = Sheets("RAW DATA FILE")
Dim lastrow As Long
Dim rng As Range
With sh
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:M" & lastrow)
rng.AutoFilter Field:=2, Criteria1:="customer account"
rng.AutoFilter Field:=13, Criteria1:="<=0"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter
End With
This is assuming you are using a header row.
EDIT:
If your intention is to delete a whole range of rows, AutoFilter
is not an option nomore. In that case a loop did the trick, but you'll need some Offset
to check for your column M
value:
Dim sh As Worksheet: Set sh = Sheets("Blad1")
Dim x As Long, lastrow As Long
With sh
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If .Cells(x, 2).Value = "customer account" And .Cells(x, 13).Offset(4, 0) <= 0 Then
.Range(x & ":" & x + 4).EntireRow.Delete
End If
Next x
End With
This will delete the rows between AND the rows that are checked. If this is not what you want then you should use: .Range(x+1 & ":" & x + 3).EntireRow.Delete
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…