Delete Entire Row If Cell Equals..

[the blue fox] · Sep 30th 2007

Morning all.

I have tried to make head and tails of a few searches to delete entire rows, but I cant seem to adjust the coding to make it work for my scenario.

I have a row range 5:20004. In that row range column R may have 'QLD' in the cell.
I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)

I would love to do this on opening the file automatically.

Can anyone offer up some help?
Thanks

**StephenR** · Sep 30th 2007

Re: Delete Entire Row If Cell Equals Qld

This assumes your data are in one block, i.e. no completely blank rows. Paste into ThisWorkbook in the VB Editor.

Code

Private Sub Workbook_Open()


Application.ScreenUpdating = False


With Sheet1.Range("A5").CurrentRegion
    .AutoFilter field:=18, Criteria1:="<>NSW"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With


Sheet1.AutoFilterMode = False


Application.ScreenUpdating = True


End Sub

Display More

[the blue fox] · Sep 30th 2007

Re: Delete Entire Row If Cell Equals Qld

Hi ..
I have adjusted to my scenario .. but still not working

Code

Application.ScreenUpdating = False
     
    With Sheet21.Range("5:20004").CurrentRegion
        .AutoFilter field:=18, Criteria1:="<>NSW"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
    End With
     
    Sheet21.AutoFilterMode = False
     
    Application.ScreenUpdating = True

The AutoFilter line is what highlights yellow and preventing it going any further.
I even left the range as "A5" but still not working.

Any ideas?

**StephenR** · Sep 30th 2007

Re: Delete Entire Row If Cell Equals Qld

As it stands this line is wrong, no need to change what I posted except sheet reference.

Code

With Sheet21.Range("5:20004").CurrentRegion

Do you have blank columns between A and R?

Stanley D. Grom · Sep 30th 2007

Re: Delete Entire Row If Cell Equals Qld

[the blue fox],

Code

Option Explicit
Sub DeleteRows()


    'R5: R20004
    'delete if R row number = "QLD"


    Dim lngLoopCtr As Long


    Application.ScreenUpdating = False
    For lngLoopCtr = 20004 To 5 Step -1
        If UCase(Cells(lngLoopCtr, "R")) = "QLD" Then
            Rows(lngLoopCtr).EntireRow.Delete
        End If
    Next lngLoopCtr
    Application.ScreenUpdating = True


End Sub

Display More

Have a great day,
Stan

[the blue fox] · Oct 1st 2007

Re: Delete Entire Row If Cell Equals Qld

StephenR
The autofilter line is still highligting yellow.

Hi Stan
Thanks .. but this loop seems to take forever. I reduced the 20004 to 65 and it still took AGES to delete the rows.

Any ideas? I can assume now if I want this done, it will in fact take ages to loop over the 20004 lines?

Stanley D. Grom · Oct 1st 2007

Re: Delete Entire Row If Cell Equals Qld

Please attach your workbook.

Have a great day,
Stan

AAE · Oct 1st 2007

Re: Delete Entire Row If Cell Equals Qld

Try this page here at Oz.

Delete Rows Meeting Condition/Criteria

HTH,
AAE

[the blue fox] · Oct 1st 2007

Re: Delete Entire Row If Cell Equals Qld

I can not get the file size down to anywhere near 48kbs.
I have to retain some of the coding needed to show, but the file if too big.

Sorry
I may have to leave this one.

Dave Hawley · Oct 1st 2007

Re: Delete Entire Row If Cell Equals Qld

Don't use words that are specific to you ONLY (Qld). Please consider others, not just yourself.

Stanley D. Grom · Oct 1st 2007

Re: Delete Entire Row If Cell Equals..

One more try:

Code

Sub DeleteRows()


    'R5: R20004
    'delete if R row number = "QLD"


    Application.ScreenUpdating = False


    With Range("R5:R20004")
        .Replace "QLD", True, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With


    Application.ScreenUpdating = True


End Sub

Display More

Have a great day,
Stan

AAE · Oct 1st 2007

Re: Delete Entire Row If Cell Equals..

Try this. Ran a short test and it worked fine.

Code

Sub DeleteRow() 
    Dim c As Long 
    Dim LastRow As Long 
     
     
    Application. ScreenUpdating = False 
    LastRow =  Range("R65536").End(xlUp).Row 
     
    For c = LastRow To 5  Step -1 
         
        If Cells(c, 18) = "QLD" Then 
            Rows(c).EntireRow.Delete 
        End If 
    Next c 
    Application.ScreenUpdating = True 
End Sub

Display More

Stanley D. Grom · Oct 1st 2007

Re: Delete Entire Row If Cell Equals..

Please try this:

Code

Sub DeleteRows() 
     
     'R5: R20004
     'delete if R row number = "QLD"
     
    Application. ScreenUpdating = False 
     
    With  Range("R5:R20004") 
        .Replace "QLD", True, xlWhole 
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete 
    End With 
     
    Application.ScreenUpdating = True 
     
End Sub

Display More

Have a great day,
Stan

Delete Entire Row If Cell Equals..

Participate now!

Share