VBA Delete Entire Row if Contains Certain Text

B

bigmacneb

Board Regular
Joined
Jul 12, 2005
Messages
93
I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks
 
Sort by date Sort by votes
max_cali,

If I understand your latest request, then, here is a new macro solution for you to consider.

Sample raw data in the active worksheet:


Excel 2007
ABCDEFG
1TRANSACTION_REF:ABC45678
2SUM OF PAYMENT_AMOUNT:100000
3PAYMENT_CCY:USD
4PAYMENT_VALUE_DATE:21/04/2017
5
6CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
7N1111111111127/02/20171111USD1111INV55555555.CSV
8N2222222222227/02/20172222USD2222INV66666666.CSV
9TRANSACTION_REF:ABC12345
10SUM OF PAYMENT_AMOUNT:1618.26
11PAYMENT_CCY:USD
12PAYMENT_VALUE_DATE:21/04/2017
13
14CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
15N33333333333429793333USD3333INV66666666.CSV
16
Sheet1


And, after the new macro:


Excel 2007
ABCDEFG
1ABC456781111111111127/02/20171111USD1111INV55555555.CSV
2ABC456782222222222227/02/20172222USD2222INV66666666.CSV
3ABC1234533333333333429793333USD3333INV66666666.CSV
4
5
6
7
8
9
10
11
12
13
14
15
16
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub max_cali_V3()
' hiker95, 04/25/2017, ME300330
Dim lr As Long, r As Range, t As String
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A1:A" & lr)
    .Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
    .Replace "CODE", "#N/A", xlWhole, , False
    .Replace "", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each r In .Range("A1:A" & lr)
    If InStr(r, "TRANSACTION_REF:") Then
      t = Right(r, Len(r) - 16)
    ElseIf r = "N" Then
      r = t
    End If
  Next r
  With .Range("A1:A" & lr)
    .Replace "TRANSACTION_REF*", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Columns(1).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the max_cali_V3 macro.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
hiker95 said:
Code: 
Sub max_cali_V2()
' hiker95, 04/24/2017, ME300330
Dim lr As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A1:A" & lr)
    .Replace "TRANSACTION_REF*", "#N/A", xlWhole, , False
    .Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
    .Replace "CODE", "#N/A", xlWhole, , False
    .Replace "", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Columns(1).AutoFit
End With
Application.ScreenUpdating = True
End Sub
Click to expand...
Here is another way to write your macro...
Code: 
[table="width: 500"]
[tr]
	[td]Sub max_cali_V3()
  Dim LastRow As Long
  With ActiveSheet
    LastRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    .Range("B1:B" & LastRow).SpecialCells(xlConstants, xlNumbers).EntireRow.Copy .Cells(LastRow + 1, "A")
    .Rows("1:" & LastRow).Delete
    .Columns(1).AutoFit
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Hi hiker95, it's totally perfect, the result is what I expect. By the way, can we adjust the code to automatically input the transaction reference (e.g. ABC45678 in cell A1, ABC12345 in cell A9) to the next columns "file name".
I'm using windows 7, Excel 2010. Thank you for your kind help.
Click to expand...

max_cali,

I missed that part, from your reply #158.

Sample raw data in the active worksheet:


Excel 2007
ABCDEFG
1TRANSACTION_REF:ABC45678
2SUM OF PAYMENT_AMOUNT:100000
3PAYMENT_CCY:USD
4PAYMENT_VALUE_DATE:21/04/2017
5
6CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
7N1111111111127/02/20171111USD1111INV55555555.CSV
8N2222222222227/02/20172222USD2222INV66666666.CSV
9TRANSACTION_REF:ABC12345
10SUM OF PAYMENT_AMOUNT:1618.26
11PAYMENT_CCY:USD
12PAYMENT_VALUE_DATE:21/04/2017
13
14CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
15N33333333333429793333USD3333INV66666666.CSV
16
Sheet1


And, after the new macro:


Excel 2007
ABCDEFG
1N1111111111127/02/20171111USD1111ABC45678
2N2222222222227/02/20172222USD2222ABC45678
3N33333333333429793333USD3333ABC12345
4
Sheet1




Code: 
Sub max_cali_V4()
' hiker95, 04/25/2017, ME300330
Dim lr As Long, r As Range, t As String
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A1:A" & lr)
    .Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
    .Replace "CODE", "#N/A", xlWhole, , False
    .Replace "", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each r In .Range("A1:A" & lr)
    If InStr(r, "TRANSACTION_REF:") Then
      t = Right(r, Len(r) - 16)
    ElseIf r = "N" Then
      r.Offset(, 6) = t
    End If
  Next r
  With .Range("A1:A" & lr)
    .Replace "TRANSACTION_REF*", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Columns("A:G").AutoFit
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi hiker95 & Rick, both codes work really well. Honestly, I'm currently not able to tell what's the difference between the two codes or the advantages of each :p Thank you all for your kind help.
 
