no find all on Mac excel - is there a workaround?

[Horkenheimer]

Hi there,

I gather there is no Find All selection on the Find & Replace tab of excel. Is there a quick workaround? My ultimate goal is to find all the cells in a row that contain an "_" and then select the entire rows of those cells and delete them.
Because there is no find all selection, I am using a VBA macro as pasted below. But this macro is still running 40 minutes later?! Does anyone have any ideas of whether this is the correct method or whether I should try something else?

Thanks in advance!



Sub FindAll()

'PURPOSE: Find all cells containing a specified values
'SOURCE: www.TheSpreadsheetGuru.com

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

'What value do you want to find (must be in string form)?
fnd = "12"

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If

Set rng = FoundCell

'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)

'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)

'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do

Loop

'Select Cells Containing Find Value
rng.Select

Exit Sub

'Error Handler
NothingFound:
MsgBox "No values were found in this worksheet"

End Sub

 

[smozgur]

Try this.
Find the rows with cells containing "_" and then delete matching rows.
No need to verify previously found range since the cells containing "_" will be deleted with the entire row. So it won't be an endless loop.

Sub findTextAndDeleteEntireRow()
Dim fnd As Range
    Set fnd = ActiveSheet.UsedRange.Find("_")
    Do Until fnd Is Nothing
        fnd.EntireRow.Delete xlUp
        Set fnd = ActiveSheet.UsedRange.FindNext
    Loop
End Sub