Deleting rows based on cell value

[solnish]

Hi all! I’ve taken on some voluntary work for an animal trust and just need some help with a bit of code to clean it up.

I’m new to this site and have intermediate knowledge of excel. Any help would be much appreciated.

I have around 60 or so excel workbooks. Each workbook has 9 worksheets named March, April, May etc through to November.

In column BR of each worksheet I have a totals column, cell type is set to general. Each cell concerned contains =SUM. For ex. =SUM(BP13:BQ13). If the cells in this totals column equal the integer zero only (not 3500, 2440 etc) or is blank I would like the whole row deleted. I would like to do the above from row 13 to 350 inclusive.

I’m using Excel 2003 Professional Edition SP3

Is it possible to apply a macro to deal with the above on a whole workbook basis instead of me having to apply it to each sheet?

Many thanks in advance!

Jules

[ARowbot]

try this:

Please Login or Register  to view this content.

[PCI]

Give a chance to next code

Please Login or Register  to view this content.

[solnish]

Hi thank you for the quick response. How do I execute the code you've written?

Thanks

[PCI]

You have to put the code in a module in your file.
Developer > Record Macro > OK
Developer > Stop Recording
Developer > Macros > Macro1 > Edit
Then using Control +c and Control + v
copy and paste the macro sent to replace macro1 or to put it just before or after

OR
You could keep the file attached as a toolbox
Load the toolbox in Excel and the file to treat.
When you are in the file to treat do Control + a
(short key to launch the macro)

[solnish]

Hi thank you for the response.

Developer > Record Macro > OK

I'm not at all sure how to find the developer option you mentioned.

Is this in the visual basic editor?

Thanks again for assisting me.

Jules

[PCI]

" developer option"
See File > Option > Customize the Ribbon >
Select Developer in the Right Window
Note: If you want to develop macro you need this menu

[solnish]

I’m using Excel 2003 Professional Edition SP3

[PCI]

See Tools > Macro
- Record New Macro > OK
- Stop Recording
- Macros > Edit

[JOHN H. DAVIS]

Tools>Macro>Visual Basic Editor>Project - VBA Project>Insert Module. Code goes there.

[solnish]

Hi thank you for your input.


I'm opening the file>alt+f11>inserting a new module>saving and closing VBE>alt+f8>selecting macro name treat and running

It appears to work but only on the first month (march) and then errors with the following message:

Run-time error '1004':
Method 'Union' of object '_Global' failed

look forward to your response

Thanks

[PCI]

Did you first make a test with the file sent ? Is it working
If you have issue with your file, prepare and send a sample of your file, removing personal info.

[solnish]

Hi it's the trust's policy not to download to this laptop so I can't from this machine.


I've attached the file as requested though. Perhaps you could have a look for me and explain what needs altering for it to work on all sheets?

As you will notice the code has purged all rows with a total of zero and any blank rows.

thanking you kindly again

[solnish]

sorry now attached. It is zipped due to it being slightly over 1MB which is too big for the forum's rules

[PCI]

Here we are.
2 issues:
- You have to use extension .xlsm
- There was an error see next code

Please Login or Register  to view this content.

[solnish]

******Here we are.
******2 issues:
******- You have to use extension .xlsm
******- There was an error see next code

It seems to be working now. No sure what yo meant by using .xlsm unless you meant to rename the workbook with the .xlms suffix? I copied the code into a new module and removed the additional chars you'd placed to show me the line of code. Ran it and it seems to have purged. I haven't checked it manually yet but will do later today. Thank you so much.

Is it difficult to now apply that code to a batch of excel workbooks exactly the same setup as that one just with different data values. I have to apply it to around 65 books approximately, from 2015 approx back 65 years each workbook being a year. This would be wonderful if possible?

Can't thank you enough for your help.

[PCI]

Are all files in the same folder?

[solnish]

Hi I don't have them yet but they will be once I have access to them yes.

thanks

[PCI]

So it can be done, but please open a new thread to avoid confusion between the needs

[solnish]

Ok well thank you was again for your help on this. You've saved the trust a lot of time which is brilliant.

[solnish]

Here is the new thread to the second part of the issue I'm trying to automate

http://www.excelforum.com/excel-prog...ml#post4128083

[PCI]

To be deleted

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[solnish]

Hi are we suppose to be discussing this in the new thread I created oppose to here?

You've kind of lost me with the above info. It needs to be simplified steps. I'm not sure about the toolbox again I think we spoke about this before and it was something not available in the 2003 version of excel I'm using?

Thank you

[solnish]

I will close the thread and marked as solved as the original question is now answered.

[PCI]

I apologize, I posted an answer here and it was not the right thread as I suggested to open a new one.
I deleted the answer here and post it the new thread.

[solnish]

Hi again, I have now received all the files that need purging. When I run them (47 roughly) there are a handful that are giving a runtime error 13 type mismatch error.

Please help!

Thank you

[solnish]

it is yellow highlighting on the Treat method on the if statement below

For I = FR To LR
If .Cells(I, WkCol) = 0 Then Set DelRg = Union(DelRg, .Cells(I, 1))
Next
End With

[PCI]

Can you give more detail how it happens: are some files treated and can you post back the full macro or file prepared

[solnish]

Hi and thanks for the response again. I am running them all from the same folder as instructed, with the ToolBox.xls file. It starts to process the files and then stops with the type mismatch error. Code below

Please Login or Register  to view this content.

would you like me to post a file that has been treated OK and also one that is failing to run?

[solnish]

Ok I have attached a zip with the folder I'm working on. Inside are three excel files 1957.xls 1961.xls and ToolBox.xls.
1957 should purge without a problem. 1961 will give the type mismatch error I'm having the problem with.
Out of the 50 files needing to be processed around 12 are failing like this. #

I just open the toolbox.xls and then hit alt+f8 then select to run TreatAllFile macro.

many thanks look forward to your response

J

[PCI]

"would you like me to post a file that has been treated OK and also one that is failing to run? "

yes of course

[solnish]

please see post at top of page for attached files, thanks

[PCI]

There an issue because there an error in cell BR78
Bext code delete the row when there is 0 but don't the check when there is an error

Please Login or Register  to view this content.