IF search is false, then delete entire row

M

megmcc17

New Member
Joined
Jul 25, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello! I am relatively new to excel macros. Someone else set this one up and I am trying to modify it to fit my needs. Original was "If cell contains "DBA", say ___, otherwise say ___" I want the otherwise to delete the entire row instead. This is what I've modified the section to:

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=""Hi "" &RC[-7]& ""! "" & IF(ISNUMBER(SEARCH(""DBA"",RC[-2])),"""" &RC[-2]& """",EntireRow.Delete)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J200"), Type:=xlFillDefault
Range("J2:J200").Select
ActiveWindow.SmallScroll Down:=-63
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


But this is what I'm getting when I run it:
1690293217410.png



How do I make it delete the entire row if RC[-2] does not contain "DBA"?

Thank you!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi @megmcc17.
Welcome to the MrExcel forum.​
Please accept my warmest greetings and sincere hope that all is well.​

Replace your code with this and try it:
VBA Code: 
Sub delete_if_does_not_contain_DBA()
  Application.ScreenUpdating = False
  With Range("J2:J" & Range("H" & Rows.Count).End(3).Row)
    .Formula = "=SEARCH(""DBA"",H2)"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.delete
    .Value = ""
  End With
  Application.ScreenUpdating = True
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Thank you! This site has been really helpful in learning to modify macros.

When I replace the section in question with your code copy/pasted, I get compile error right before the start of your code.

The code in my original post is only one small section of a macro with many actions in it, both before and after this part. Probably should have said that in my original post - sorry! If you need the full macro, here it is with the part I'm trying to modify in green:


Sub DBAready()
'
' DBAready Macro
'

'
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("C:S").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.Replace What:=", ", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("C2").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C200"), Type:=xlFillDefault
Range("C2:C200").Select
ActiveWindow.SmallScroll Down:=-78
Range("A1").Select
ActiveCell.FormulaR1C1 = "Last Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "First Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "First Proper"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Today's date"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Days since LAS"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Next 3"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Next 1"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Text to send"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Text formula"
Range("I1").Select
Selection.Font.Bold = True
Range("E2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E200"), Type:=xlFillDefault
Range("E2:E200").Select
ActiveWindow.SmallScroll Down:=-48
Range("F2").Select
ActiveCell.FormulaR1C1 = "=DAYS(RC[-1],RC[-2])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F200"), Type:=xlFillDefault
Range("F2:F200").Select
ActiveWindow.SmallScroll Down:=-66
Range("G2").Select
Columns("G:G").ColumnWidth = 27.33
ActiveCell.Formula2R1C1 = _
"=INDEX(NextThree!C[-3],MATCH(1,(NextThree!C[-5]=RC[-6])*(NextThree!C[-4]=RC[-4]),0))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G200"), Type:=xlFillDefault
Range("G2:G200").Select
ActiveWindow.SmallScroll Down:=-63
Range("H2").Select
ActiveCell.Formula2R1C1 = _
"=INDEX(NextThree!C[-3],MATCH(1,(NextThree!C[-6]=RC[-7])*(NextThree!C[-5]=RC[-5]),0))"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H200"), Type:=xlFillDefault
Range("H2:H200").Select
ActiveWindow.SmallScroll Down:=-66
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=""Hi "" &RC[-7]& ""! "" & IF(ISNUMBER(SEARCH(""DBA"",RC[-2])),"""" &RC[-2]& """",EntireRow.Delete)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J200"), Type:=xlFillDefault
Range("J2:J200").Select
ActiveWindow.SmallScroll Down:=-63
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Last Name"
Columns("A:C").Select
Selection.ColumnWidth = 11.56
Columns("D:E").Select
Selection.ColumnWidth = 11.33
Columns("F:F").Select
Selection.ColumnWidth = 11.33
Columns("H:H").ColumnWidth = 11.89
Range("I1").Select
Columns("I:I").ColumnWidth = 88.11
Columns("I:I").ColumnWidth = 96.33
Rows("1:1").Select
Columns("D:D").ColumnWidth = 16.89
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
 
