Find a value and delete the next 8 rows

[PeteW1959]

I have a table that has been extracted from a large pdf document. They are actually delivery notes and I am trying to nail down a stock discrepancy.

I want to find a value in a cell, then delete the next 8 rows. I have done quite a bit with VBA in Access, but never in Excel and I'm not sure where to start.

I have attached 2 spreadsheets, Before.xlsx and After.xlsx I want to find 'PROD1' in Before.xlsx then get rid of the next 8 rows to give the result in After.xlsx

Can anyone point me in the right direction? I will want to do a more complicated procedure later, but if I use this as a starting point I should be able to figure out the rest myself.

[JOHN H. DAVIS]

I don't see any attachments?

[PeteW1959]

Ooops, my bad. Files now attached.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[PeteW1959]

Thanks, John, that does the trick. I am trying to understand how this code works so could you please explain a couple of things.

Why use "B" in the range command?

What does the 3 in the End command do?

[JOHN H. DAVIS]

I used Column B to find the last row because there were no gaps. I could have used activesheet.usedrange.rows.count, which would have found the last cell in the range with data.

.End(3).Row - same as .End(xlUp).Row. A shorter version to write xlUp in this instance.

[PeteW1959]

Ah, I see. Thanks for your help John.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as SOLVED. Refer to link for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html