Macro to find and clear contents...

[rcicconetti]

I would like a macro to attach to a button and complete the following task.

If I enter a name in A3 and run the macro, it should search for the contents of A3 and clear all instances of that name on the sheet. Then clear A3 as well.

This will be on a scheduling assistant and the process will be used to eliminate an employee from the schedule.

Thank you

 

[Richard U]

Put this in a code module, and assign it to the button if you're using it directly on a spreadsheet, or a call to it if you are using a user form.

sub Remove_Employee

Dim rng_finder As Range
dim first, last as range

With Worksheets("MySheet").usedrange
    Set rng_finder = .Find(cells(3,1).text, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng_finder Is Nothing Then
        Set first = rng_finder
        Do
            Set last = rng_finder
            last.value =""
            Set rng_finder = .FindNext(rng_finder)
        Loop While Not rng_finder Is Nothing And rng_finder.Address <> first.Address
    End If
End With
cells(3,1).value = ""

end sub

 

[rcicconetti]

I changed "MySheet" to ActiveSheet and the cell was actually A5, so I changed the cell to (5,1).
tHE BUGGER found a problem with ***last.value =""

Any suggestions?

sub Remove_Employee

Dim rng_finder As Range
dim first, last as range

With ActiveSheet.usedrange
    Set rng_finder = .Find(cells(5,1).text, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng_finder Is Nothing Then
        Set first = rng_finder
        Do
            Set last = rng_finder
            last.value =""
            Set rng_finder = .FindNext(rng_finder)
        Loop While Not rng_finder Is Nothing And rng_finder.Address <> first.Address
    End If
End With
cells(5,1).value = ""

end sub

[/QUOTE]

 

[Richard U]

instead of last.value = ""

try Cells(last.row,last.column) = ""

 

[rcicconetti]

Hi there,
Sorry for the late response. I'm having connectivity problems abroad.
The bugger moved to the next line now, Run Time Error '91' "Object Variable or With block variable not set"
Loop While Not rng_finder Is Nothing And rng_finder.Address <> first.Address