Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
436 views
in Technique[技术] by (71.8m points)

excel - if column A text "customer account" and column M <=0 delete all rows between

enter image description hereenter image description here

Trying to delete complete rows if criteria met. If column A has text "customer account" and column M <=0 then delete all rows between. It doesn't give any error but does not delete the rows

Dim sh As Worksheet

Set sh = Sheets("RAW DATA FILE")

Dim x As Long, lastrow As Long
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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...