Deleting rows that dont fit criteria

[Cuervogold]

I am new to the Excel macro language, and I am trying to delete entire
rows from 10000+ data points if the last two digits of a 12-digit
number in column A do not end in 00. Here is the macro I have, but it
will only delete the cell in column A. Again, I have an entire
worksheet with corresponding data that needs to be deleted. Thanks

============================

Sub Delete_numbers()

Dim i As Long
Dim number As string
Dim last2 As string

i = 1
number = ActiveCell.FormulaR1C1
For i = 1 to 1000000
number = ActiveCell.FormulaR1C1
If Not number = "" Then
last2 = Right(number, 2)
If Not last2 = "00" Then
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Else
Exit For
End If
Next i
End Sub

 

[Bob Umlas]

Change
Selection.Delete Shift:=xlUp
to
Selection.EntireRow.Delete

But whole thing can be:
Sub Delete_numbers()
Dim i as long
For i=1000000 to 1 step -1
if Right(cells(i,1).Value,2)="00" Then Rows(i).Delete
Next
End Sub

Bob Umlas
Excel MVP

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteNon00Rows()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If Not .Cells(X, "A").Value Like "*00" Then Rows(X).Delete
Next
End With
End Sub

Note that I started with the (calculated) last row and worked the loop
backwards... if you loop forward, each deleted row screws up what the loop
counter is counting.

 

[Gary''s Student]

Consider:

Sub Delete_numbers()
Dim i As Long, n As Long
Dim number As String
Dim last2 As String
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
number = Cells(i, "A").Value
last2 = Right(number, 2)
If Not last2 = "00" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub



1. we calculate where data stops so we don't need to test for it.
2. we loop backwards to simplfy the logic
3. we avoid Selecting or Activating to improve speed.

 

[Bernie Deitrick]

All the previous suggestions involve stepping up column A and deleting rows one by one. A possible
faster technique is to group the rows based on your criteria, then do one deletion. The code below
is between 4 to 250 times faster than the stepping code for the specific condition of 10,000 rows
(with 1% needing to be deleted, it is 4 times faster (0.4 seconds versus 1.6 seconds), with 99%
needing to be deleted, it is 250 times faster (0.4 seconds versus 90 seconds)). (Note that it is
actually slower if no rows need to be deleted, but not noticably: 0.23 seconds vs 0.14 seconds).
Obviously, as more rows need to be checked and deleted, the run time difference increases, and will
decrease as fewer need to be deleted. If this type of row deletion is something that you use on
large files on a regular basis, then optimizing your code could be important...

HTH,
Bernie
MS Excel MVP

Sub Delete00sInColA()
Dim myRow As Long
Dim myC As Range

myRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").EntireColumn.Insert
Range("A1").Value = "Keep"
With Range("A2:A" & myRow)
.FormulaR1C1 = _
"=IF(RIGHT(RC[1],2)=""00"",""Trash"",""Keep"")"
.Value = .Value
End With
Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
Set myC = Columns("A:A").Find(What:="Trash", After:=Range("A1"))
Range(myC, Cells(myRow, 1)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

 

[Gary''s Student]

An interesting idea.

Would using an AutoFilter via VBA be faster still??
--
Gary''s Student - gsnu200857

All the previous suggestions involve stepping up column A and deleting rows one by one. A possible
faster technique is to group the rows based on your criteria, then do one deletion. The code below
is between 4 to 250 times faster than the stepping code for the specific condition of 10,000 rows
(with 1% needing to be deleted, it is 4 times faster (0.4 seconds versus 1.6 seconds), with 99%
needing to be deleted, it is 250 times faster (0.4 seconds versus 90 seconds)). (Note that it is
actually slower if no rows need to be deleted, but not noticably: 0.23 seconds vs 0.14 seconds).
Obviously, as more rows need to be checked and deleted, the run time difference increases, and will
decrease as fewer need to be deleted. If this type of row deletion is something that you use on
large files on a regular basis, then optimizing your code could be important...

HTH,
Bernie
MS Excel MVP

Sub Delete00sInColA()
Dim myRow As Long
Dim myC As Range

myRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").EntireColumn.Insert
Range("A1").Value = "Keep"
With Range("A2:A" & myRow)
.FormulaR1C1 = _
"=IF(RIGHT(RC[1],2)=""00"",""Trash"",""Keep"")"
.Value = .Value
End With
Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
Set myC = Columns("A:A").Find(What:="Trash", After:=Range("A1"))
Range(myC, Cells(myRow, 1)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

I am new to the Excel macro language, and I am trying to delete entire
rows from 10000+ data points if the last two digits of a 12-digit
number in column A do not end in 00. Here is the macro I have, but it
will only delete the cell in column A. Again, I have an entire
worksheet with corresponding data that needs to be deleted. Thanks

============================

Sub Delete_numbers()

Dim i As Long
Dim number As string
Dim last2 As string

i = 1
number = ActiveCell.FormulaR1C1
For i = 1 to 1000000
number = ActiveCell.FormulaR1C1
If Not number = "" Then
last2 = Right(number, 2)
If Not last2 = "00" Then
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Else
Exit For
End If
Next i
End Sub

 

[Rick Rothstein]

I guess the following would be a little faster...

Sub DeleteNon00Rows()
Dim X As Long
Dim LastRow As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If Not .Cells(X, "A").Value Like "*00" Then Rows(X).Delete
Next
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[Bernie Deitrick]

Would using an AutoFilter via VBA be faster still??

Actually, no, because the rows are not grouped when they are filtered. The row deletion appears to
go by visible areas when the filter is applied, so it can be faster than the row by row deletion,
but isn't as fast as if the rows to be deleted are all grouped initially.

Bernie
MS Excel MVP