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
hiker95 said:
jimgun,

I prefer to see the actual raw data worksheet(s), and, what the results should look like.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
Click to expand...



Hello hiker95
Can you help me for making macro vba excel in this thread?
https://www.mrexcel.com/forum/excel-questions/1000112-formula-rearrange-table.html#post4800001

thank you very much be4
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi guys, I'm very new in VBA to initiate any code. I'm learning VBA to serve my daily work at office.

I'm in need of writing a code deleting entire rows if they contain specific texts (PAYMENT_VALUE, PAYMENT_TERM) or the row is entirely blank. Only valuea1, valueb1,..., valuexy left in the sheet. Any help would be very much appreciated!

[TABLE="class: grid, width: 411"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]PAYMENT_VALUE_1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]PAYMENT_TERM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]valuea1[/TD]
[TD]valueb1[/TD]
[TD]valuec1[/TD]
[TD]valued1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]valuea2[/TD]
[TD]valueb2[/TD]
[TD]valuec2[/TD]
[TD]valued2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]valuea3[/TD]
[TD]valueb3[/TD]
[TD]valuec3[/TD]
[TD]valued3[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]valuea4[/TD]
[TD]valueb4[/TD]
[TD]valuec4[/TD]
[TD]valued4[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]PAYMENT_VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]PAYMENT_TERM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]valuea1[/TD]
[TD]valueb1[/TD]
[TD]valuec1[/TD]
[TD]valued1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]valuea2[/TD]
[TD]valueb2[/TD]
[TD]valuec2[/TD]
[TD]valued2[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]valuea3[/TD]
[TD]valueb3[/TD]
[TD]valuec3[/TD]
[TD]valued3[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]valuea4[/TD]
[TD]valueb4[/TD]
[TD]valuec4[/TD]
[TD]valued4[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 411"]
<colgroup><col><col><col span="3"></colgroup><tbody></tbody>[/TABLE]
 
Upvote 0
max_cali,

Welcome to the MrExcel forum.

Your instructions are not clear.

If the following is what your raw data looks like in the active worksheet:


Excel 2007
ABCD
1PAYMENT_VALUE_1
2PAYMENT_TERM
3
4
5valuea1valueb1valuec1valued1
6valuea2valueb2valuec2valued2
7valuea3valueb3valuec3valued3
8valuea4valueb4valuec4valued4
9
10PAYMENT_VALUE
11PAYMENT_TERM
12VALUE
13
14valuea1valueb1valuec1valued1
15valuea2valueb2valuec2valued2
16valuea3valueb3valuec3valued3
17valuea4valueb4valuec4valued4
Sheet1



So that we can get it right on the first try, what should the results look like?
 
Upvote 0
max_cali,

Here is a macro solution for you to consider that is based on your flat text display, that will adjust to the number of raw data rows, and, columns, and, that uses two arrays in memory, and, should be very fast.

Sample raw data in the active worksheet:


Excel 2007
ABCDE
1PAYMENT_VALUE_1
2PAYMENT_TERM
3
4
5valuea1valueb1valuec1valued1
6valuea2valueb2valuec2valued2
7valuea3valueb3valuec3valued3
8valuea4valueb4valuec4valued4
9
10PAYMENT_VALUE
11PAYMENT_TERM
12VALUE
13
14valuea1valueb1valuec1valued1
15valuea2valueb2valuec2valued2
16valuea3valueb3valuec3valued3
17valuea4valueb4valuec4valued4
18
Sheet1


And, after the macro:


Excel 2007
ABCDE
1valuea1valueb1valuec1valued1
2valuea2valueb2valuec2valued2
3valuea3valueb3valuec3valued3
4valuea4valueb4valuec4valued4
5valuea1valueb1valuec1valued1
6valuea2valueb2valuec2valued2
7valuea3valueb3valuec3valued3
8valuea4valueb4valuec4valued4
9
10
11
12
13
14
15
16
17
18
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).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub max_cali()
' hiker95, 04/23/2017, ME300330
Dim a As Variant, i As Long, c As Long, lr As Long, lc As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
  ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a, 1)
    If Len(a(i, 1)) > 5 And Left(a(i, 1), 5) = "value" Then
      j = j + 1: o(j, 1) = a(i, 1)
      For c = 2 To UBound(a, 2)
        If Not a(i, c) = vbEmpty Then
          o(j, c) = a(i, c)
        End If
      Next c
    End If
  Next i
  .Range(.Cells(1, 1), .Cells(lr, lc)).ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(UBound(o, 2)).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 macro.
 
Last edited:
Upvote 0
Hi hiker95, your post is kinda long that I'll not quote here, however it's such a warm welcome and I really appreciate your great quick response.

Sorry for my unclear explanation in previous post.

I have a file consolidated from many sources, so the format will be the same and repeated.

