Find certain criteria delete that row and the next 5 rows as well

[gatorcc]

I am trying to write a macro that will find a certain criteria in Column F and once it matches that criteria it deletes that row and the next 5 rows.

I know if I use this command it will delete the one row that matches my criteria but not the following 5 rows as well.
If .Value = "REPORT" The .EntireRow.Delete

[mdmackillop]

If .Value = "REPORT" Then .resize(6).EntireRow.Delete

[Paul_Hossler]

What happens if lines look like this?

100: REPORT
101: something
102: something
103: REPORT
104: something
105: something
106: something
107: something
108: something
109: something

REPORT+5 would delete 100-105, but not 103-108.

Would you ever have less that 5 lines before the next "REPORT"?

Paul

[Bob Phillips]

What happens if lines look like this?

100: REPORT
101: something
102: something
103: REPORT
104: something
105: something
106: something
107: something
108: something
109: something

REPORT+5 would delete 100-105, but not 103-108.

Would you ever have less that 5 lines before the next "REPORT"?

Paul

The OP specifically said the next 5 as well.

[mikerickson]

100: REPORT
101: something A
102: something B
103: REPORT
104: something C
105: something D
106: something E
107: something F
108: something G
109: something H
What direction is the code going?

Top to bottom:
Delete 100-106
no more "report"s
macro ends, top line is "something F"

Bottom to top:
Delete 103:108
Delete 100,101,102,109,110,111
macro ends, no data

[Paul_Hossler]

The OP specifically said the next 5 as well.


True, but the 'Next 5' after line 100 "Report" would delete the "REPORT" line 103, before the 5 lines after that could be deleted. leaving lines 106 - 109.

Similarly if you go from bottom to top, Lines 103 - 108 would be correctly deleted first, but when you reached the REPORT in 100, then 'Next 5 as well', would delete 100 (of course), 101, 102, BUT would also delete the new 103, 104, and 105.

I was only asking if there would always be at least 5 delete-able lines between "REPORT" lines. If there are, then Mac's code



If .Value = "REPORT" Then .resize(6).EntireRow.Delete


would work fine

Paul

[gatorcc]

If .Value = "REPORT" Then .resize(6).EntireRow.Delete

I got a compile error. Invalid or unqualified reference. Also can I get this to start on row 7?

[mdmackillop]

Please post your code.