How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA
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.
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
Output on a new sheet shown below.
Comments
Post a Comment