Digita
06-01-2010, 04:59 PM
Guys,
Got a question and hope you can help. The code below assigns all visible (filtered) cells to a range object and writes it to a new worksheet.
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
Worksheets.Add
ActiveSheet.Range("A1").Resize(rng.Rows.Count,rng.Columns.Count) = rng.Value
However, this code doesn't work properly. It did not pick up the filtered rows. Dropping the .SpecialCells(xlCellTypeVisible) part, the code picks up everything including the hidden rows.
Without using .copy command, what is the workaround to capture visible rows only? I'm running this code in XL 2k.
Thanks in advance.
Regards
kp
Got a question and hope you can help. The code below assigns all visible (filtered) cells to a range object and writes it to a new worksheet.
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
Worksheets.Add
ActiveSheet.Range("A1").Resize(rng.Rows.Count,rng.Columns.Count) = rng.Value
However, this code doesn't work properly. It did not pick up the filtered rows. Dropping the .SpecialCells(xlCellTypeVisible) part, the code picks up everything including the hidden rows.
Without using .copy command, what is the workaround to capture visible rows only? I'm running this code in XL 2k.
Thanks in advance.
Regards
kp