How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

12:28 AM Unknown 0 Comments

You might be working on a project where you need to filter sets of data and create a raw data of that filtered sets of data to a new sheet or range.

By default, Excel copies hidden or filtered cells in addition to visible cells. If some cells, rows, or columns on your worksheet are not displayed, you have the option of copying all cells or only the visible cells.

The following snippet allows you to automate the process in microseconds.

[VBA]

Public Function GetFilteredData()
    Dim rawWs As Worksheet            'RAW DATA WORKSHEET
    Dim tarWs As Worksheet            'TARGET WORKSHEET
    
    'Replace this with your actual Worksheets
    Set rawWs = Sheets("Raw Data")    
    Set tarWs = Sheets("Filtered Data Visualizations")
    
    Application.ScreenUpdating = False

    'Clear old contents of the Target Worksheet
    tarWs.Range("A2:N" & Rows.Count).ClearContents
    
    '****************************************************
    ' Select Raw Data Sheet and
    ' Copy only the visible rows if filter is applied
    '
    rawWs.Select
    Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, Range("N2").Column)).SpecialCells(xlCellTypeVisible).Copy
    
    '****************************************************
    'Select the Target worksheet and
    'Paste the copied data
    '
    tarWs.Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Range("A2").Select
    Application.ScreenUpdating = True
End Function