Excel Macro 1

[schnabs]

Hello,
I'm looking for some help with a Macro. It needs to be code because I need to run it 90 times per month, in the span of one day, so it needs to happen quick. I have columns A through G filled in a delinquency list for payments. If the cell in column G is 0.00 for a specific customer, I need to delete all of that customers charges. I need the code to run the entire length until the end of the list. I appreciate any feedback or help!

 

[ItIsHardToProgram]

What Skip says is totaly true.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Can I hear an AMEN!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Look at what is written between the brackets on the line you highlighted...

the only thing the function does is start deleting rows when its equal 0, until you get a specific number that tells it to stop, you need to replace these brackets by the specific number, also you must specify the name of your worksheet in the other brackets I have placed...

But you should think reconfiguring your excel spreadsheet.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

By the way, schnabs, how did you generate your report, with customer totals?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

Well the customization of the report is limited to an extent. This is a direct export of Quickbooks Enterprise v8. I agree it is not very efficient the way Quickbooks exports data, but the ultimate goal is to filter out all customers with a zero balance, and make it accessible to anyone, even those computer illiterate. So I guess instead of saying I'm looking for the fastest way to do this, what I should have said is I'm looking for the most user friendly, most automated way to do this. Any suggestions and I'm all ears.
Thanks.

 

[SkipVought]

So you can't generate a report in Quickbooks of customers with zero balance?

The major problem is that, in many case, a REPORT output is an extremely poor source for data analysis, because a REPORT often is not generated in a format that Excel can use.

Use a formula to propogate the Customer Name intoevery row of your report. Assuming that your customers are in column A, and assuming that column Z is the first empty column to the right of your report....
[tt]
Z2: =if(A2="",Z1,A2)
[/tt]
With the customer name on every row in your report, it will be a simple task to identify and delete customers that meet the AutoFilter criteria.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

Wow what a different approach. I never looked at it like that. I will play around with it and let you know how it goes.
Thanks!

 

[schnabs]

Hmmm. Maybe I'm not thinking out of the box enough, but I'm still having a problem. I was thinking of uploading my file if you wouldn't mind taking a look at it?

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

I will look at your workbook.

I will post my replys here.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

[tt]
C2: =IF(LEFT(A2,5)="Total",B2,C3)
[/tt]
Filter on 0 in column C

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

Awesome Skip thanks! Such a simple command that seemed so hard. Looks like I have a ways to go to really master Excel.
Thanks again!