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

<div class="separator" style="clear: both; text-align: center;"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" width="320" /></a></div> <div style="text-align: justify;"> 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.<br /> <br /> 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. </div> <br /> The following snippet allows you to automate the process in microseconds.<br /> <br /> <b><span style="color: #cc0000; font-size: large;">[</span><span style="background-color: #cc0000;"><span style="color: #f4cccc;">VBA</span></span><span style="color: #cc0000; font-size: large;">] </span></b><br /> <pre class="brush: vb">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</pre> <br /> <div style="text-align: center;"> <b>Output on a new sheet shown below.</b></div> <div style="text-align: center;"> <b><br /></b></div> <div class="separator" style="clear: both; text-align: center;"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" width="320" /></a></div> <br />

excel microsoft excel VBA 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,…

Read more »

How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.<br /><br /><span style="color: #cc0000; font-size: large;"><b>[</b></span><span style="background-color: #cc0000;"><span style="color: #f4cccc;"><b>VBA</b></span></span><b><span style="color: #cc0000; font-size: large;">]</span></b><br /><pre class="brush: vb">Dim FilteredRows as Variant<br /><br />Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean)<br /><br /> Dim Rng As Range, rngF As Range, rngVal As Range 'Ranges<br /> Dim val As Variant 'Range Value<br /> Dim i As Integer 'Counter<br /> Dim lRow as long 'Last Row<br /><br /> Application.ScreenUpdating = False<br /><br /> Sheets("Raw Data").Select<br /> lRow = WorksheetFunction.CountA(Range("A:A"))<br /><br /> 'Set the range of all visible cells of the filtered data<br /> Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _<br /> Range("A2").Column)).SpecialCells(xlCellTypeVisible)<br /> <br /> For Each Rng In Range("$A2:$A$" & lRow)<br /> If Not Intersect(Rng, rngF) Is Nothing Then<br /> If rngVal Is Nothing Then<br /> Set rngVal = Rng<br /> Else<br /> Set rngVal = Union(rngVal, Rng)<br /> End If<br /> If rngVal.Cells.Count = lRow Then Exit For<br /> End If<br /> Next Rng<br /> <br /> 'Resize array variable<br /> ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant<br /> <br /> For Each val In rngVal<br /> If RowPrefixed = True Then<br /> FilteredRows(i) = val.Address<br /> Else<br /> FilteredRows(i) = Split(val.Address, "$")(2)<br /> End If<br /><br /> Debug.Print val.Address & " - " & Split(val.Address, "$")(2)<br /> i = i + 1<br /> Next val<br /> <br /> Debug.Print rngVal.Address<br /> <br /> Applicaiton.ScreenUpdating = True<br />End Function<br /></pre><br />To use the above function, you can assigned the following macro to a button or shape. <br /><pre class="brush: vb">Sub SetFilter()<br /> Call GetFilteredRows(True)<br />End Sub<br /></pre><br />And you can see the output in the Immediate window as shown below.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" width="400" /></a></div><br />

excel microsoft excel VBA How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.[VBA]Dim FilteredRows as VariantPublic Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean) Dim Rng As Range, rngF…

Read more »