Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows based on call value
Hi all. I have a worksheet with 30k plus rows. I ran a vlookup in
column AA. If the value in column AA=1, I would like to delete that row. I found some code on this group to do that (below) but it ran for 20 minutes, and I eventually killed it. Any ideas on how to accomplish this efficiently?? Thanks!! Sub Delete_Rows() Dim delRange As Range Dim cell As Range For Each cell In Range("AA2:AA" & Range("AA" & _ Rows.Count).End(xlUp).Row) If cell.Value = 1 Then If delRange Is Nothing Then Set delRange = cell Else Set delRange = Union(delRange, cell) End If End If Next cell If Not delRange Is Nothing Then delRange.EntireRow.Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows based on call value
Hi Steve:
I would use this type of code: Option Explicit Sub deleterows() Dim w As Worksheet Dim lr As Long Dim i As Long Set w = ActiveSheet lr = Range("AA" & w.Cells.Rows.Count).End(xlUp).Row For i = lr To 1 Step -1 If w.Range("AA" & i) = 1 Then w.Rows(i).Delete Next i Set w = Nothing End Sub -- Hope this helps Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand Please do not forget to rate this reply. "Steve" wrote: Hi all. I have a worksheet with 30k plus rows. I ran a vlookup in column AA. If the value in column AA=1, I would like to delete that row. I found some code on this group to do that (below) but it ran for 20 minutes, and I eventually killed it. Any ideas on how to accomplish this efficiently?? Thanks!! Sub Delete_Rows() Dim delRange As Range Dim cell As Range For Each cell In Range("AA2:AA" & Range("AA" & _ Rows.Count).End(xlUp).Row) If cell.Value = 1 Then If delRange Is Nothing Then Set delRange = cell Else Set delRange = Union(delRange, cell) End If End If Next cell If Not delRange Is Nothing Then delRange.EntireRow.Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows based on call value
Just a curious question....
Are you running Windows Vista? I made an application for someone else running Vista. This application ran in seconds on my machine running Windows XP and both Office 2003 & 2007. But on the other person's PC that was running Vista & Office 2007, it took a solid 5 minutes to run. I am wondering what complications may be encountered with Vista. Does anyone else have any more info on this topic??? Mark Ivey "Steve" wrote in message ... Hi all. I have a worksheet with 30k plus rows. I ran a vlookup in column AA. If the value in column AA=1, I would like to delete that row. I found some code on this group to do that (below) but it ran for 20 minutes, and I eventually killed it. Any ideas on how to accomplish this efficiently?? Thanks!! Sub Delete_Rows() Dim delRange As Range Dim cell As Range For Each cell In Range("AA2:AA" & Range("AA" & _ Rows.Count).End(xlUp).Row) If cell.Value = 1 Then If delRange Is Nothing Then Set delRange = cell Else Set delRange = Union(delRange, cell) End If End If Next cell If Not delRange Is Nothing Then delRange.EntireRow.Delete End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows based on call value
Steve:
Sorry in my haste, asI was going out, I omitted a couple of very important items: 'at the start Application.Calculation = xlCalculationManual Application.ScreenUpdating = False 'at the end Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True So the macro should be: Option Explicit Sub deleterows() Dim w As Worksheet Dim lr As Long Dim i As Long Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Set w = ActiveSheet lr = Range("AA" & w.Cells.Rows.Count).End(xlUp).Row For i = lr To 1 Step -1 If w.Range("AA" & i) = 1 Then w.Rows(i).Delete Next i Set w = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Hope this helps Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand Please do not forget to rate this reply. "Martin Fishlock" wrote: Hi Steve: I would use this type of code: Option Explicit Sub deleterows() Dim w As Worksheet Dim lr As Long Dim i As Long Set w = ActiveSheet lr = Range("AA" & w.Cells.Rows.Count).End(xlUp).Row For i = lr To 1 Step -1 If w.Range("AA" & i) = 1 Then w.Rows(i).Delete Next i Set w = Nothing End Sub -- Hope this helps Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand Please do not forget to rate this reply. "Steve" wrote: Hi all. I have a worksheet with 30k plus rows. I ran a vlookup in column AA. If the value in column AA=1, I would like to delete that row. I found some code on this group to do that (below) but it ran for 20 minutes, and I eventually killed it. Any ideas on how to accomplish this efficiently?? Thanks!! Sub Delete_Rows() Dim delRange As Range Dim cell As Range For Each cell In Range("AA2:AA" & Range("AA" & _ Rows.Count).End(xlUp).Row) If cell.Value = 1 Then If delRange Is Nothing Then Set delRange = cell Else Set delRange = Union(delRange, cell) End If End If Next cell If Not delRange Is Nothing Then delRange.EntireRow.Delete End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows based on call value
See this page for a few examples that will make the code faster
http://www.rondebruin.nl/delete.htm for example http://www.rondebruin.nl/delete.htm#Union -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ... Hi all. I have a worksheet with 30k plus rows. I ran a vlookup in column AA. If the value in column AA=1, I would like to delete that row. I found some code on this group to do that (below) but it ran for 20 minutes, and I eventually killed it. Any ideas on how to accomplish this efficiently?? Thanks!! Sub Delete_Rows() Dim delRange As Range Dim cell As Range For Each cell In Range("AA2:AA" & Range("AA" & _ Rows.Count).End(xlUp).Row) If cell.Value = 1 Then If delRange Is Nothing Then Set delRange = cell Else Set delRange = Union(delRange, cell) End If End If Next cell If Not delRange Is Nothing Then delRange.EntireRow.Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete rows based on a category? | Excel Discussion (Misc queries) | |||
Delete Rows based on value | Excel Worksheet Functions | |||
Delete rows based on an array | Excel Programming | |||
Delete rows based on value... | Excel Programming | |||
Delete Rows Based On Content | Excel Programming |