Run-time Error '1004
 

Please help. I get an error "run-time '1004 Application-defined or
orject-defined error" on the line of code "Set CP_range =
CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)" The error occurs when I
have a workbook with only the header row and no records below row one. The
function code is below

' Function that copies rows where Category equals Priority
Function CP_datarange(CP_bk As Workbook) As Range
Set CP_Sh = CP_bk.Worksheets(1)
CP_Sh.UsedRange.AutoFilter field:=6, Criteria1:=Trim("Priority")
Set CP_range = CP_Sh.AutoFilter.Range
Set CP_range = CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)
Set CP_datarange = CP_range
End Function

Request assistance

Run-time Error '1004
 

If CP_range.Rows.Count = 1 then the statement
CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)
will not return a range object and teh set statement will fail. Consider
doing a check prior to the set statement to ensure that CP_range.Rows.Count
is greater than 1...
That is my best guess as to what is going on.
--
HTH...

Jim Thomlinson


"Bill" wrote:

Please help. I get an error "run-time '1004 Application-defined or
orject-defined error" on the line of code "Set CP_range =
CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)" The error occurs when I
have a workbook with only the header row and no records below row one. The
function code is below

' Function that copies rows where Category equals Priority
Function CP_datarange(CP_bk As Workbook) As Range
Set CP_Sh = CP_bk.Worksheets(1)
CP_Sh.UsedRange.AutoFilter field:=6, Criteria1:=Trim("Priority")
Set CP_range = CP_Sh.AutoFilter.Range
Set CP_range = CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)
Set CP_datarange = CP_range
End Function

Request assistance