VBA Excel Delete Row Based on Value in Column

Question

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

Don't work with the active cell. You're not using your cell iterator variable, start with that. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 6, 2017 at 21:08
Also I highly suggest not looping through all rows in a column. Minimize that range too, which will help. — BruceWayne
– BruceWayne, Commented Oct 6, 2017 at 21:21

user8221156 · Accepted Answer · 2017-10-06 22:42:14Z

3

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

answered Oct 6, 2017 at 22:42

user8221156

Sign up to request clarification or add additional context in comments.

Comments

JonRo · Accepted Answer · 2017-10-07 23:08:47Z

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

Collectives™ on Stack Overflow

VBA Excel Delete Row Based on Value in Column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related