Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find Causing Serious Lag
Hey All,
Long time user, never posted though. Here I have about 15,000 rows of data, all which have formulas in columns K:V. I select all of the rows which contain a "9" in column T as the value in the cell and cut and insert them into another sheet. The sorting is of course speedy and the macro will eventually do it's job, but it takes a LONG time for it to find the first cell with a "9". Since the largest value for column T is a "9", the sort puts all of the data at the bottom of the list which is part of the problem I'm sure. But reversing the logic will only start the find at the bottom instead and still have to go through the entire list. Is there a way to speed this up at all? Dim rng As Range Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes Range("T6").Select Set rng = Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) If rng Is Nothing Then End Else Range("T6").Select Range(Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).EntireRow, ActiveCell.Offset(0, -15).End(xlDown).Offset(0, 15).EntireRow).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find Causing Serious Lag
Ok so I placed a break after the find method and it returns the
"Object variable or with block variable not set" error. So maybe there is something else wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find Causing Serious Lag
This is the almost the same as the one I offered under your other posting.
Test it on a copy before installing in your original. Sub Tst() Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes lastRow = Cells(Rows.Count, 20).End(xlUp).Row With Worksheets(1).Range("T6:T" & lastRow) Set c = .Find(9, , LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If c Is Nothing Then Exit Sub Else rng = c.Address .Range(Cells(Range(rng).Row, 1), Cells(lastRow, 256)).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If End With End Sub "Kigol" wrote: Hey All, Long time user, never posted though. Here I have about 15,000 rows of data, all which have formulas in columns K:V. I select all of the rows which contain a "9" in column T as the value in the cell and cut and insert them into another sheet. The sorting is of course speedy and the macro will eventually do it's job, but it takes a LONG time for it to find the first cell with a "9". Since the largest value for column T is a "9", the sort puts all of the data at the bottom of the list which is part of the problem I'm sure. But reversing the logic will only start the find at the bottom instead and still have to go through the entire list. Is there a way to speed this up at all? Dim rng As Range Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes Range("T6").Select Set rng = Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) If rng Is Nothing Then End Else Range("T6").Select Range(Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).EntireRow, ActiveCell.Offset(0, -15).End(xlDown).Offset(0, 15).EntireRow).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find Causing Serious Lag
c keeps returning nothing so I changed With Worksheets(1). to With
Activesheet. It then finds the correct cell but errors on .Range(Cells(Range(rng).Row, 1), Cells(lastRow, 256)).Select with an Application or object defined error. But all in all it runs MUCH faster. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find Causing Serious Lag
Here are the mods I did. It works perfectly now. Thanks again.
Sub Tst() Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes lastRow = Cells(Rows.Count, 20).End(xlUp).Row With ActiveSheet.Range("T6:T" & lastRow) Set c = .Find(9, , LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If Not c Is Nothing Then rng = c.Address firstrow = c.Row Range(Cells(firstrow, 1), Cells(lastRow, 256)).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If End With On May 16, 3:56 pm, JLGWhiz wrote: This is the almost the same as the one I offered under your other posting. Test it on a copy before installing in your original. Sub Tst() Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes lastRow = Cells(Rows.Count, 20).End(xlUp).Row With Worksheets(1).Range("T6:T" & lastRow) Set c = .Find(9, , LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If c Is Nothing Then Exit Sub Else rng = c.Address .Range(Cells(Range(rng).Row, 1), Cells(lastRow, 256)).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If End With End Sub "Kigol" wrote: Hey All, Long time user, never posted though. Here I have about 15,000 rows of data, all which have formulas in columns K:V. I select all of the rows which contain a "9" in column T as the value in the cell and cut and insert them into another sheet. The sorting is of course speedy and the macro will eventually do it's job, but it takes a LONG time for it to find the first cell with a "9". Since the largest value for column T is a "9", the sort puts all of the data at the bottom of the list which is part of the problem I'm sure. But reversing the logic will only start the find at the bottom instead and still have to go through the entire list. Is there a way to speed this up at all? Dim rng As Range Application.ScreenUpdating = False Sheets("Summary").Rows("6:20000").Select Selection.Sort Key1:="Pricing Bucket", Order1:=xlAscending, Key2:="On Private List?", Order2:=xlAscending, Header:=xlYes Range("T6").Select Set rng = Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) If rng Is Nothing Then End Else Range("T6").Select Range(Cells.Find(What:="9", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).EntireRow, ActiveCell.Offset(0, -15).End(xlDown).Offset(0, 15).EntireRow).Select Selection.Cut Sheets("Excluded List").Activate Rows("7").Insert Sheets("Summary").Activate End If- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compatibility alert - can't find cells causing problem | Excel Discussion (Misc queries) | |||
zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
Cells.Find Causing Serious Lag | Excel Programming | |||
Add-in causing 'GHOST' cells to be selected | Excel Programming | |||
Causing all calculated cells to refresh... | Excel Programming |