copy &amp;amp; paste from autofiltered list

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

If you have a large list being auto filtered, it would go faster if you copy each visible area rather than loop through the cells one at a time.

I made a test with 10K rows, and result is a fraction of a second...

and with 100K rows, it is taking around 2-3 second...
but we need Long instead of integer in the code...

Sub copyVisible()
    Dim r As Long
    r = 2
    Do While Cells(r, 1).Value <> ""
      If Not Rows(r).Hidden Then Cells(r, 2).Value = Cells(r, 1).Value
      r = r + 1
    Loop
End Sub

for 1M rows, 428K selected rows, it takes 25 seconds...

if you have millions of records, yes i agree, we need to find another approach :)

The addition of lines 4 and 12 will speed it up.

Sub copyVisible()
    Dim r As Long

    Application.ScreenUpdating = False

    r = 2
    Do While Cells(r, 1).Value <> ""
      If Not Rows(r).Hidden Then Cells(r, 2).Value = Cells(r, 1).Value
      r = r + 1
    Loop

    Application.ScreenUpdating = True

End Sub

The addition of lines 4 and 12 will speed it up

I made a test with 1M rows... 428K rows selected...
no difference between original and the one with

Application.ScreenUpdating = False
...
Application.ScreenUpdating = True

both is around 23 seconds...

I wanted to test the performance of other solution, posted by byundt
but I got error...

and tried this, with 1M rows, 428K selected

Sub copyVisible3()
    Application.ScreenUpdating = False
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    For Each c In Selection
      Cells(c.Row, 4).Value = c.Value
    Next

    Application.ScreenUpdating = True
End Sub

27 seconds... not better than the original one...

Using the techniques described in this article of mine I ran the Test sub three times against a 125 row sample, first with lines 7 and 16 comment out and then with those lines uncommented. These are the results:
Elapsed time: 15516ms
Elapsed time: 15531ms
Elapsed time: 15594ms

Elapsed time: 4594ms
Elapsed time: 4625ms
Elapsed time: 4781ms

Sub test()
Dim x As Long
Dim lngStart As Long
Dim lngFinish As Long

lngStart = GetTickCount()
Application.ScreenUpdating = False

 For x = 1 To 2000
    copyVisible
 Next
 
 lngFinish = GetTickCount()

Debug.Print "Elapsed time: " & CStr(lngFinish - lngStart) & "ms"
Application.ScreenUpdating = True

End Sub
Sub copyVisible()
    Dim r As Long

    r = 2
    Do While Cells(r, 1).Value <> ""
      If Not Rows(r).Hidden Then Cells(r, 2).Value = Cells(r, 1).Value
      r = r + 1
    Loop

End Sub

These are the results:

we are not running a tiny code 2000 times and refreshing the screen 2000 times...
we are running it only once...

here is the results with 1 pass

Sub test()
Dim x As Long
Dim lngStart As Long
Dim lngFinish As Long

lngStart = GetTickCount()
Application.ScreenUpdating = False

copyVisible1
 
lngFinish = GetTickCount()

Application.ScreenUpdating = True
Debug.Print "Elapsed time: " & CStr(lngFinish - lngStart) & "ms"

End Sub

with 1 M
Elapsed time: 17578ms
Elapsed time: 18078ms

with 100K
Elapsed time: 1766ms
Elapsed time: 1860ms

with 10K
Elapsed time: 375ms
Elapsed time: 375ms

with 1K
Elapsed time: 47ms
Elapsed time: 47ms

Summary:
up to 100K rows, there is no significant difference
around 1M, the difference is 0.5 second

we are not running a tiny code 2000 times and refreshing the screen 2000 times...
we are running it only once...

Obviously. Running through my 125-row sample 2000 times is nearly equivalent doing a 250,000-row sample one time.

Obviously. Running through my 125-row sample 2000 times is nearly equivalent doing a 250,000-row sample one time.

running 5 km marathon is not same as running 50 times 100 m short run :)

I gave you the results above!