0

What I'm trying to do is I want to copy the rows to each different type based on record type.

Raw file tab - there are hundreds of thousands rows of data containing 6 different record types.
PRPO TAB - so I want PROP type to be copied to PROP tab

PPPP TAB - so I want PPPP type to be copied to PROP tab

ABCD TAB - so I want ABCD type to be copied to PROP tab

I have 6 similar module1~module6 as below, and module7 combines all 6 of them. but the problem is it took me about 50 mins to run module7 for about 150,000 rows.

Is there anyway to optimize the speed?

Below is my VBA codes:


Sub PROP()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

'Taking the Inputs
    
Dim Sheet3 As String, Sheet6 As String, Range3 As String, Range6 As String, Range0 As String, Sheet0 As String, CriteriaColumn As Integer
Dim LastRow As Long
    
Sheet3 = "Raw Data"
sCol = "B"
Sheet6 = "PROP"
Range6 = "A9"
Range0 = "A2"
CriteriaColumn = 1
     
'Forming the Necessary Ranges
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
    
With Sheets(Sheet3)
    LastRow = .Cells(.Rows.Count, sCol).End(xlUp).Row
    If LastRow > 1 Then
        Set Rng1 = .Range("B2", sCol & LastRow)
        Set Rng3 = .Range("A2", sCol & LastRow)
        Debug.Print Rng1.Address
    End If
End With
With Sheets(Sheet6)
    LastRow2 = .Cells(.Rows.Count, sCol).End(xlUp).Row
    If LastRow2 > 1 Then
        Set Rng2 = .Range("A9", Range6 & LastRow2)
        Debug.Print Rng1.Address
        Rng2.Clear
    End If
End With
'Copying the Headers
'Rng1.Rows(1).Copy
'Rng2.Cells(0, 1).PasteSpecial Paste:=xlPasteAll
 
'Copying the Dataset with Criteria
Count = 0
   
For i = 1 To Rng1.Rows.Count
    If Rng3.Cells(i, CriteriaColumn) = "PROP" Then
        Count = Count + 1
        Rng1.Rows(i).Copy
        Rng2.Cells(Count, 1).PasteSpecial Paste:=xlPasteAll
    End If
           
Next i

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Improve this question
5
  • 3
    I'm not going to rewrite that for you. But you could add some breakpoints to see where all the time is being spent, then look at optimising that bit.
    – spikey_richie
    Commented Apr 10 at 15:19
  • 2
    Maybe sort on the record type then look for the first and last instance of a record type and do the whole range at once?
    – cybernetic.nomad
    Commented Apr 10 at 15:24
  • 1
    And it may be the code is not at fault -- VBA is interpreted, and plowing through 150,000 rows would take considerable time. If 55 minutes is too long, perhaps a faster machine is needed, or another language.
    – DrMoishe Pippik
    Commented Apr 10 at 16:07
  • 1
    The only possible optimization is break the serial calculation of 150,000 rows into multiple parts. I don't believe VBA supports multiple threads so even if you do that it run each part serially. There really isn't any optimization that can be done, but a question like this, really should be on SO as it's a programming question.
    – Ramhound
    Commented Apr 10 at 16:30
  • so your looping through each row? Seems like you could do a filter for a group copy/paste instead.
    – gns100
    Commented Apr 10 at 17:53

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .