We help IT Professionals succeed at work.
Troubleshooting Question

copy & paste from autofiltered list

30 Views
Last Modified: 2020-11-03
I have 2 columns in an excel spreadsheet, A and B. Currently column B is blank, A is populated with several thousand records. I have applied an auto-filter on Column A to limit the amount of data I can see, based on certain criteria. What I want to do is copy and paste the filtered list of data into its adjacent cell in column B. However, standard copy and paste does not seem to work this way, and pastes the data in a sequence of cells rather than 'level' with the filtered data from column A.

Is there a way to paste the data 'in line' with the filtered cells from Column A? I couldn't see anything obvious in the paste special options. I think you used to be able to move the data but I dont really want to do that either, a true copy and paste is required. 
Comment
Watch Question

Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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 :)
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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...

HainKurtSr. System Analyst
CERTIFIED EXPERT

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


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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...
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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!
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
pma111, have you gotten the answer that you needed, or do you need more help?  If you have what you need then please close this question.