VBA filter is un hiding filtered results page info.

[realred2]

I have a multi-step process with a workbook, that I have hidden rows on. When I get to a second page, I pull some of the info on the first page and it populates a range with all the data I need, with out extra lines. It does this with VBA code that uses "Filter". When I run this VBA code, it makes all the hidden rows on page 1 show again. I've put the code I'm using below, is there a way to do this without messing up the original page formatting/hidden rows?

 Sub GetRTFInfo()
    Worksheets("CS INFO SHEET").Range("B2:E1300").ClearContents
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rngToCopy As Range
    Dim lastRow As Long
   'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("ADDRESS MATRIX")
    Set ws2 = ThisWorkbook.Worksheets("CS INFO SHEET")
    With ws1
       'assumung that your data stored in column A:B, Sheet1
        lastRow = .Cells(.Rows.Count, "AF").End(xlUp).Row
        Set rng = .Range("AF13:AI" & lastRow)
      'clear all filters
        .AutoFilterMode = False
        With rng
           'apply filter
            .AutoFilter Field:=4, Criteria1:="x"
            On Error Resume Next
           'get only visible rows
            Set rngToCopy = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
       'copy range
        If Not rngToCopy Is Nothing Then rngToCopy.Copy
                    ws2.Range("B2").PasteSpecial Paste:=xlPasteValues
       'clear all filters
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
    Worksheets("CS INFO SHEET").Range("E2:E1300").ClearContents
 End Sub

Thanks!

 

[Dan_W]

Does it work if you changed the following line in the code by adding .Copy to:

Set rngToCopy = .SpecialCells(xlCellTypeVisible).Copy

and then deleting this line:

If Not rngToCopy Is Nothing Then rngToCopy.Copy

 

[Fluff]

How about

 Sub GetRTFInfo()
    Worksheets("CS INFO SHEET").Range("B2:E1300").ClearContents
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim Rws As Variant, Ary As Variant
   'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("ADDRESS MATRIX")
    Set ws2 = ThisWorkbook.Worksheets("CS INFO SHEET")
    With ws1
       'assumung that your data stored in column A:B, Sheet1
        lastRow = .Cells(.Rows.Count, "AF").End(xlUp).Row
        Set rng = .Range("AF13:AI" & lastRow)
        Rws = Filter(Evaluate(Replace("transpose(if(@=""x"",row(@)-min(row(@))+1,false))", "@", rng.Columns(4).Address)), False, False)
        If UBound(Rws) >= 0 Then
            Ary = Application.Index(rng.Value2, Application.Transpose(Rws), Array(1, 2, 3))
            ws2.Range("B2").Resize(UBound(Ary), 3).Value = Ary
        End If
 End Sub