I have code that filters for a date range, then needs to run a copy/paste if there is visible data after filtering.
The trouble is, my code in bold is bypassing the copy/paste, even if there's filtered results to work with.
At the moment I have headers in rows 1 and 2 and the filtered results show rows 37, 44 and 50. I presume my current code is checking row 3, finding it hidden, so bypassing the copy paste, whereas I'd like it to check the next visible row under row 2. Should I be using some kind of .Rows.Hidden and xlCellTypeVisible combination to make the copy/paste occur when there's filtered results?
Thanks.
Rich (BB code):
ActiveSheet.Range("A3:Y1000000").AutoFilter field:=8, Criteria1:=">=" & Format(StartDate, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(EndDate, "mm/dd/yyyy")
With ActiveSheet.Range("H2").Offset(1, 0)
If Not (.Rows.Hidden) Then
Range("H3:H" & Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets("BR").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets("BR").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
Range("I3:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets("BR").Range("D" & Rows.Count).End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteAll
End If
End With
The trouble is, my code in bold is bypassing the copy/paste, even if there's filtered results to work with.
At the moment I have headers in rows 1 and 2 and the filtered results show rows 37, 44 and 50. I presume my current code is checking row 3, finding it hidden, so bypassing the copy paste, whereas I'd like it to check the next visible row under row 2. Should I be using some kind of .Rows.Hidden and xlCellTypeVisible combination to make the copy/paste occur when there's filtered results?
Thanks.