Find Next

[zoom38]

I cant get the following find next routine to work. Anyone have any idea what im doing wrong?




Sub RemovePlayerFromGameWorksheets()
Dim LastRow As Long
Dim c As Range
Dim FirstAddress As String
'Find The Last Row On The Game Worksheet
LastRow = Sheets("Game 1").Cells(Rows.Count, 2).End(xlUp).Row
For GameNum = 1 To 3
With Sheets("Game " & GameNum).Range("B5:B" & LastRow)
'On Error Resume Next
'Find The Player To Be Deleted
Set c = Range("B5:B" & LastRow).Find(What:=(DeletePlayer), _
LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
'Move To Cell A1
Range("a1").Select
End If
End With
Next GameNum
End Sub


Attached is a short version of the file im working on. I'm trying to delete all occurences of the name input but it will only delete the first occurence.

Thanks
Gary

[Ebrow]

Hi,

This is my first every reply to any forum. :hi:

What you were doing wrong, was that you were searching on a range that you had modified and therefore broke on the second time round on the search.

I have added a few lines of code to store each found address under an array. Once all entries have been found, the delete function is used to remove each row. This starting at the end so that you don't have to adjust the addresses in the array.


Sub RemovePlayerFromGameWorksheets()
Dim LastRow As Long
Dim c As Range
Dim FirstAddress As String
'variable used to store found addresses
Dim myRow(36000) As String
Dim myCounter As Long
myCounter = 1
'Find The Last Row On The Game Worksheet
LastRow = Sheets("Game 1").Cells(Rows.Count, 2).End(xlUp).Row
For GameNum = 1 To 3
With Sheets("Game " & GameNum).Range("B5:B" & LastRow)
'On Error Resume Next
'Find The Player To Be Deleted
Set c = Range("B5:B" & LastRow).Find(What:=(deleteplayer), _
LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
Do
'store each address found
myRow(myCounter) = c.Address
myCounter = myCounter + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < myRow(myCounter - 1)
'go from end to start to delete (prevent address adjustment)
For x = myCounter - 1 To 1 Step -1
Range(myRow(x)).EntireRow.Delete
Next
'Move To Cell A1
Range("a1").Select
End If
End With
Next GameNum
End Sub

[Bob Phillips]

Is it because you haven't properly qualified the find?

Maybe



Set c = Range("B5:B" & LastRow).Find(What:=(DeletePlayer), _
LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)


should be



Set c = .Find(What:=(DeletePlayer), _
LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

[zoom38]

Thanks for the replies.

XL your solution does not make a difference, it still deletes the first occurence of the name and then I still receive the following error message:

"Run-Time error 1004, Unable to get the FindNext Property of the range class"

And the debugger highlights the "Set c = .FindNext(c)" line.


Ebrow, I can't get yours to operate properly either. You routine deletes 2 rows on the first occurrence and 1 row (correct row) on the 2nd occurrence. It also only works on the first sheet, it is not working on successive sheets. Ive tried to fix it but my tinkering isn't working either.


Any suggestions?

Thanks
Gary

[mdmackillop]

See comments within the code


Option Explicit
Sub RemovePlayerFromGameWorksheets()
Dim LastRow As Long
Dim c As Range
Dim FirstAddress As String
Dim ToDelete As Range
Dim WS As Worksheet
Dim Rng As Range
Dim GameNum As Long
For GameNum = 1 To 3
'Clear FirstAddress for each loop
FirstAddress = ""
Set WS = Sheets("Game " & GameNum)
'You need to find the last row on each sheet but why bother?
'As quick to search the whole column.
'With Range(WS.Cells(5, 2), WS.Cells(Rows.Count, 2).End(xlUp))
'use
With WS.Columns(2)
'You've already set the range to be searched; Use necessary options only
Set c = .Find(What:=(DeletePlayer), LookAt:=xlWhole, _
LookIn:=xlValues, MatchCase:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
'You can't delete the range then use FindNext based upon it
If ToDelete Is Nothing Then
Set ToDelete = c
Else
Set ToDelete = Union(ToDelete, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
'Move To Cell A1 - Why????
'Range("a1").Select
End If
End With
'Delete found cells & rows
ToDelete.EntireRow.Delete
Set ToDelete = Nothing
Next GameNum
End Sub

[Ebrow]

hey, sorry, I forgot to add the lines:



End With
Next GameNum

It should read


End With
myCounter = 0
Next gamenum
]

[Paul_Hossler]

Another way would be to just loop from the bottom to the top, deleting the correct rows. Bottom to top avoids the row numbers shifting around on you

HTH

Paul



Sub RemovePlayerFromGameWorksheets()
Dim LastRow As Long, N As Long, GameNum As Long
For GameNum = 1 To 3
With Worksheets("Game " & GameNum)
'Find The Last Row On The Game Worksheet
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
'Find The Player To Be Deleted
For N = LastRow To 2 Step -1
If .Cells(N, 2).Value Like DeletePlayer Then .Rows(N).Delete
Next N
End With
Next GameNum
End Sub

[johnske]

Another way would be to just loop from the bottom to the top, deleting the correct rows. Bottom to top avoids the row numbers shifting around on you... Instead of multiple deletes, it's faster to just get the addresses of all the rows to be deleted and then do it with a single 'delete' (this also addresses the issue of 'the rows shifting around on you') :)

[Paul_Hossler]

johnske -- I think you're partially correct, but there wasn't that much data (<70 lines x 3 sheets), so the trade off was programming simplicity vs. what would probably be no perceptable run time decrease.

Based on my very un-scientific timing test, Union-ing a multi-area range and then deleting the Union's EntireRow really doesn't save anything over just deleting each of the multi-row areas sequentially.

[Paul_Hossler]

johnske -- I think you're 99.9% correct, but there wasn't that much data (<70 lines x 3 sheets), so the trade off was programming simplicity vs. what would probably be no perceptable run time savings.

Based on my very un-scientific timing test, using Union-ing to build a multi-area range and then deleting the Union's EntireRow really doesn't save anything over just deleting each of the multi-row areas sequentially.

Now, what I have done when there's a lot of sparse data that might need to be deleted is to

1. loop down and replace the test value with TRUE as a marker for the rows to be deleted
2. use an unused column as a "Original Position" indicator (usually the Row Num)
3. sort by the test value column to group the TRUEs
4. Use MarkerColumn.SpecialCells (xlCellTypeBoolean).EntireRow.Delete
5. sort by the Original Position col
6. Delete the Original Position Col

That seems to work faster when there's a lot of sparse rows.

JMHO

Paul

[mdmackillop]

I cant get the following find next routine to work. Anyone have any idea what im doing wrong?
Remember to answer the OP's question.

[zoom38]

MD, thanks for the assist, without extensive testing it appears to work great. Thanks for the comments also, helps me understand whats happening better.

Paul your routine also seems to work very well, again without extensive testing. You are correct that the file is quite small, it will have approx. 25 games and then 2 to 5 playoff games so a max of 30 sheets to loop through.

Ebrow, even with the changes I still can't get yours to work. Im going to stick with the others.

Thank You All
Gary