VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

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

 

[jimgun]

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

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

 

[hiker95]

Hello hiker95
Can you help me for making macro vba excel in this thread?
https://www.mrexcel.com/forum/excel-...ml#post4800001

thank you very much be4

jimgun,

No more Private Messages, please.

I have looked at your NEW thread, and, I can not see the logic to solve your new request.

Maybe someone else on MrExcel can assist you.

 

[max_cali]

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!

	A	B	C	D
1	PAYMENT_VALUE_1
2	PAYMENT_TERM
3
4
5	valuea1	valueb1	valuec1	valued1
6	valuea2	valueb2	valuec2	valued2
7	valuea3	valueb3	valuec3	valued3
8	valuea4	valueb4	valuec4	valued4
9
10	PAYMENT_VALUE
11	PAYMENT_TERM
12	VALUE
13
14	valuea1	valueb1	valuec1	valued1
15	valuea2	valueb2	valuec2	valued2
16	valuea3	valueb3	valuec3	valued3
17	valuea4	valueb4	valuec4	valued4
18

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

<colgroup><col><col><col span="3"></colgroup><tbody></tbody>

 

[hiker95]

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
	A	B	C	D
1	PAYMENT_VALUE_1
2	PAYMENT_TERM
3
4
5	valuea1	valueb1	valuec1	valued1
6	valuea2	valueb2	valuec2	valued2
7	valuea3	valueb3	valuec3	valued3
8	valuea4	valueb4	valuec4	valued4
9
10	PAYMENT_VALUE
11	PAYMENT_TERM
12	VALUE
13
14	valuea1	valueb1	valuec1	valued1
15	valuea2	valueb2	valuec2	valued2
16	valuea3	valueb3	valuec3	valued3
17	valuea4	valueb4	valuec4	valued4
Sheet1

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

 

[hiker95]

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
	A	B	C	D	E
1	PAYMENT_VALUE_1
2	PAYMENT_TERM
3
4
5	valuea1	valueb1	valuec1	valued1
6	valuea2	valueb2	valuec2	valued2
7	valuea3	valueb3	valuec3	valued3
8	valuea4	valueb4	valuec4	valued4
9
10	PAYMENT_VALUE
11	PAYMENT_TERM
12	VALUE
13
14	valuea1	valueb1	valuec1	valued1
15	valuea2	valueb2	valuec2	valued2
16	valuea3	valueb3	valuec3	valued3
17	valuea4	valueb4	valuec4	valued4
18
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E
1	valuea1	valueb1	valuec1	valued1
2	valuea2	valueb2	valuec2	valued2
3	valuea3	valueb3	valuec3	valued3
4	valuea4	valueb4	valuec4	valued4
5	valuea1	valueb1	valuec1	valued1
6	valuea2	valueb2	valuec2	valued2
7	valuea3	valueb3	valuec3	valued3
8	valuea4	valueb4	valuec4	valued4
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.

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.

 

[max_cali]

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)

	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	CODE	REF	COMPLETE DATE	TOTAL	CCY	PMT AMOUNT	FILE 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:USD
12	PAYMENT_VALUE_DATE:21/04/2017
13
14	CODE	REF	COMPLETE DATE	TOTAL	CCY	PMT AMOUNT	FILE NAME
15	N	33333333333	9/1/2017	3333	USD	3333	INV66666666.CSV

<tbody>
</tbody>

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:

	A	B	C	D	E	F	G
1	N	11111111111	27/02/2017	1111	USD	1111	INV55555555.CSV
2	N	22222222222	27/02/2017	2222	USD	2222	INV66666666.CSV
3	N	33333333333	9/1/2017	3333	USD	3333	INV66666666.CSV

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

Thank you in advance and welcome all help.

 

[hiker95]

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
	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	CODE	REF	COMPLETE DATE	TOTAL	CCY	PMT AMOUNT	FILE 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:USD
12	PAYMENT_VALUE_DATE:21/04/2017
13
14	CODE	REF	COMPLETE DATE	TOTAL	CCY	PMT AMOUNT	FILE NAME
15	N	33333333333	42979	3333	USD	3333	INV66666666.CSV
16
Sheet1

And, after the new macro:

Excel 2007
	A	B	C	D	E	F	G
1	N	11111111111	27/02/2017	1111	USD	1111	INV55555555.CSV
2	N	22222222222	27/02/2017	2222	USD	2222	INV66666666.CSV
3	N	33333333333	42979	3333	USD	3333	INV66666666.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).

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.

 

[max_cali]

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:

	A	B	C	D	E	F	G
1	N	11111111111	27/02/2017	1111	USD	1111	ABC45678
2	N	22222222222	27/02/2017	2222	USD	2222	ABC45678
3	N	33333333333	42979	3333	USD	3333	ABC12345

<thead>
</thead><tbody>
</tbody>

 

[hiker95]

Hi hiker95, it's totally perfect, the result is what I expect.

max_cali,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

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".

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?