• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for a Chart Clean Up

Mike808

New Member
Excel Ninjas-
So, in my uploaded file, I have some relatively simple clean up's I have to do before importing into my Access Database. I want to create a Macro to do it, but I know for the Macro to be universal (used on any of my spreadsheets), I need to use some if/then statements. The two clean up items are these:

The spreadsheet is actually several spreadsheets together. At the end of each spreadsheet, there is roll up summary info that I do not need. Therefore, I delete these rows including the headers for the next spreadsheet. I would think that I could develop a statement saying "If column "A" cells are null, then delete the row".

The last clean up is there are cells within the spreadsheet that have formatting errors, numbers stored as text and so on. I would like a blanket clean up tool for that.

Any help would be great!! Mahalo from Hawaii!!

Mike808
 

Attachments

  • FY17 Airframes CHINO 1_4_17.xlsx
    227 KB · Views: 2
Can you please elaborate on what you call a Roll Up Summary? by using a worksheet and ranges as an example

Would it be ok to save the cleaned up file as a new file?

Do you need the Summary worksheet included in the output?
 
I also note that a lot of Rows in the CNAF worksheet have no Date in Column A

What should happen to those?
a) Use the date above,
b) delete the row
c) something else?
 
Can you please elaborate on what you call a Roll Up Summary? by using a worksheet and ranges as an example

Would it be ok to save the cleaned up file as a new file?

Do you need the Summary worksheet included in the output?

I also note that a lot of Rows in the CNAF worksheet have no Date in Column A

What should happen to those?
a) Use the date above,
b) delete the row
c) something else?

Hui-

Why did I know that it was gonna be you?? First, the Summary worksheet is not needed. All of the rest of the worksheets are. Column A is the Depot header, and there is no blanks. Therefore, if there is a blank in Column A, then it should be deleted, i.e. Row 1-5. To answer the questions:

With this spreadsheet, Rows 1 -5, 76-83, 207-214, 416-424, 465-483, 492-501, 538-572 are not needed.

Examples of format issues are: J51 and F50.
 
Mike

Can you please see how this goes:

It is documented enough that you'll see what it does
 

Attachments

  • FY17 Airframes CHINO 1_4_17.xlsm
    222.2 KB · Views: 3
Hui-

This is AWESOME! Is there a quick easy code to consolidate the sheets into one sheet under the top headers of the CNAF worksheet?? I would think a simple copy and paste all starting at A2, but actual working eludes me.

Mahalo again for the awesome work!!

Mike808
 
Back
Top