Horkenheimer
New Member
- Joined
- Feb 13, 2020
- Messages
- 16
- Office Version
- 2019
- Platform
- MacOS
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
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