Upvote 0
megmcc17 said:
I get compile error right before the start of your code.
Click to expand...
In which line do you have the error?
What does the error say?

You must provide that information so that I can help you.

But check the following:

You'll understand that I can't test your macro because I have no idea how your data is, but I can do some checking.

I updated your code, try the following macro:

VBA Code: 
Sub DBAready()
  ' DBAready Macro
  
  Application.ScreenUpdating = False
  Application.CutCopyMode = False
  
  Columns("A:B").Select
  Selection.delete Shift:=xlToLeft
  Columns("B:E").Select
  Selection.delete Shift:=xlToLeft
  Columns("C:S").Select
  Selection.delete Shift:=xlToLeft
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("A:A").Select
  Selection.Replace What:=", ", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False ', FormulaVersion:=xlReplaceFormula
  
  Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
    "(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
    TrailingMinusNumbers:=True
  
  Columns("C:C").Select
  Selection.delete Shift:=xlToLeft
  Columns("B:B").Select
  Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False ', FormulaVersion:=xlReplaceFormula2
  
  Range("C2").Select
  ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
  Range("C2").Select
  Selection.AutoFill Destination:=Range("C2:C200"), Type:=xlFillDefault
  
  Range("A1").Value = "Last Name"
  Range("B1").Value = "First Name"
  Range("C1").Value = "First Proper"
  Range("E1").Value = "Today's date"
  Range("F1").Value = "Days since LAS"
  Range("G1").Value = "Next 3"
  Range("H1").Value = "Next 1"
  Range("I1").Value = "Text to send"
  Range("J1").Value = "Text formula"
  Range("I1").Font.Bold = True
  Range("E2").Formula = "=TODAY()"
  
  Range("E2").Select
  Selection.AutoFill Destination:=Range("E2:E200"), Type:=xlFillDefault
  
  Range("F2").FormulaR1C1 = "=DAYS(RC[-1],RC[-2])"
  Range("F2").Select
  Selection.AutoFill Destination:=Range("F2:F200"), Type:=xlFillDefault
  
  Range("G2").Select
  Columns("G:G").ColumnWidth = 27.33
  ActiveCell.FormulaR1C1 = _
    "=INDEX(NextThree!C[-3],MATCH(1,(NextThree!C[-5]=RC[-6])*(NextThree!C[-4]=RC[-4]),0))"
  
  Range("G2").Select
  Selection.AutoFill Destination:=Range("G2:G200"), Type:=xlFillDefault
  
  Range("H2").Select
  ActiveCell.FormulaR1C1 = _
    "=INDEX(NextThree!C[-3],MATCH(1,(NextThree!C[-6]=RC[-7])*(NextThree!C[-5]=RC[-5]),0))"
  
  Range("H2").Select
  Selection.AutoFill Destination:=Range("H2:H200"), Type:=xlFillDefault
  
  With Range("J2:J" & Range("H" & Rows.Count).End(3).Row)
    .Formula = "=SEARCH(""DBA"",H2)"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.delete
    .Value = ""
  End With
      
  Columns("A:C").ColumnWidth = 11.56
  Columns("D:E").ColumnWidth = 11.33
  Columns("F:F").ColumnWidth = 11.33
  Columns("H:H").ColumnWidth = 11.89
  Columns("I:I").ColumnWidth = 96.33
  Columns("D:D").ColumnWidth = 16.89
  Rows("1:1").Select
  With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
164
Coyotex3
C
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
322
kevin9999
K
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
984
Joe4
Joe4
R
  • Question
Filter & Delete Deleting Header Row Suddenly
Replies
0
Views
233
reberryjr
R
C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
376
kvsrinivasamurthy
K

Forum statistics

Threads
1,215,072
Messages
6,122,968
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top