increase speed and simplify VBA

First off, I would turn off calculation and write the formula all at once...it'll be much faster. Then I'd convert the formulas to values. Then I'd sort it and delete everything at once. Then turn calcualtion back on (if it was on in the first place.)

Or, if sorting is not an option then loop using an array to test and store the records you want to keep. This be very fast.

Give this a shot:

Then I'd sort it and delete everything at once.

Click to expand...

...or apply a filter and delete the displayed rows.

Denis

Then I'd sort it and delete everything at once.

Click to expand...

...or apply a filter and delete the displayed rows.

Denis

Click to expand...

With as many records that she has it would take a long time to delete. There are posts about this here. With a lot of records it is undeniably much faster to first sort the data.

Let Excel do the work for you. Use the data table you have as the source for a MS Query created database query. Specify the Purchaser and the Date and Sum(Amount) as the fields of interest. Add a criterion of Sum(Amount)>=3000.

Now, all you have to do is update the source table and refresh the existing query.

Edit: For a brief introduction to MS Query see
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

Hi everyone!

I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.

I've created a helper column where I've inserted the formula

Code:

=SUMPRODUCT(--(DateRange=A2),--(PurchaserRange=G2),--(VoidRange<>"Y"),AmtRange)

All of the ranges are dynamic.
{snip of image}

The actual sheet has more than 12,000 records. I import, create dynamic named ranges and insert the helper column in VBA. I would love to simplify the macro in order to speed it up. This is the code to add the helper column which takes the most time:

Code:

Sub Add_Helper()
Dim Cell                      As Range

  Application.Calculation = xlManual
  Application.ScreenUpdating = False
  For Each Cell In Range("DateRange")
    Cell.Offset(0, 11) = "=SUMPRODUCT(--(DateRange=" & Cell.Address & "),--(PurchaserRange=" & Cell.Offset(0, 5).Address & "),--(VoidRange<>""Y""),AmtRange)"
  Next Cell
  Calculate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Can anyone help?

Click to expand...

Use the data table you have as the source for a MS Query created database query.

Click to expand...

Gosh darn good idea there, Tushar.

Yeah, too many jump to using VBA when simpler solutions exist. I create Query tables, PivotTables, and associated charts whenever possible. Then, I convert the workbook into a template. Maintenance becomes a cinch and performance is as fast as Excel itself! Not to mention that my clients can make minor tweaks themselves without having to involve me.

Last year I created a template and an associated add-in to analyze XML data downloaded via a web service. Yesterday, I decided to add a new analysis module to it. My work consisted of starting with the current template, adding to the main data table 3 new columns with formulas that computed additional data items I needed, creating a new PT based on the data, a new chart based on the PT, and saving the result as a new template.

The changes to the code? None. The code was originally written to automatically extend the formulas to all new data rows.

Testing? Minimal. The first -- and only -- pass revealed an error in one of the three formulas I added. Fixed it and I was done.

Total time? On a product I hadn't looked at in nearly a year, a little under an hour.

Use the data table you have as the source for a MS Query created database query.

Click to expand...

Gosh darn good idea there, Tushar.

Click to expand...

One thing I could never figure out is, for example:

Let's say I have a random list of part numbers, let's say 1000 part numbers. There's no parameters in regards to the parts themselves, but let's say I wanted to look at 2006 and 2007 sales.

can this be accomplised using Query, and how easy is it?

I have actually come across this and my solution was a brute force find/copy/paste solution from a pivot table. It's not entirely slow, but it is certAINLY AN *UGLY* way of going about it.

do you have a recommendation?

One thing I could never figure out is, for example:

Let's say I have a random list of part numbers, let's say 1000 part numbers. There are no parameters in regards to the parts themselves, but let's say I wanted to look at 2006 and 2007 sales.

The data is contained in a PT and may contain say 20,000 part numbers with 4 years of sales in total (part numbers down the side, years along the top.)

Can this be accomplised using Query, efficiently, and how easy is it?

I know Query has the "and" comparison function, however, doing this 1000 times seems crazy, and probably not possible. So, can Query do this some other way?

I have actually come across this and my solution was a brute force find/copy/paste solution from a pivot table. It's not entirely slow, but it is certainly an *UGLY* way of going about it.

Do you have a recommendation?

Use the source of the PT as the source for the query. You can specify 2 values for a column with a connecting OR condition as in Year=2006 OR Year=2007. Let MSQuery / SQL worry about the speed.

You can take it a step further. If you specify the two values as parameters, you could enter them as values in two cells and let MSQuery update the table whenever either cell value changes. Assuming you want all years between the 1st and 2nd years, the criteria in MS Query should be > = Year1 AND < = Year2.

One thing I could never figure out is, for example:

Let's say I have a random list of part numbers, let's say 1000 part numbers. There are no parameters in regards to the parts themselves, but let's say I wanted to look at 2006 and 2007 sales.

The data is contained in a PT and may contain say 20,000 part numbers with 4 years of sales in total (part numbers down the side, years along the top.)

Can this be accomplised using Query, efficiently, and how easy is it?

I know Query has the "and" comparison function, however, doing this 1000 times seems crazy, and probably not possible. So, can Query do this some other way?

I have actually come across this and my solution was a brute force find/copy/paste solution from a pivot table. It's not entirely slow, but it is certainly an *UGLY* way of going about it.

Do you have a recommendation?

Click to expand...