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.
Resizing Filtered Data
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Resizing Filtered Data
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78476
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Resizing Filtered Data
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:
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
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Resizing Filtered Data
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.
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.
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78476
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Resizing Filtered Data
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"
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California