Copy Paste Calculate Visible Cells from One Column of a Filtered Table

Question

I am using AutoFilter to sort through a table in VBA  which results in a smaller table of data  I only want to copy paste the visible cells of one column after the filter is applied  Also  I would like to average the filtered values of one column and put the result in a different cell   I ve found this snippet on Stack which allows me to copy paste the entire visible results of the filter  but I don t know how to modify it or another way to get only one column s worth of data  without the header  from it   Range  A1  Cells 65536 Cells 1 256  End xlToLeft  Column  End xlUp   SpecialCells xlCellTypeVisible  Copy Sheets  Sheet2   Range  A1   PasteSpecial xlPasteValuesAndNumberFormats Application CutCopyMode   False   Addition to answer  to calculate with the filtered values    tgt Range  B2   Value  WorksheetFunction Average copyRange SpecialCells xlCellTypeVisible

User · Accepted Answer

I set up a simple 3-column range on Sheet1 with Country, City, and Language in columns A, B, and C. The following code autofilters the range and then pastes only one of the columns of autofiltered data to another sheet. You should be able to modify this for your purposes:

Sub CopyPartOfFilteredRange()
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long

    Set src = ThisWorkbook.Sheets("Sheet1")
    Set tgt = ThisWorkbook.Sheets("Sheet2")

    ' turn off any autofilters that are already set
    src.AutoFilterMode = False

    ' find the last row with data in column A
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row

    ' the range that we are auto-filtering (all columns)
    Set filterRange = src.Range("A1:C" & lastRow)

    ' the range we want to copy (only columns we want to copy)
    ' in this case we are copying country from column A
    ' we set the range to start in row 2 to prevent copying the header
    Set copyRange = src.Range("A2:A" & lastRow)

    ' filter range based on column B
    filterRange.AutoFilter field:=2, Criteria1:="Rio de Janeiro"

    ' copy the visible cells to our target range
    ' note that you can easily find the last populated row on this sheet
    ' if you don't want to over-write your previous results
    copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A1")

End Sub

Note that by using the syntax above to copy and paste, nothing is selected or activated (which you should always avoid in Excel VBA) and the clipboard is not used. As a result, Application.CutCopyMode = False is not necessary.

User · Answer

Here a code that works with windows office 2010  This script will ask you for input filtered range of cells and then the paste range    Please  both ranges should have the same number of cells   Sub Copy Filtered Cells    Dim from As Variant Dim too As Variant Dim thing As Variant Dim cell As Range   Selection SpecialCells xlCellTypeVisible  Select       Set from   Selection SpecialCells xlCellTypeVisible      Set temp   Application InputBox  Copy Range     Type  8      Set from   temp SpecialCells xlCellTypeVisible      Set too   Application InputBox  Select Paste range selected cells   Visible cells only    Type  8         For Each cell In from         cell Copy         For Each thing In too             If thing EntireRow RowHeight  gt  0 Then                 thing PasteSpecial                 Set too   thing Offset 1  Resize too Rows Count                  Exit For             End If         Next     Next   End Sub   Enjoy

User · Answer

I ve found this to work very well  It uses the  range property of the  autofilter object  which seems to be a rather obscure  but very handy  feature   Sub copyfiltered         Copies the visible columns       and the selected rows in an autofilter             Assumes that the filter was previously applied           Dim wsIn As Worksheet     Dim wsOut As Worksheet      Set wsIn   Worksheets  Sheet1       Set wsOut   Worksheets  Sheet2          Hide the columns you don t want to copy     wsIn Range  B B D D   EntireColumn Hidden   True       Copy the filtered rows from wsIn and and paste in wsOut     wsIn AutoFilter Range Copy Destination  wsOut Range  A1   End Sub

User · Answer

Just to add to Jon s coding if you needed to take it a step further  and do more than just one column you can add something like   Dim copyRange2 As Range Dim copyRange3 As Range  Set copyRange2  src Range  B2 B   amp  lastRow  Set copyRange3  src Range  C2 C   amp  lastRow   copyRange2 SpecialCells xlCellTypeVisible  Copy tgt Range  B12   copyRange3 SpecialCells xlCellTypeVisible  Copy tgt Range  C12     put these near the other codings that are the same you can easily change the Ranges as you need    I only add this because it was helpful for me  I d assume Jon already knows this but for those that are less experienced sometimes it s helpful to see how to change add modify these codings  I figured since Ruya didn t know how to manipulate the original coding it could be helpful if one ever needed to copy over only 2 visibile columns  or only 3  etc  You can use this same coding  add in extra lines that are almost the same and then the coding is copying over whatever you need    I don t have enough reputation to reply to Jon s comment directly so I have to post as a new comment  sorry

[excel] Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table

Examples related to excel

Examples related to filter

Examples related to copy-paste

Examples related to vba