Resizing Filtered Data

[jstevens]

I am encountering a VBA error: Application-defined or Object defined error. It occurs when the row after the field headings is hidden.

Your thoughts are appreciated.

el_resize.xlsm

[HansV]

It's not just when you hide the row after the field headings.
In most situations, tbl will not be a contiguous range, but consist of multiple areas.
Resizing a non-contiguous range will cause an error.

You can resize first, then filter:

Code: Select all

Sub Resize_Based_On_Q1()
    Dim tbl As Range
    Dim Address As String

    With Sheets("Sheet1")
        If .AutoFilterMode = False Then
            .Range("A2").AutoFilter
        End If
        .Range("A2").AutoFilter Field:=5, Criteria1:="<>6", Operator:=xlAnd
        Set tbl = .Range("A2").CurrentRegion
        Set tbl = tbl.Offset(2).Resize(tbl.Rows.Count - 2).SpecialCells(xlCellTypeVisible)
        Address = tbl.Address
    End With
    MsgBox Address
End Sub

[jstevens]

Hans,

Thanks for the reply.

What is the significance of setting Criteria!:="<>6"? My original code was only picking up Q1 values <>0. Therefore Rows 4,5,7,9 were visible.

EL_107.png

[HansV]

Sorry about that, that was an experiment to see if it depended on hiding the row below the headers. Change it back to Criteria1:="<>0"

[jstevens]

Hans,

Thanks for the clarification.