Solved

Vba to delete entire row

Posted on 2016-11-14
6
42 Views
Last Modified: 2016-11-15
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
0
Comment
Question by:Jagwarman
6 Comments
 
LVL 33

Expert Comment

by:Norie
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

Open in new window

0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
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

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41887478
Hi Norie

this row gives me a syntax error

For I = Range("O" & Rows.Count).End(xlUp).Row Step -1
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 33

Accepted Solution

by:
Norie earned 250 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

Open in new window

0
 
LVL 49

Expert Comment

by:Rgonzo1971
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

Open in new window

Regards
0
 

Author Closing Comment

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

Regards
Jagwarman
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question