Vba to delete entire row

Can an expert provide me with VBA code that will delete the entire row if it finds the number 1 in column ‘O’ [must be a singular 1 not 21, 31 etc]

Many thanks in advance
JagwarmanAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Too early in the morning.:)

This is how it should read.
Dim I As Long

    For I = Range("O" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Cells(I, "O").Value = 1 Then
            Rows(I).EntireRow.Delete
        End If
    Next I

Open in new window

0
 
NorieVBA ExpertCommented:
Try this.

Dim I As Long

    For I = Range("O" & Rows.Count).End(xlUp).Row Step -1
        If Cells(I, "O").Value = 1 Then
            Rows(I).EntireRow.Delete
        End If
    Next I

Open in new window

0
 
MacroShadowConnect With a Mentor Commented:
Here
Sub Demo()
    Dim lngLastRow As Long
    Dim lngCurrentRow As Long

    With ActiveSheet
        lngLastRow = Range("O" & Rows.Count).End(xlUp).Row
        For lngCurrentRow = lngLastRow To 1 Step -1
            With .Cells(lngCurrentRow, "O")
                If Not IsError(.Value) Then
                    If .Value = "1" Then .EntireRow.Delete
                End If
            End With
        Next lngCurrentRow
    End With
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
JagwarmanAuthor Commented:
Hi Norie

this row gives me a syntax error

For I = Range("O" & Rows.Count).End(xlUp).Row Step -1
0
 
Rgonzo1971Commented:
HI,

pls try
Sub Macro1()
'
' Macro1 Macro
'
   Set c = Range("O" & Rows.Count)
   For lCol = 1 To WorksheetFunction.CountIf(Range("O:O"), 1)
        Set c = Range("O:O").Find(What:="1", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
            c.EntireRow.Delete
   Next lCol
 
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
Many thanks both really good but EE forces me to make one Best.

Regards
Jagwarman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.