Trying to create a dynamic named range

[gshock]

I'm trying to create a named range that can be resized based on the number of rows, but I'm getting an invalid use of property error, and I don't understand why. I'm trying to follow the VBA code in this post, and I don't know what I'm doing wrong.

Dynamic range in VBA with Excel functions

Hi, I'm trying to set the sourceRange as a dynamic range in VBA that starts from cell AC22 and offset with the height of COUNTA(data_date_range)+1, where data_date_range is a named range. and width of Columns(InputClaims), where InputClaims is also a named range. The following formula works...

www.mrexcel.com

 

[John_w]

You can't have Range("Location").Rows.Count on a line like that because it doesn't mean anything. It's just a number which you could display (MsgBox), use with another object or method.

For example, in the linked thread, Range("InputClaims").Columns.Count is used as the 2nd argument to the Resize method - the number of columns to resize Range("AC22") by.

 

[gshock]

John_w, thanks for your help. Sorry for the late reply.

When

Range("AC22")

is used in this way, it serves as a starting point? Correct? Is he determining how many columns to resize by using this:

Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1

 

[John_w]

Posting the complete statement from the linked thread for clarity:

Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1, _
                                Range("InputClaims").Columns.Count

is used in this way, it serves as a starting point?

Yes, Range("AC22") is the starting point for resizing the range, in this case a single cell.

Is he determining how many columns to resize by using this:

No, that part specifies the number of rows to resize by. Resize takes 2 arguments: the number of rows to resize by and the number of columns to resize by. See Range.Resize property (Excel)