Upvote 0
max_cali,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Upvote 0
hiker95 said:
Hi hiker95, it's totally perfect, the result is what I expect. By the way, can we adjust the code to automatically input the transaction reference (e.g. ABC45678 in cell A1, ABC12345 in cell A9) to the next columns "file name".
I'm using windows 7, Excel 2010. Thank you for your kind help.
max_cali,
Click to expand...
I missed that part, from your reply #158.
Code: 
Sub max_cali_V4()
' hiker95, 04/25/2017, ME300330
Dim lr As Long, r As Range, t As String
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A1:A" & lr)
    .Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
    .Replace "CODE", "#N/A", xlWhole, , False
    .Replace "", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each r In .Range("A1:A" & lr)
    If InStr(r, "TRANSACTION_REF:") Then
      t = Right(r, Len(r) - 16)
    ElseIf r = "N" Then
      r.Offset(, 6) = t
    End If
  Next r
  With .Range("A1:A" & lr)
    .Replace "TRANSACTION_REF*", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Columns("A:G").AutoFit
End With
Application.ScreenUpdating = True
End Sub
Click to expand...
Here is how I would modify the code I posted in Message #162 to do this...
Code: 
[table="width: 500"]
[tr]
	[td]Sub max_cali_V3revised()
  Dim X As Long, LastRow As Long, LastCol As Long, TRANS As Range, REFs As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlColumns, xlPrevious).Column
  Application.ScreenUpdating = False
  With Range("B1:B" & LastRow)
    .Value = Evaluate(Replace("IF(LEFT(@,11)=""TRANSACTION"",RIGHT(@,8),IF(@=""N""," & .Address & ",""""))", "@", .Offset(, -1).Address))
    Set TRANS = .SpecialCells(xlConstants, xlTextValues)
    Set REFs = .SpecialCells(xlConstants, xlNumbers)
    For X = 1 To REFs.Areas.Count
      Intersect(REFs.Areas(X).EntireRow, Columns(LastCol + 1)) = TRANS.Areas(X)(1).Value
    Next
    .SpecialCells(xlConstants, xlNumbers).EntireRow.Copy Cells(LastRow + 1, "A")
  End With
  Rows("1:" & LastRow).Delete
  Columns("A").AutoFit
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Hi dears, i need to modify the code above so that:
+ The first row (only first row for easy reference) after combination will be:

Excel 2010 32 bit
A​
B​
C​
D​
E​
F​
G​
1​
CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTTRANSACTION REF

<tbody>
</tbody>

+ Replace the File name in column G by respective Transaction ref in A1
+ Add few more columns right after column G: Column H: CCY (input from cell A3); Column I (input from cell A2); Column J (input from cell A4)...
+ There will be a blank row (would be perfect if it's formatted in light blue) between each transaction

The raw data is still the same (i make minor adjustment to show clear difference between two transactions):

Excel 2010 32 bit
A​
B​
C​
D​
E​
F​
G​
1​
TRANSACTION_REF:ABC45678
2​
SUM OF PAYMENT_AMOUNT:100000
3​
PAYMENT_CCY:USD
4​
PAYMENT_VALUE_DATE:21/04/2017
5​
6​
CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
7​
N
11111111111​
27/02/2017
1111​
USD
1111​
INV55555555.CSV
8​
N
22222222222​
27/02/2017
2222​
USD
2222​
INV66666666.CSV
9​
TRANSACTION_REF:ABC12345
10​
SUM OF PAYMENT_AMOUNT:1618.26
11​
PAYMENT_CCY:EUR
12​
PAYMENT_VALUE_DATE:25/04/2017
13​
14​
CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTFILE NAME
15​
N
33333333333​
42979​
3333​
USD
3333​
INV66666666.CSV
16​

<tbody>
</tbody>


The result will be:

Excel 2010 32 bit
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
CODEREFCOMPLETE DATETOTALCCYPMT AMOUNTTRANSACTION REF
CCY​
PAYMENT AMOUNTVALUE DATE
2​
N
11111111111​
27/02/2017
1111​
USD
1111​
ABC45678
USD​
10000​
21/04/2017
3​
N
22222222222​
27/02/2017
2222​
USD
2222​
ABC45678
USD​
10000​
21/04/2017
4​
5​
N
33333333333​
42979​
3333​
USD
3333​
ABC12345
EUR​
1618.26​
25/04/2017

<tbody>
</tbody>

Any help would be very much appreciated.
 
Last edited:
Upvote 0
Mark,

I came across your code, modified it slightly (below) but i'm receiving a Run-time error '13' Type Mismatch message. What have I done incorrectly?

Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
'========================================================================
Last = Cells(Rows.Count, "E").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "E").Value) = "#N/A" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
 
Upvote 0
rileyma said:
I came across your code, modified it slightly (below) but i'm receiving a Run-time error '13' Type Mismatch message. What have I done incorrectly?

If (Cells(i, "E").Value) = "#N/A" Then
Click to expand...
You cannot test for an error cell that way, try it this way...

If Cells(i, "E").Value = CVErr(xlErrNA) Then
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Delete entire rows with negative numbers in Amount Column - VBA
Replies
6
Views
355
Peter_SSs
Peter_SSs
D
  • Question
Find text in one cell and add data to the cell below it
Replies
7
Views
317
rlv01
R
N
  • Solved
Function to find a portion of text in a cell
Replies
5
Views
523
Fluff
Fluff
C
  • Question
VBA Copy Sort
Replies
4
Views
152
Keebler
Keebler
C
  • Solved
Lookup certain value in row and give back cell value in same column first row
Replies
7
Views
392
Fluff
Fluff

Forum statistics

Threads
1,215,219
Messages
6,123,687
Members
449,117
Latest member
Aaagu

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