I enclose here the complete format of my file (sorry I don't know how to input the sheet into your format)
[TABLE="class: grid, width: 786"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]TRANSACTION_REF:ABC45678
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]SUM OF PAYMENT_AMOUNT:100000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]PAYMENT_CCY:USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]PAYMENT_VALUE_DATE:21/04/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]CODE
[/TD]
[TD]REF
[/TD]
[TD]COMPLETE DATE
[/TD]
[TD]TOTAL
[/TD]
[TD]CCY
[/TD]
[TD]PMT AMOUNT
[/TD]
[TD]FILE NAME
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD]N
[/TD]
[TD="align: right"]11111111111
[/TD]
[TD]27/02/2017
[/TD]
[TD="align: right"]1111
[/TD]
[TD]USD
[/TD]
[TD="align: right"]1111
[/TD]
[TD]INV55555555.CSV
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD]N
[/TD]
[TD="align: right"]22222222222
[/TD]
[TD]27/02/2017
[/TD]
[TD="align: right"]2222
[/TD]
[TD]USD
[/TD]
[TD="align: right"]2222
[/TD]
[TD]INV66666666.CSV
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD]TRANSACTION_REF:ABC12345
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD]SUM OF PAYMENT_AMOUNT:1618.26
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD]PAYMENT_CCY:USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD]PAYMENT_VALUE_DATE:21/04/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14
[/TD]
[TD]CODE
[/TD]
[TD]REF
[/TD]
[TD]COMPLETE DATE
[/TD]
[TD]TOTAL
[/TD]
[TD]CCY
[/TD]
[TD]PMT AMOUNT
[/TD]
[TD]FILE NAME
[/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD]N
[/TD]
[TD="align: right"]33333333333
[/TD]
[TD="align: right"]9/1/2017
[/TD]
[TD="align: right"]3333
[/TD]
[TD]USD
[/TD]
[TD="align: right"]3333
[/TD]
[TD]INV66666666.CSV
[/TD]
[/TR]
</tbody>[/TABLE]

My requirement is: Any row containing: "TRANSACTION_REF", "SUM OF PAYMENT_AMOUNT", "PAYMENT_CCY", "PAYMENT_VALUE_DATE", "CODE" or BLANK will be deleted.

After deleting, the file should be:

[TABLE="class: grid, width: 794"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]N[/TD]
[TD="align: right"]11111111111[/TD]
[TD]27/02/2017[/TD]
[TD="align: right"]1111[/TD]
[TD]USD[/TD]
[TD="align: right"]1111[/TD]
[TD]INV55555555.CSV[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]N[/TD]
[TD="align: right"]22222222222[/TD]
[TD]27/02/2017[/TD]
[TD="align: right"]2222[/TD]
[TD]USD[/TD]
[TD="align: right"]2222[/TD]
[TD]INV66666666.CSV[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]N[/TD]
[TD="align: right"]33333333333[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD="align: right"]3333[/TD]
[TD]USD[/TD]
[TD="align: right"]3333[/TD]
[TD]INV66666666.CSV[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

Thank you in advance and welcome all help.
 
Upvote 0
max_cali,

Here is another macro solution for you to consider, based on your new flat text displays, that does not use arrays, or, looping thru the rows in the active worksheet, and, should be very fast.

Sample raw data:


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/20171111USD1111INV55555555.CSV
2N2222222222227/02/20172222USD2222INV66666666.CSV
3N33333333333429793333USD3333INV66666666.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_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

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_V2 macro.
 
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.

The result will be like:
ABCDEFG

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]N[/TD]
[TD="bgcolor: #FFFFFF"]11111111111[/TD]
[TD="bgcolor: #FFFFFF"]27/02/2017[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1111[/TD]
[TD="bgcolor: #FFFFFF"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1111[/TD]
[TD="bgcolor: #FFFFFF"]ABC45678[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]N[/TD]
[TD="bgcolor: #FFFFFF"]22222222222[/TD]
[TD="bgcolor: #FFFFFF"]27/02/2017[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2222[/TD]
[TD="bgcolor: #FFFFFF"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2222[/TD]
[TD="bgcolor: #FFFFFF"]ABC45678[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]N[/TD]
[TD="bgcolor: #FFFFFF"]33333333333[/TD]
[TD="bgcolor: #FFFFFF, align: right"]42979[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3333[/TD]
[TD="bgcolor: #FFFFFF"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3333[/TD]
[TD="bgcolor: #FFFFFF"]ABC12345[/TD]

</tbody>
 
Upvote 0
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".
Click to expand...

max_cali,

So that I can get it right on the next try, please supply another screenshot, with the N's manually completed by you, for the results that you are now looking for?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
236
Peter_SSs
Peter_SSs
M
  • Solved
Delete entire row contains SUM words for each sheet contains 8000 rows
2
Replies
15
Views
498
Maklil
M
C
  • Solved
Highlight entire rows in used range where any cell contains searched text
Replies
5
Views
413
CorrieAnn
C
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
417
mlan1604
M
S
  • Solved
Delete rows that contain highlighting
Replies
5
Views
73
Serafin54
S

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

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