Delete row based on cell value

[288enzo]

Hello,

I'm not using a table.

I would like to search column A for a specific value. I would like to delete the row where the specific value is found in column A and shift all rows up 1.

Please let me know if more info is needed. In my mind it seems fairly simple, but then again nothing is truly simple.

Thank you,

 

[Fluff]

What is the value?
Also how many rows of data do you have & how many rows to delete?

 

[288enzo]

What is the value?
Also how many rows of data do you have & how many rows to delete?

It's text that I'm looking for, for this purpose let's say "Text".
I'll have 15 rows of data. A4:E18 to be specific
I'll only have to delete 1 row. "Text" will only show up once in column A.

Thank you!

 

[Fluff]

Ok, how about

Sub enzo()
   Dim Fnd As Range
   Set Fnd = Range("A4:A18").Find("Text", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.EntireRow.Delete
End Sub

 

[cmowla]

I love to use Range.Find too, but using it will affect the options for Ctrl+F in Excel. (So I use it within software systems that I write, but I wouldn't generally recommend giving it to someone to use as a subroutine of their own.) Using match is safer in that regard and is releavant to this request (since it appears we are searching case insensitive and matching the entire cell's contents. And Range.Find doesn't find values in hidden columns.)

Here is more generalized code that takes in the sheet name, letter of the column to search, and the search.

Sub Test__Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This()
Call Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This("Sheet1", "A", "Bob")
End Sub

Sub Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This(sheetName As String, columnLetter As String, search As String)
On Error GoTo No_Match_Found__No_Row_Was_Deleted
Dim rowNumberToDelete As Long
rowNumberToDelete = Application.WorksheetFunction.Match(search, Sheets(sheetName).Range(columnLetter & ":" & columnLetter), 0)
Sheets(sheetName).Range(rowNumberToDelete & ":" & rowNumberToDelete).EntireRow.Delete
No_Match_Found__No_Row_Was_Deleted:
End Sub

 

[288enzo]

Ok, how about

Sub enzo()
   Dim Fnd As Range
   Set Fnd = Range("A4:A18").Find("Text", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.EntireRow.Delete
End Sub

Worked perfectly, thank you. It also sent me off to learn more about "Find" and all the parameters you can change.

 

[288enzo]

I love to use Range.Find too, but using it will affect the options for Ctrl+F in Excel. (So I use it within software systems that I write, but I wouldn't generally recommend giving it to someone to use as a subroutine of their own.) Using match is safer in that regard and is releavant to this request (since it appears we are searching case insensitive and matching the entire cell's contents). (And Rand.Find doesn't find values in hidden columns.)

Here is more generalized code that takes in the sheet name, letter of the column to search, and the search.

Sub Test__Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This()
Call Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This("Sheet1", "A", "Bob")
End Sub

Sub Delelete_The_First_Row_In_This_Sheet_In_This_Column_That_Contains_This(sheetName As String, columnLetter As String, search As String)
On Error GoTo No_Match_Found__No_Row_Was_Deleted
Dim rowNumberToDelete As Long
rowNumberToDelete = Application.WorksheetFunction.Match(search, Sheets(sheetName).Range(columnLetter & ":" & columnLetter), 0)
Sheets(sheetName).Range(rowNumberToDelete & ":" & rowNumberToDelete).EntireRow.Delete
No_Match_Found__No_Row_Was_Deleted:
End Sub

Thank you for another example.

 

[cmowla]

Worked perfectly, thank you. It also sent me off to learn more about "Find" and all the parameters you can change.

Range.Find is the programmatic way of doing Ctrl+F. The extra parameters correspond to those in the options button in that window (which you click to expand).

But you have to be extremely careful when using it. Unless you specify all of the parameters (and understand specifically what they need to be for your purposes), I wouldn't recommend to use it until you do!

I suffered serious (subtle) bugs from not specifying ALL of the parameters in the past. For example, if you use more than one subroutine that uses Range.Find, if one of them searches for the entire cell contents but the other does not (or if one is case-sensitive but the other is not, etc.), but you don't specify the value of that parameter in both, then it can literally be "at random" that your software system will work . . . because there is actually a third factor at play . . . Excel's Range.Find options menu!

By the way, according to MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010, Range.Find is the most efficient when the search involves multiple columns.

 

[288enzo]

Range.Find is the programmatic way of doing Ctrl+F. The extra parameters correspond to those in the options button in that window (which you click to expand).

But you have to be extremely careful when using it. Unless you specify all of the parameters (and understand specifically what they need to be for your purposes), I wouldn't recommend to use it until you do!

I suffered serious (subtle) bugs from not specifying ALL of the parameters in the past. For example, if you use more than one subroutine that uses Range.Find, if one of them searches for the entire cell contents but the other does not (or if one is case-sensitive but the other is not, etc.), but you don't specify the value of that parameter in both, then it can literally be "at random" that your software system will work . . . because there is actually a third factor at play . . . Excel's Range.Find options menu!

By the way, according to MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010, Range.Find is the most efficient when the search involves multiple columns.

That is great info, thank you for taking the time. Much appreciated.

 

[cmowla]

Also, there's more.

Here is a generalized "First Occurrence" search subroutine that I wrote a little while back: (As you can see in the comments, Range.Find also has caveats when dealing with an Excel Table object or just a plain range)!

Sub Test__FirstOccurrence()
MsgBox FirstOccurrence(ThisWorkbook, ActiveSheet.name, "A7:A101111", "", True, False)
End Sub

Function FirstOccurrence( _
book As Workbook, _
sheetName As String, _
searchRange As String, _
search As String, _
CaseSensitive As Boolean, _
match_Entire_Cell_Contents As Boolean _
)
'Where I originally first heard about Range.Find:  https://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba (In the "Sub HighlightFindValues()".)

'NOTE:  CStr(lastDataRow + 1) when searching for the first cell that is blank ("") or contains contents ("*") . . . definitely do this
'when searching through rows of a column in an Excel TABLE!!!!  I don't know why it doesn't work correctly otherwise!

Dim look_At_This As Variant
If match_Entire_Cell_Contents = True Then
    look_At_This = xlWhole
Else
    look_At_This = xlPart
End If

FirstOccurrence = 0 'Returns 0 if no match is found.
Dim FoundCell As Range
Dim myRange As Range
Dim LastCell As Range
Set myRange = Sheets(sheetName).Range(searchRange)
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(What:=search, after:=LastCell, MatchCase:=CaseSensitive, SearchDirection:=xlNext, lookat:=match_Entire_Cell_Contents, SearchOrder:=xlByRows, LookIn:=xlFormulas) 'xlFormulas  , LookIn:=xlValues)

If Not FoundCell Is Nothing Then
    FirstOccurrence = FoundCell.row
Else
    'If there is no occurrence,
    Exit Function
End If

End Function

EDIT:
And in this sub, I don't provide a parameter in the test sub to search by value or by formulas. This one automatically searches within formulas "LookIn:=xlFormulas. That parameter is important also.