Help to improve performance of code VBA

[Lenna]

My code (below) executes but it gives me a message that there is too much data on the clipboard, even when I have only few rows. User has to click "OK" to move on... it's very annoying. Please suggest how to improve this code in other aspects as well.

It opens a .CSV file (user choice) filters it and copies filtered data to .xlsm file.

[INDENT]Private Sub CommandButton1_Click()



Dim csvFile As Variant
Dim csvBook As Workbook


csvFile = Application.GetOpenFilename("Text Files (*.csv),(*.csv),,Please select CSV file to open")
If (csvFile <> False) Then
Workbooks.Open csvFile
Set csvBook = ActiveWorkbook
End If
'Trim column O
Columns("O:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
    Range("O1").Select
    Selection.Copy
    Range("A1").Select
    Selection.End(xlDown).Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("O" & FinalRow).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select


'filter data
Cells.Select
    Selection.AutoFilter
    
        FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
'ActiveSheet.Range("$A$1:$O$" & FinalRow).AutoFilter Field:=13, Criteria1:=Array( _


    
    ActiveSheet.Range("$A$1:$Q$" & FinalRow).AutoFilter field:=10, Criteria1:=Array( _
        "AT1R", "C1Q_I", "C1Q_I_RPT", "C1Q_II", "C1Q_II_RP", _
        "FL__PRAI", "FL__PRAI_RPT", "FL__PRAII", "FL__PRAII_RPT", "FL_EPC_XM_ALLO", _
        "FL_XM_ALLO", "FL_XM_ALLO_RPT", "FL_XM_AUTO", "GP__I", "GP__IDv2", "GP__II", _
        "GP__MICA", "GP_MICARPT", "IBEAD_SABI", "LCTI", "LPRI", "LPRI_RPT", "LPRII", _
        "LPRII_RPT", "LSM__MICA", "LSMI", "LSMI_RPT", "LSMII", "LSMII_RPT", "LSMMICA_RPT", _
        "MICA_SAB", "SABI", "SABI_Dilution", "SABI_IgM", "SABI_RPT", "SABII", _
        "SABII_Dilution", "SABII_IgM", "SABII_RPT", "Serum_Stored"), Operator:= _
        xlFilterValues
   
Columns("A:Q").Select
Selection.Copy
Windows("SerologyQAMacro.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
csvBook.Close SaveChanges:=False ' or True


End Sub[/INDENT]

 

[JoeMo]

Haven't looked closely at your code, but it might help if you don't copy entire columns to the clipboard.

try:
Intersect(Columns("O"), activesheet.UsedRange).Copy

 

[Peter Thompson]

With regards to other things that could be done to improve the code you could alter the code so that you don't select a range before carrying out an action on it. So for example when you put the trim formula you could do it as follows

dim lngLastrow as long
LngLastrow =cells(rows.count,1).end(xlup).row
Range("O1:O" & lngLastrow).formular1c1="trim(rc[1])"

 

[Lenna]

I implemented your suggestions but still got the same annoying message: "Large amount of information on clipboard. Do you want to be able to paste this information later?''

Help!

 

[Lenna]

Peter,

I've tried your code and it places trim(rc[1]) in O1.

What am I missing?

 

[Michael M]

you could also change this section...
As mentioned try to avoid Selecting, by removing the references to Select.Selection

Columns("A:Q").Select
Selection.Copy

TO

Range("A1:Q" & Finalrow).Copy

 

[Smitty]

You should be able to suppress the message with Application.DisplayAlerts = False

And here's a link to clear the clipboard: Windows Clipboard

 

[Lenna]

I inserted this code into a button on the user form to clear clipboard and I crashed Excel.

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

You should be able to suppress the message with Application.DisplayAlerts = False

And here's a link to clear the clipboard: Windows Clipboard

 

[Smitty]

That's odd...I've never had a problem with it.

Did you get any error messages prior to the crash?

 

[GTO]

I inserted this code into a button on the user form to clear clipboard and I crashed Excel.

What does that mean exactly? Could you show us the button's code and where did you put the declared functions (the API)?

Mark