My current macro takes 20 minutes + to run. How can this code be made more efficient??!!

Hello VBA pro's! The code below runs REALLY slow. I was assisted on this forum before w/ a similar problem & was hoping someone out there could help.

Does this run so slow b/c it's not being run as a dynamic range? Variant Data? I would like to understand the logic behind why this runs as inefficiently as it does!

Private Sub CommandButton1_Click()
'Fill In Columns for CSV

Dim intStoreNumberRow As Integer
Dim intQuantityRow As Integer
Dim intStoreNumberColumn As Integer
Dim intQuantityColumn As Integer
Dim intSheet4Row As Integer
Dim intSheet4Counter As Integer

'First Row that Data Should Appear
intSheet4Row = 2
intSheet4Counter = 1

'Store Numbers are stored in F1:GR40
For intStoreNumberColumn = 6 To 200
    For intStoreNumberRow = 1 To 40
        'Quantities are stored in F43:GR500
        For intQuantityRow = 43 To 340
            'If there is a Store Number and Quantity, then proceeds to fill
            If Sheet3.Cells(intStoreNumberRow, intStoreNumberColumn) <> "" And Sheet3.Cells(intQuantityRow, intStoreNumberColumn) <> 0 Then
                'Increases Counter by 1
                intSheet4Counter = intSheet4Counter + 1
                'My Company - stays same
                Sheet4.Cells(intSheet4Counter, 1) = Sheet3.Cells(1, 2)
                'My Division - stays same
                Sheet4.Cells(intSheet4Counter, 2) = Sheet3.Cells(2, 2)
                'Corporation - stays same
                Sheet4.Cells(intSheet4Counter, 3) = Sheet3.Cells(4, 2)
                'Sold To/Company - stays same
                Sheet4.Cells(intSheet4Counter, 4) = Sheet3.Cells(5, 2)
                'Store Number - changes
                Sheet4.Cells(intSheet4Counter, 5) = Sheet3.Cells(intStoreNumberRow, intStoreNumberColumn)
                'UPC Code - changes
                Sheet4.Cells(intSheet4Counter, 6) = Sheet3.Cells(intQuantityRow, 5)
                'Order Type - stays same
                Sheet4.Cells(intSheet4Counter, 7) = "RPL"
                'Promotion Code - stays same
                Sheet4.Cells(intSheet4Counter, 8) = "..."
                'PO Number - stays same
                Sheet4.Cells(intSheet4Counter, 9) = Sheet3.Cells(8, 2)
                'Wholesale Price - stays same
                Sheet4.Cells(intSheet4Counter, 10) = Sheet3.Cells(11, 2)
                'Retail Price - stays same
                Sheet4.Cells(intSheet4Counter, 11) = Sheet3.Cells(13, 2)
                'Start Date - stays same
                Sheet4.Cells(intSheet4Counter, 12) = Sheet3.Cells(9, 2)
                'Cancel Date - stays same
                Sheet4.Cells(intSheet4Counter, 13) = Sheet3.Cells(10, 2)
                'Department Number -stays same
                Sheet4.Cells(intSheet4Counter, 14) = Sheet3.Cells(7, 2)
                'Replenishment Order Units - changes
                Sheet4.Cells(intSheet4Counter, 15) = Sheet3.Cells(intQuantityRow, intStoreNumberColumn)
            End If
        Next intQuantityRow
    Next intStoreNumberRow
Next intStoreNumberColumn


'Format Info
    Columns("A:A").Select
    Selection.NumberFormat = "00"
    Columns("B:B").Select
    Selection.NumberFormat = "00"
    Columns("C:D").Select
    Selection.NumberFormat = "General"
    Columns("E:E").Select
    Selection.NumberFormat = "0000"
    Columns("F:F").Select
    Selection.NumberFormat = "000000000000"
    Columns("G:H").Select
    Selection.NumberFormat = "General"
    Columns("J:K").Select
    Selection.NumberFormat = "0.00"
    Columns("L:M").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Columns("N:N").Select
    Selection.NumberFormat = "000"
    Columns("O:O").Select
    Selection.NumberFormat = "00000000000"

End Sub

Thanks in advance!!!!

Hi

Have you considered the amount of data you are processing here? 3 loops - 195, 40 and 298. Multiplying these comes to over 2.3m items to process.

I don't know how many of those will pass the test and be processed, but either way, there is a fair chunk of data to work through.

rylo

Hi Rylo & Dave-



I've attached the workbook w/ sample data to give you a better idea. I THINK the problem is that the code automatically loops 2.3 million times REGARDLESS of the actual parameters of the data in question. For example, in the "assortment" tab the sample data stops @ row 48 (not 340) & column 118 (not 195).



Note that the code that's in question is on the "info for .csv" tab. In order for you to use that macro however you need to first use the one on the "assortment info" tab.



Thanks for all your help!!!

Private Sub CommandButton2_Click()
'Clear Columns

'Dim intSheet4InfoColumn As Integer
'Dim lngSheet4InfoRow As Long

'For intSheet4InfoColumn = 1 To 40
'    For lngSheet4InfoRow = 2 To 65536
'        Sheet4.Cells(lngSheet4InfoRow, intSheet4InfoColumn).Clear
'    Next lngSheet4InfoRow
'Next intSheet4InfoColumn
Sheet4.Cells.Clear


End Sub

The above is a better way to clear a worksheet. As for the rest, I honestly don't know what you are doing. Maybe someone else comes along but in the meantime explain in english what you are doing?

Thanks.

Hi

For the 2 loop ends that you have mentioned, why don't you work out the current loop ends rather than have them fixed.

The 48 (last row) can be determined by

cells(rows.count,"B").end(xlup).row

and the last column by

cells(42,columns.count).end(xltoleft).column

This will at least reduce the volume of data being processed.

The third loop (currently 1 - 40) could be made dynamic by something like

cells(41,1).end(xlup).row

Again, that will further reduce your code processing.

HTH

rylo

Okay -

Given the format in tab 1 ("Assortment"), the first macro in this workbook in tab 2 ("Assortment info") is needed to remove the formatting from tab 1 as well as the subtotals.

What I need the 2nd macro to do in tab 3 ("Info for csv") is to take the data from tab 2 ("Assortment info" and put it into the form of individual records.

For example,

all records will have the same info in the top left of tab 2:
SAMPLE COMPANY: 02
SAMPLE DIVISION: 05
BRAND: SAMPLE
CORPORATION: SAMPLE
ACCOUNT NAME: SAMPLE
ACCOUNT #: 0012345
ACCOUNT DEPT: 123
PO #: 1234567
START DATE: 03/22/2012
CANCEL DATE: 04/02/2012
COST: $1.00
OWNED PRICE: $2.00
TICKET PRICE: $3.00

• Each record needs to have only 1 store # associated w/ it (this is the data in tab2 rows 1-40 in columns f-ds)
• Each record/door needs to show all unique "Item/SKU (UPC)s" associated w/ it.
• The ONLY fields that change are columns E/F/O

Thanks!