Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster loop than this
I am currently using the following loop to delete unwanted rows from a
specified sheet on a workbook:- Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then .Rows(r).Delete End If Next r End With End Sub Is this the most efficient loop that I could use? It takes about 2 minutes to complete this loop on a spreadsheet with about 1400 rows. Admittedly this is on quite a small laptop so would be faster on a desktop but could I improve on my code. TIA Andi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster loop than this
I think your loop is ok but there might be a better approach to this
Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A8:A" & rw) rngActive.AutoFilter rngActive.AutoFilter Field:=7, Criteria1:="=*closed*", _ Operator:=xlAnd .Range("A9").CurrentRegion.SpecialCells(xlCellType Visible) _ .EntireRow.Delete rngActive.AutoFilter End With End Sub HTH Charles Chickering Andibevan wrote: I am currently using the following loop to delete unwanted rows from a specified sheet on a workbook:- Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then .Rows(r).Delete End If Next r End With End Sub Is this the most efficient loop that I could use? It takes about 2 minutes to complete this loop on a spreadsheet with about 1400 rows. Admittedly this is on quite a small laptop so would be faster on a desktop but could I improve on my code. TIA Andi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster loop than this
Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook)
Dim rngActive As Range, Cell As Range Dun rng as Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then if rng is nothing then set rng = .Range("G" & r) else set rng = Union(rng,.Range("G" & r)) end if End If Next r End With if not rng is nothing then rng.EntireRow.Delete end if End Sub another way is to use find Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) dim rng as Range Set rng = Nothing Do if not rng is nothing then rng.EntireRow.Delete end if set rng = wbcopy.Columns(7).Find( _ What:="Closed", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) Loop until rng is nothing End Sub -- Regards, Tom Ogilvy "Andibevan" wrote: I am currently using the following loop to delete unwanted rows from a specified sheet on a workbook:- Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then .Rows(r).Delete End If Next r End With End Sub Is this the most efficient loop that I could use? It takes about 2 minutes to complete this loop on a spreadsheet with about 1400 rows. Admittedly this is on quite a small laptop so would be faster on a desktop but could I improve on my code. TIA Andi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster loop than this
might need a bit more tweaking to avoid deleting the header row (or rows
above A8 that are contiguous and filled): Just to demo from the immediate window. Range("A8:H8").Resize(20).Formula = "=Rand()" ? range("A9").CurrentRegion.Address $A$8:$H$27 -- Regards, Tom Ogilvy "Die_Another_Day" wrote: I think your loop is ok but there might be a better approach to this Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A8:A" & rw) rngActive.AutoFilter rngActive.AutoFilter Field:=7, Criteria1:="=*closed*", _ Operator:=xlAnd .Range("A9").CurrentRegion.SpecialCells(xlCellType Visible) _ .EntireRow.Delete rngActive.AutoFilter End With End Sub HTH Charles Chickering Andibevan wrote: I am currently using the following loop to delete unwanted rows from a specified sheet on a workbook:- Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then .Rows(r).Delete End If Next r End With End Sub Is this the most efficient loop that I could use? It takes about 2 minutes to complete this loop on a spreadsheet with about 1400 rows. Admittedly this is on quite a small laptop so would be faster on a desktop but could I improve on my code. TIA Andi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster loop than this
Good point. Maybe one day I'll think of all these things like you :) Oh
well I started learning VBA about 8 months ago so I don't think I'm doing to bad so far. How about this: Range("A9",Cells(Range("A9").End(xlDown).Row,Range ("A9").End _ (xltoRight).Column)).SpecialCells(xlCellTypeVisibl e).EntireRow.Delete Charles Tom Ogilvy wrote: might need a bit more tweaking to avoid deleting the header row (or rows above A8 that are contiguous and filled): Just to demo from the immediate window. Range("A8:H8").Resize(20).Formula = "=Rand()" ? range("A9").CurrentRegion.Address $A$8:$H$27 -- Regards, Tom Ogilvy "Die_Another_Day" wrote: I think your loop is ok but there might be a better approach to this Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A8:A" & rw) rngActive.AutoFilter rngActive.AutoFilter Field:=7, Criteria1:="=*closed*", _ Operator:=xlAnd .Range("A9").CurrentRegion.SpecialCells(xlCellType Visible) _ .EntireRow.Delete rngActive.AutoFilter End With End Sub HTH Charles Chickering Andibevan wrote: I am currently using the following loop to delete unwanted rows from a specified sheet on a workbook:- Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook) Dim rngActive As Range, Cell As Range With wbCopy.Worksheets("Project Log Form") Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row Set rngActive = .Range("A9:A" & rw) Dim r As Long For r = rw To 9 Step -1 If UCase(.Range("G" & r)) = "CLOSED" Then .Rows(r).Delete End If Next r End With End Sub Is this the most efficient loop that I could use? It takes about 2 minutes to complete this loop on a spreadsheet with about 1400 rows. Admittedly this is on quite a small laptop so would be faster on a desktop but could I improve on my code. TIA Andi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
VBA | Individual Iterations faster than Loop Statement | Excel Programming | |||
Faster For-Next Loop? | Excel Programming | |||
Is there a faster way | Excel Programming |