CODEWITHSUNDEEP
vbaexcel
pvxl1989
pvxl1989

Reputation: 37

VBA Excel Delete Row Based on Value in Column

Would like to delete rows from a report based on the data in column M. Report is of variable size row-wise but the same width in columns. "Valid" in a cell means it gets deleted.

Sub Create()
Dim Range1 As Range
Set Range1 = Range("M:M")
For Each cell In Range1
    If ActiveCell.Value = "Valid" _
    Then ActiveCell.EntireRow.Delete
Next cell
End Sub

Upvotes: 1

Views: 11263

Answers (2)

JonRo
JonRo

Reputation: 181

I found a way using your For Each :

Public Sub Create()
Dim Range1 As Range
Dim Cell
Dim LastRow As Long

    Set Range1 = Range("M1")
    ' assume, there is some data in the first row of your sheet
    LastRow = Range1.CurrentRegion.Rows.Count
    ' otherwise, find last cell in column M with a value, assume before row 10000
    LastRow = Range("M10000").End(xlUp).Row

    ' select the cells to process
    Set Range1 = Range(Range1, Range1.Offset(LastRow, 0))

    ' process the rows
    For Each Cell In Range1
        If Cell.Value = "Valid" Then
            Debug.Print "' delete row from at address :: " & Cell.Address
            Range(Cell.Address).EntireRow.Delete
        End If
    Next
End Sub

Upvotes: 0

user8221156
user8221156

Reputation:

It now about the ActiveCell but cells in the column "M:M". Also, you need to start form the bottom up (not obvious but true). So, assuming there are fewer rows than 10000, you need something like this:

Sub Create()
    Dim LastRow As Long
    Dim i As Long

    LastRow = Range("M10000").End(xlUp).Row
    For i = LastRow To 1 Step -1
        If Range("M" & i) = "Valid" Then
            Range("M" & i).EntireRow.Delete
        End If
    Next
End Sub

Upvotes: 3

Related Questions