Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
What is the best code to use to look for a name ( col A) and delete the whole
row. -- capt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
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 "capt" wrote: What is the best code to use to look for a name ( col A) and delete the whole row. -- capt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
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 "capt" wrote: What is the best code to use to look for a name ( col A) and delete the whole row. -- capt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
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) "capt" wrote in message ... What is the best code to use to look for a name ( col A) and delete the whole row. -- capt |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
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" wrote: What is the best code to use to look for a name ( col A) and delete the whole row. -- capt |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup with criteria and delete row
Thank you FSt1.
Just the job. it works great. -- capt "FSt1" wrote: 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" wrote: What is the best code to use to look for a name ( col A) and delete the whole row. -- capt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows if specific criteria not met. | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Delete row depending on criteria | Excel Discussion (Misc queries) | |||
How do I delete a row from a spreadsheet if criteria is not met i. | Excel Worksheet Functions |