VBA Dialog box FindAll

[ziad alsayed]

dear all

i am trying use the dialog box "find" to find the Word "TOTAL" in column G, so after finding it i will press Find all and all cells with the Word "Total" will be select, so now i will loop through each cell and delete the entire row completly.

i tried to use the recording but it didn't work. i succeed to launch the find dialog box but don't know what to do after .

below is what i found

Sub RemoveTotal()
    Columns("G:G").Select
    Application.Dialogs(xlDialogFormulaFind).Show
End Sub

appreciate any help.

 

[GlennUK]

Why don't you use Autofiltering instead? Delete the visible rows after applying the filter.

See this thread:

http://www.mrexcel.com/forum/showthread.php?t=565969&highlight=autofilter

 

[ziad alsayed]

dear GlennUK

thanks for your reply,this works and i tried it before, just wnat to learn doing it by using the dialog box find

hope you can assist.

 

[p45cal]

dear GlennUK

thanks for your reply,this works and i tried it before, just wnat to learn doing it by using the dialog box find

hope you can assist.

I don't think you'll be able to do it (doing FindAll is probably doable, but not the Select All afterwards. Try:

Sub RemoveTotal()
Dim cellsToDelete As Range
With Columns("G:G")
    Set c = .Find("Total")
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Set cellsToDelete = c
        Do
            Set c = .FindNext(c)
            Set cellsToDelete = Union(cellsToDelete, c)
        Loop Until c.Address = FirstAddress
    End If
End With
cellsToDelete.EntireRow.Delete
End Sub

 

[ziad alsayed]

thanks P45cal , this works perfectly and i learned a new way of doing it

appreciate your help as usual.