Dynamic Range with VBA

Maslan · Mar 18th, 2002, 02:22 AM

Hi,
I used this code to try and define a dynamic range. Got it by recording macro. While it is defined in the Insert->Define->Name menu, references to Range("DATABASE") will cause error "Reference is invalid" -- Help??

xlBook.Names.Add Name:="DATABASE", RefersToR1C1:= _
"=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),6)"

Dave Hawley · Mar 18th, 2002, 03:31 AM

Hi

2 Things

1. You dynamic range will NOT be dynamic if you just use: "COUNTA(Sheet1!C1)" This will return either 1 or 0

2. You have "xlBook" I assume this is a Workbook Object variable?? Try using "ActiveWorkbook"

I have many eaxmples of Dynamic ranges here:

http://www.ozgrid.com/Excel/DynamicRanges.htm

nisht · Mar 18th, 2002, 03:49 AM

There are different way to define a dynamic ranges in VBA.. Each way has its own limitations and advantage. It is very difficult to make someone understand on Net.

If you want to know how to define dynamic ranges goto my website and download file 16.

Name of the file is "Populating Combobox."

It demonstrates how you can make the range dynamic.

I hope you must have some knowledge in VBA.

I hope you will like to file.

http://www.pexcel.com/download.htm
nishith desai

Maslan · Mar 18th, 2002, 03:50 AM

Cheers guys.. Got it at last... Me = silly
Thanks for the files..

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 02:22 AM	#1
Maslan New Member Join Date: Mar 2002 Posts: 11	Hi, I used this code to try and define a dynamic range. Got it by recording macro. While it is defined in the Insert->Define->Name menu, references to Range("DATABASE") will cause error "Reference is invalid" -- Help?? xlBook.Names.Add Name:="DATABASE", RefersToR1C1:= _ "=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),6)"

Mar 18th, 2002, 03:31 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi 2 Things 1. You dynamic range will NOT be dynamic if you just use: "COUNTA(Sheet1!C1)" This will return either 1 or 0 2. You have "xlBook" I assume this is a Workbook Object variable?? Try using "ActiveWorkbook" I have many eaxmples of Dynamic ranges here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Mar 18th, 2002, 03:49 AM	#3
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	There are different way to define a dynamic ranges in VBA.. Each way has its own limitations and advantage. It is very difficult to make someone understand on Net. If you want to know how to define dynamic ranges goto my website and download file 16. Name of the file is "Populating Combobox." It demonstrates how you can make the range dynamic. I hope you must have some knowledge in VBA. I hope you will like to file. http://www.pexcel.com/download.htm nishith desai

Mar 18th, 2002, 03:50 AM	#4
Maslan New Member Join Date: Mar 2002 Posts: 11	Cheers guys.. Got it at last... Me = silly Thanks for the files..