A macro to format data

[tomberlins]

I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.

Is there a way to delete the last couple of lines of data of a spreadsheet?

[Palmetto]

Please Login or Register  to view this content.

Counts rows down column-A and deletes last two rows.

[tomberlins]

So I have never typed the language to produce a macro I just simply record the steps I use to format, can I just copy and paste what you entered into my current macro to make it work?

Follow up to the overall issue, I need to delete lines that have "0" or "-" in particular columns can I write language to do that too?

[Palmetto]

can I just copy and paste what you entered into my current macro to make it work?

As it is just basic, somewhat "generic" code you ** should not ** have any problems with just appending it to your current code (probably at the end of your current code).

Follow up to the overall issue, I need to delete lines that have "0" or "-" in particular columns can I write language to do that too?

Yes. VBA can be written for this, but you should supply more details or post a sample workbook.

[tomberlins]

I have added a sample booklet. Of course I tried to remove any sensitive data. Basically I need the first two rows deleted, the third row replaced with a header row, the last two rows deleted, and than any row that contains a "0" or "-" in the two bolded columns to be deleted.

[Palmetto]

Please Login or Register  to view this content.

EDIT: overlook the comment to delete a column. Code is now amended for this requirement, as well as including code to autofit the columns per the contents of cells in row 1. Attached new copy of the workbook with revised code.

[tomberlins]

Alright so you are pretty awesome here, can I adjust the language to delete "-" and "less than or equal to 0", instead of just "0"

[Palmetto]

can I adjust the language to delete "-" and "less than or equal to 0", instead of just "0"

I revised the code in the my earlier post for <= 0.

Note: I did not revise the code in the workbook to this latest request. Just delete to old code and copy the revised code into the workbook.

[tomberlins]

So this is awesome I can't tell you how much of a time saver it is. I assume this is more knowledge of VBE than excel?? Could you help with two other ammendments - I would like to delete the last column and I would like to sort all the data by the header row descending

First by Column B (MFG)

then by Column G (Sales)

And how do I learn more????

[Palmetto]

Revised code to sort per your request:

Please Login or Register  to view this content.