Using Macros to delete cells & rows based on specific criteria

[schnellsls]

Hello,

Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.

I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.

Next, in column C, I have...

Employee2's Name
Employee2's Number
SPACE
Employee3's Name
Employee3's Number
SPACE
SUB TOTAL
SPACE
SPACE
SUB TOTAL
SPACE
(and this repeats over and over... the number of employees may be more or less)

I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.

So, all I would have left in column C is

SUB TOTAL
SPACE

Please let me know if you require any more specific information.

Thank you very much for your help.

 

[schnellsls]

Well I may have spoke too soon. It worked for a few files.
I tried it on a report that was a bit bigger and it got about half way through and gave an error.

Run-time error '1004'
Application - defined or object-defined error

Then it highlights this line of the last bit of code you sent. (from the 3rd Macro)

ar.Resize(ar.Rows.Count - 3, 1).EntireRow.Delete

This is interesting...

On the spreadsheet, it looks like the spot where it got hung up was an area that for some reason breaks the mold of the normal data.

There is an extra row between NV SUTA and the 29-24. Might this throw the macro off?
=====================


Yeah, that was it. I tried opening the file again and before running the macros I deleted that extra row between NV SUTA and 29-24, and the macro ran perfectly.

Not sure if there is a way to check for that, but I can just do a manual check on the data before hand.


=====================

Also, 2 more things,

1. would there be a way to change the "With sheets ("sheet1") " name to say, "With Sheets ("CurrentSheet")?


2. Row # 7 contains a bunch of lines, basically ='s signs. Is there a way to clear contents of row 7?

Perhaps a first step of Macro 1...
Would it be something like...

Range("A7:I7")ClearContents ?


====================

Again, I really appreciate your help...

Thank you!

 

[circledchicken]

Not sure if there is a way to check for that, but I can just do a manual check on the data before hand.

I'm sure it is possible to handle that specific scenario, but then that might be one of many possible scenario's. If after running this a few times you notice there are particular scenarios that keep coming up you might want to integrate a solution into a macro - but that is more trial and error for you I think - obviously people here will be happy to help if you have any specific areas you get stuck on.

1. would there be a way to change the "With sheets ("sheet1") " name to say, "With Sheets ("CurrentSheet")?

Yes, if I understand correctly you can try the following (note this is standalone, you don't need to put it in brackets after the Sheets):

With ActiveSheet

2. Row # 7 contains a bunch of lines, basically ='s signs. Is there a way to clear contents of row 7?

Perhaps a first step of Macro 1...
Would it be something like...

Range("A7:I7")ClearContents ?

I thought this was already built into your first macro - SUTA_Format? This line:

Rows("7:7").ClearContents

 

[schnellsls]

Howdy,

A. Yes you were right 7:7 did clear everything. My bad. Sorry about making you go back through and looking at that.

B. With ActiveSheet worked great!

C. The extra space thing I will deal with as it comes up, and if I find it happens to be a regular thing, I will address it then.

As of now, I'd say this is all good and done.

Thank you very very much for helping me! I really appreciate your expertise.

Thank you!