Large file 30000 rows need to delete rows that dont contain "3","9", or "18" in column A

[rkol297]

Large file 30000 rows need code to delete rows that don't contain "3","9", or "18" in column A this is a large file so I need some code that works well without a lot of processing time.

 

[texasalynn]

use the auto filter and uncheck the three you don't want. Delete the rows - no code required

 

[rkol297]

I need VBA code to do this task as I need it to be automated because I have other code that will run after this. Need VBA code, filtering won't work in this case.

 

[James Snyder]

Still use .Autofilter, but in code. This is untested, but I am working on an Autofilter problem of my own:

' Set up your range, then:
Dim filteredRange As Range
With your Range
    ' Set AutoFilter to screen out non-compared objects
    .AutoFilter Field:=1, Criteria1:=Array( _
        3, _
        9, _
        18), _
        Operator:=xlFilterValues
        filteredRange = .Cells.SpecialCells(xlCellTypeVisible) ' returns the keepers
        For i = filteredRange.count to 1 Step -1
            If Cells(i, 1).EntireRow.Hidden = True Then Cells(i, 1).EntireRow.Delete
        Next i
End With

TO quickly explain parts of it, the Field:L=1 represents selecting the first column to filter against. The Criteria1:=array(3,9,18) is setting what to filter with.The Operator:=xlFilterValues is saying to choose the cell value instead of formulas, etc. The loop works backwards to 1 to keep deleted rows from altering what you are working on.

 

[MPW]

Question: Could a Sort be used to get all the bad rows into one place and then just delete them all at once instead of looping through all the rows.
A sort is very fast. I know that when I delete a single row it takes about the same amount of time as deleting multiple rows.

Just a thought.

 

[montecarlo2079]

you could use this, im not sure how to completely fix it up for the 3 different values so this is sloppy but it is fast.


and then just call it from a main sub or from macro1

Sub macro1()
Dim lLRow As Long

With ThisWorkbook.Worksheets("TAB NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("a:a").AutoFilter Field:=1, Criteria1:="18"
.Range("a2:a" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With


End Sub


Sub macro1()
Dim lLRow As Long

With ThisWorkbook.Worksheets("TAB NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("a:a").AutoFilter Field:=1, Criteria1:="9"
.Range("a2:a" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With


End Sub

 

[montecarlo2079]

sorry im sure that was confusing


sub delete ()
call macro1
call macro2
call macro3
end sub


Sub macro1()
Dim lLRow As Long

With ThisWorkbook.Worksheets("TAB NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("a:a").AutoFilter Field:=1, Criteria1:="3"
.Range("a2:a" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With


End Sub


Sub macro2()
Dim lLRow As Long

With ThisWorkbook.Worksheets("TAB NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("a:a").AutoFilter Field:=1, Criteria1:="9"
.Range("a2:a" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With


End Sub

Sub macro3()
Dim lLRow As Long

With ThisWorkbook.Worksheets("TAB NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("a:a").AutoFilter Field:=1, Criteria1:="18"
.Range("a2:a" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With


End Sub

 

[James Snyder]

Question: Could a Sort be used to get all the bad rows into one place and then just delete them all at once instead of looping through all the rows.
A sort is very fast. I know that when I delete a single row it takes about the same amount of time as deleting multiple rows.

Just a thought.

Unfortunately, I could not figure out how to do that without knowing what your bad data is. There are several constants, but only

xlCellTypeVisible applies to your problem. Here are the rest:

[B]Type=XlCellType and can be one of these XlCellType constants.[/B]
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
[URL="http://www.ozgrid.com/VBA/last-used-cell.htm"]xlCellTypeLastCell[/URL]. The last cell in the used range. Note this XlCellType will   include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

 

[rallcorn]

Adapted from from Rick Rothstein:

Sub DeleteRows()
    Dim UnusedColumn As Long, LastRow As Long
    
    LastRow = Cells.Find(What:="*", searchorder:=xlRows, _
        searchdirection:=xlPrevious, LookIn:=xlValues).Row
    
    UnusedColumn = Cells.Find(What:="*", searchorder:=xlByColumns, _
        searchdirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    
    With Cells(1, UnusedColumn).Resize(LastRow)
        .FormulaR1C1 = "=if(or(RC1=3,RC1=9,RC1=18),"""",""X"")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
    End With
MsgBox "Done!"

End Sub