Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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 "Cuervogold" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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. -- Rick (MVP - Excel) "Cuervogold" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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. -- Gary''s Student - gsnu200857 "Cuervogold" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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 "Cuervogold" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
An interesting idea.
Would using an AutoFilter via VBA be faster still?? -- Gary''s Student - gsnu200857 "Bernie Deitrick" wrote: 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 "Cuervogold" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Cuervogold" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that dont fit criteria
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting several rows with given criteria | Excel Discussion (Misc queries) | |||
deleting rows based on criteria | Excel Programming | |||
Deleting Rows if certain criteria isnt met | Excel Programming | |||
Deleting rows with a two criteria | Excel Programming | |||
Deleting rows that dont... | Excel Programming |