Cells.Find Causing Serious Lag

[Kigol]

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

 

[Kigol]

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?

 

[Guest]

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]

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.

 

[Kigol]

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