Solved: SpecialCells(xlCellTypeVisible) does not seem to work in range object

[Digita]

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

[mbarron]

Try this:
Sub rngAreas()
Dim rng As Range
Dim i As Integer
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

Worksheets.Add

For i = 1 To rng.Areas.Count
ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(1).Resize(rng.Areas(i).Rows.Count, rng.Areas(i).Columns.Count) _
= rng.Areas(i).Value
Next

End Sub

[Digita]

Hi Mbarron,

Your code works the trick. Thank you so much.

Regards


kp

[Digita]

Hi again,

Just found out the code by Mbarron picks up hidden rows as well. I need to grab the filtered rows only. Any further ideas? Thanks again.

Regards

kp

[Digita]

Sorry. Mbarron's code works perfectly well. A colleague of mine is playing tricks on me. A minor modification to Mbarron's version:



Sub rngAreas()
Dim rng As Range
Dim i As Integer
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

Worksheets.Add

For i = 1 To rng.Areas.Count
ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2).Resize(rng.Areas(i).Rows.Count, rng.Areas(i).Columns.Count) _
= rng.Areas(i).Value
Next

End Sub


Thread is marked solved.

Thanks again.