VLookup with criteria and delete row

[capt]

What is the best code to use to look for a name ( col A) and delete the whole
row.

 

[Don]

I am not a fan of deleting a row, but I would look into filters. Either on
that row or you can do a vlookup of names from a 2nd list you want to delete
, then if found it will show up on your filter then you can delete row and it
should only delete the visible selection

 

[Mike H]

A formula can only alter the cell it is in, you can't say something like

=if(a1="myname")then delete this row
so you need VB to delete rows. Right click your sheet tab, view code and
paste this in. If the name "myname" (which you can change to what you want)
appears in column A the entire row will be deleted.

Sub sonic()
For x = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(x, 1).Value = "myname" Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike

 

[Chip Pearson]

You can't use formulas to delete rows -- you must use VBA code. The
following code will delete all the rows on Sheet1 that have "abc" in column
A.

Dim RowNdx As Long
Dim LastRow As Long
With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(.Cells(RowNdx, "A").Value, _
"abc", vbTextCompare) = 0 Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[FSt1]

hi
and another line of logic.
Sub findDelete()
Dim c As String
Dim Rng As Range
c = InputBox("Enter string to delete.")
Set Rng = Nothing
Set Rng = Range("A:A").Find(what:=c, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'Rng.Select 'optional
Rng.EntireRow.Delete shift:=xlUp
End Sub

regards
FSt1

 

[capt]

Thank you FSt1.
Just the job. it works great.