Find and delete selection

[Loopy86]

Hi


What code will I use to find a person in col A, then delete all the cells it finds?

EG - I am searching for "Medcalf" so I can exclude him from a report, he has 6 entries. I want to delete the section, instead of a cell count so next time it runs, it will search the name range and not cells.

Columns("A:A").Select
Selection.Find(What:="Medcalf", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


Thanks

 

[Peter_SSs]

Can you provide more detail of just what is in the sheet to start with and just what you want to achieve in the sheet at the end?

 

[thunder_anger]

hi

paste this in sheet1 code page

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D1"), Target) Is Nothing Then Call DelMe
End Sub

where D1 is the cell that will contain the search object
DelME is another macro

insert a new module

sub DelMe()
dim mi as Range
dim si as Range
dim doo as Range
set doo = range ("d1")
set mi  = activesheet.range("a1:a100") 'contains data to search with in
for each si IN mi 
if si.value = doo.value then
si.clearcontent
si.offset(0,1).clearcontent 'repeat this line as many as you want and change the red bold digit as it will refer to the cells that holds the data you want to delete
end if
next si 
end sub

try and tell me but remember to make a back up for your data
macros are not reversible

 

[Loopy86]

Thanks for replying!

I stopped panicking, and this is what I have now:

ActiveSheet.Range("$A$1:$F$17").AutoFilter Field:=1, Criteria1:= _
"S. Medcalf"

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select


Loopy86

 

[Peter_SSs]

Thanks for replying!

I stopped panicking, and this is what I have now:

ActiveSheet.Range("$A$1:$F$17").AutoFilter Field:=1, Criteria1:= _
"S. Medcalf"

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select


Loopy86

What does your code do after that? Delete those rows?

You generally do not need to select things to work with them in vba and selecting can slow your code considerably.

Turning off ScreenUpdating while code runs also makes it run faster and stops screen flicker.

So, assuming that you do want to delete any "S. Medcalf" rows, you could consider this alternative code. Notice that the code doesn't 'select' anything.

Test in a copy of your workbook.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Range("$A$1:$F$17")<br>        .AutoFilter Field:=1, Criteria1:="S. Medcalf"<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .Offset(1).Resize(.Rows.Count - 1) _<br>            .SpecialCells(xlCellTypeVisible).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>