Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Vba to delete entire row

Posted on 2016-11-14
Medium Priority
202 Views
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]

0
Question by:Jagwarman

LVL 35

Expert Comment

ID: 41887468
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
``````
0

LVL 28

Assisted Solution

ID: 41887474
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
``````
0

Author Comment

ID: 41887478
Hi Norie

this row gives me a syntax error

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

LVL 35

Accepted Solution

Norie earned 1000 total points
ID: 41887481
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
``````
0

LVL 53

Expert Comment

ID: 41887488
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
``````
Regards
0

Author Closing Comment

ID: 41887494
Many thanks both really good but EE forces me to make one Best.

Regards
Jagwarman
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month11 days, left to enroll