Excel VBA Macro for Copying Filtered Rows optimization guide as Expected in Office 365
I'm performance testing and I've searched everywhere and can't find a clear answer. I'm attempting to create a VBA macro that copies filtered rows from one sheet to another in Excel (Office 365). My goal is to copy only the visible cells after applying a filter, but for some reason, the macro is copying all rows, including hidden ones. Here's the code I've written so far: ```vba Sub CopyFilteredRows() Dim SourceSheet As Worksheet Dim TargetSheet As Worksheet Dim LastRow As Long Dim FilteredRange As Range Set SourceSheet = ThisWorkbook.Sheets("Source") Set TargetSheet = ThisWorkbook.Sheets("Target") ' Determine the last row of the source sheet LastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "A").End(xlUp).Row ' Set the range to the used cells Set FilteredRange = SourceSheet.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible) ' Copy the filtered cells to the target sheet FilteredRange.Copy TargetSheet.Range("A1") End Sub ``` When I run this macro, it successfully identifies the visible cells according to the filter applied on the "Source" sheet, yet it still copies some hidden rows. I've ensured that the filter is applied correctly before executing the macro. I also tried changing the definition of `FilteredRange` to: ```vba Set FilteredRange = SourceSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible) ``` However, this only throws an behavior if there are no visible cells, and I still encounter the scenario of hidden rows being copied. The macro seems to be ignoring the filter state. I have checked to see if the AutoFilter is applied manually, and it is, but the performance is not as expected. Any ideas on how to ensure that only the filtered visible rows are copied? I'm running Excel for Microsoft 365 (Version 16.0.13901.20286) and I'm looking for a solution that addresses this without changing the structure of my workbook. This is part of a larger API I'm building. Is there a better approach? Hoping someone can shed some light on this.