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
1.1k views
in Technique[技术] by (71.8m points)

vba - Deleting rows with values based on a column

I have a monthly base with almost 373,000 lines. Of these, part has a low value or is blank. I'd like to erase this lines.

I have part of this code to delete those that have zero. How to create a code that joins the empty row conditions (column D) in a more agile way.

Thanks

Sub DelRowsZero()

    Dim i As Long
        For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
        If Cells(i, "D") = 0 Then Rows(i).Delete
    Next i

End Sub

Example

See Question&Answers more detail:os

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

1 Answer

0 votes
by (71.8m points)

How about:

Sub ZeroKiller()
    Dim N As Long, ToBeKilled As Range
    Dim i As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        If Cells(i, "D").Value = 0 Or Cells(i, "D").Value = "" Then
            If ToBeKilled Is Nothing Then
                Set ToBeKilled = Cells(i, "D")
            Else
                Set ToBeKilled = Union(ToBeKilled, Cells(i, "D"))
            End If
        End If
    Next i

    If Not ToBeKilled Is Nothing Then
        ToBeKilled.EntireRow.Delete
    End If
End Sub

This assumes that A is the longest column. If this is not always the case, use:

N = Range("A1").CurrentRegion.Rows.Count

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

...