VBA running slow--help

[molinamike]

Hello Gurus,

I am running VBA to copy data from a closed csv file and then paste into a specific cell within an open workbook. This workbook has fifteen worksheets and each sheet has a similar code for their respective sheet. The problem is that the code is running way to slow. Below is an example of the code for one of the sheets. Would someone review the code being used and tell me if I can make any adjustments that would speed things up? I really appreciate any help that can be provided.

Sub TransferData_MOD()
Application.ScreenUpdating = False

'showallrecods turns off filters
Call ShowAllRecords

'the below codes deletes all previous data before bringing in new data
Sheets("MOD").Activate
Sheets("MOD").Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

'code used to bring in new data
Workbooks.Open Filename:="C:\DB3\MOD.CSV"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("DB3.xlsb").Activate
Sheets("MOD").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("MOD.CSV").Close SaveChanges:=False
Windows("DB3.xlsb").Activate

'the below lines copies helper cells containing formulas and then pastes them as values
Sheets("MOD").Range("P3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[RCBricker]

Hello Gurus,

I am running VBA to copy data from a closed csv file and then paste into a specific cell within an open workbook. This workbook has fifteen worksheets and each sheet has a similar code for their respective sheet. The problem is that the code is running way to slow. Below is an example of the code for one of the sheets. Would someone review the code being used and tell me if I can make any adjustments that would speed things up? I really appreciate any help that can be provided.

Sub TransferData_MOD()
Application.ScreenUpdating = False

'showallrecods turns off filters
Call ShowAllRecords

'the below codes deletes all previous data before bringing in new data
Sheets("MOD").Activate
Sheets("MOD").Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

'code used to bring in new data
Workbooks.Open Filename:="C:\DB3\MOD.CSV"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("DB3.xlsb").Activate
Sheets("MOD").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("MOD.CSV").Close SaveChanges:=False
Windows("DB3.xlsb").Activate

'the below lines copies helper cells containing formulas and then pastes them as values
Sheets("MOD").Range("P3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

not sure if it will run all that much faster but give this a try. Basically the exact same code just using best practices.

Sub TransferData_MOD()
Dim wbDES As Workbook, wbMOD As Workbook
Dim ws As Worksheet, wsDES As Worksheet
Dim rng As Range
Dim lngROW As Long, lngCOL As Long

    Set wbDES = ThisWorkbook
    Set wsDES = Sheets("MOD")
    
    Application.ScreenUpdating = False
    'showallrecods turns off filters
    Call ShowAllRecords
     'the below codes deletes all previous data before bringing in new data
    With wsDES
        lngROW = Range("A3").End(xlDown).Row
        lngCOL = Range("A3").End(xlToRight).Column
        Set rng = wsDES.Range(wsDES.cells(3, 1), wsDES.cells(lngROW, lngCOL))
        rng.EntireRow.Delete
    End With
     'code used to bring in new data
    Set wbMOD = Workbooks.Open(filename:="C:\DB3\MOD.CSV")
    Set ws = ActiveSheet
    With ws
        lngROW = Range("A1").End(xlDown).Row
        lngCOL = Range("A1").End(xlToRight).Column
        Set rng = ws.Range(wsDES.cells(1, 1), ws.cells(lngROW, lngCOL))
        rng.Copy
    End With
    wsDES.Range("A1").Paste
    Application.CutCopyMode = False
    wbMOD.Close False
    'the below lines copies helper cells containing formulas and then pastes them as values
    With wsDES
        lngROW = Range("P3").End(xlDown).Row
        lngCOL = Range("P3").End(xlToRight).Column
        Set rng = wsDES.Range(wsDES.cells(3, 16), wsDES.cells(lngROW, lngCOL))
        rng.Copy
        rng.PasteSpecial xlPasteValues
    End With
End Sub

 

[RickXL]

Hi,

I think this should run faster:

Sub TransferData_MOD()
    Dim WBCSV   As Workbook
    Dim wsMOD   As Worksheet
    Dim ary     As Variant
    
    Application.ScreenUpdating = False
    
    Set WBCSV = Workbooks.Open(Filename:="C:\DB3\MOD.CSV")
    ary = WBCSV.Worksheets(1).UsedRange
    
    Call ShowAllRecords
    Set wsMOD = ThisWorkbook.Worksheets("MOD")

    With wsMOD
        '.Cells.Clear
        .Range("A3", .Range("A3").SpecialCells(xlCellTypeLastCell)).Clear
        .Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary
        WBCSV.Close SaveChanges:=False
    End With
    
    Application.ScreenUpdating = True
End Sub

First, a question ...
Why do you clear the Worksheet MOD from A3 then overwrite from A1? Why not just clear everything?
If you decide to do that you can un-comment the Cells.Clear in the code above and remove the line that follows it.

The structure of the code follows your example. But ...
It uses UsedRange to determine how much data there is to be copied.
Instead of using the ClipBoard it uses a Variant called ary.
Using the ClipBoard forces Excel to make many versions of the data because it does not know how it will be pasted or even which program it will be pasted into. Using a Variant is limiting in that it assumes that only values will be required and that the data will be pasted into Excel.

I have assumed that only Values are required to be copied and not Formulas or Formats etc and that the Code will be in the same Workbook as the MOD Worksheet.


Regards,

 

[RCBricker]

Hi,

I think this should run faster:

Sub TransferData_MOD()
    Dim WBCSV   As Workbook
    Dim wsMOD   As Worksheet
    Dim ary     As Variant
    
    Application.ScreenUpdating = False
    
    Set WBCSV = Workbooks.Open(Filename:="C:\DB3\MOD.CSV")
    ary = WBCSV.Worksheets(1).UsedRange
    
    Call ShowAllRecords
    Set wsMOD = ThisWorkbook.Worksheets("MOD")

    With wsMOD
        '.Cells.Clear
        .Range("A3", .Range("A3").SpecialCells(xlCellTypeLastCell)).Clear
        .Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary
        WBCSV.Close SaveChanges:=False
    End With
    
    Application.ScreenUpdating = True
End Sub

First, a question ...
Why do you clear the Worksheet MOD from A3 then overwrite from A1? Why not just clear everything?
If you decide to do that you can un-comment the Cells.Clear in the code above and remove the line that follows it.

The structure of the code follows your example. But ...
It uses UsedRange to determine how much data there is to be copied.
Instead of using the ClipBoard it uses a Variant called ary.
Using the ClipBoard forces Excel to make many versions of the data because it does not know how it will be pasted or even which program it will be pasted into. Using a Variant is limiting in that it assumes that only values will be required and that the data will be pasted into Excel.

I have assumed that only Values are required to be copied and not Formulas or Formats etc and that the Code will be in the same Workbook as the MOD Worksheet.


Regards,

Rick this is some nice code.

question about the ary variant and how that works (never tried to pick up a block of data that way).

It looks to me to basically create an array of the data. when you paste it back into the new sheet using:

.Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary

the resize is where I am a bit confused. Can you explain why and how the syntax works?

 

[RickXL]

If you make ary a Variant then you can read in a Range without having to specify the size of anything. Excel will work it all out for you. (Annoyingly, it does something slightly different if you read in only one cell.)

As for the output steps ...

If you just try:

Range("A1") = ary

then it only puts data in cell A1. You need to specify the size of the Range required to take all the data from the Array.

If you knew the Array was 10 rows by 20 columns you could say:

Range("A1").Resize(10, 20)

However, the size of the Array is not known beforehand.

You can find the size of the Array by using UBound(). For instance:

Debug.Print UBound(ary, 1)

will print the size of the first dimension of the Array in the Immediate Window. If you want the second dimension you need to change the 1 for a 2.

.Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary

will do the whole thing. It will specify the start cell (A1), and Resize the Range to match the Array size. I use the same syntax every time I do it so it comes automatically now.


Was it faster?


Regards,

 

[molinamike]

Rick,

Thank you very much the code...yes, it made massive improvements with the speed. Your second response also provided some clarity with the use of "ary'. Awesome stuff!!! thanks big time.

 

[RickXL]

No problem. I am just pleased to be of assistance.

I am amazed how well-hidden this way to use VBA is. I knew nothing about it until I came to MrExcel but it is often the quickest way to do things. The times seems to be dependent on how often VBA needs to communicate with the Worksheet. So just copying the data to VBA once and writing it back once is a huge time saver.


Regards,