More elegant solution to a dynamic range problem.

gilligan73

New Member
Joined
Jul 16, 2014
Messages
8
Hi experts. I thought I had it all worked out by creating a dynamic range using OFFSET and COUNTA, and it worked perefctly. But, the macro that uses these dynamic named ranges has several steps which causes a run-time error. I believe that this is due to the reference to the columns being deleted in the first step.

Say I have columns 1 - 10. All rows contain data, numbers or text. I then delete, say columns 2, 4, 6 and 8. I then add in some columns, to get information from a vlookup, for example. But columns 2 and 4 are in my dynamic named range AFTER I have deleted and added columns...So my dynamic ranges, which were correct, now have #REF in the formula. Not good. I have a bit of sorry workaround but was thinking to create dynamic named ranges that I need in VBA after I have deleted and added the rows. If this sounds reasonable, any good links to how to best to this? Other ideas?

Thanks in advance!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You can create named ranges in VBA, as an example:

Code:
ActiveWorkbook.Names.Add Name:="gilligan73", RefersTo:="=Sheet1!$G$73"
Hope this helps,

Chris.
 
Upvote 0
Thank you Chris...you are getting me on the right track....The solution I have works but it is not "human friendly" if they need to look at the raw data....My worksheet is named "TheData", so I will make a named range "TheData3", for example...writing the dynamic named range is fine in Excel, but throws a syntax error when in VB....the refers to works fine in Excel named range...but I just am missing some little thing...sorry, but I am more SQL and VB.NET then I am VBA and helping out another department...here is the "offensive" code...I want to populate a working named range (dynamic) with a VLOOKUP:

ActiveWorkbook.Names.Add Name:="TheData3"; RefersTo:="=OFFSET(TheData!$c$2;0;0;COUNTA(TheData!$b:$b);1)"

Worksheets("TheData").Range("TheData3").FormulaLocal = "=VLOOKUP(A2;BALookup!$A$2:$C$25;3;FALSE)"

Thanks for any advice...I think I am close to a better solution than I have....tussen takk! Dave
 
Upvote 0
I think it is an ID10T error on my part....it just gives me a syntax error and the line is the red error indication....however:

ActiveWorkbook.Names.Add Name:="TheData3", RefersTo:="=OFFSET(TheData!$c$2;0;0;COUNTA(TheData!$b:$b);1)"

With a comma instead of semicolon does not show in red but still an error. With semicolon, I get "expected end of statement" but with comma, just an syntax error. (Using European-Norway Syantax so I often have to replace "," with ";" but that didn't work...also explicitly chose the worksheet, but I was running it on the necessary worksheet anyway (Sheets("TheData").Select)

Thanks,
Dave
 
Upvote 0
I really can't think what might be wrong. I would try:

Code:
ActiveWorkbook.Names.Add Name:="TheData3", RefersTo:="=OFFSET(TheData!$C$2,0,0,COUNTA(TheData!$B:$B),1)"
Other than that I have no idea. I constructed an example with the same syntax and it's working fine for me.

What version of Excel are you using?

Chris.
 
Upvote 0
In VBA it's simpler than in Excel: use usedrange, currentregion or area
Named ranges are a 'solution' in Excel to imitate these VBA's dynamic properties

Code:
msgbox cells(1,3).currentregion.address
 
Upvote 0
Thanks Chris and snb....snb, have not tried yours but Chris nailed it....I guess the quotes did the trick in changing from semicolon to comma as it appears to now work like a charm! I am too used to working with US Excel and always find myself messing up the ";" but the change to a comma appears to have done the trick.

A pint on me in you find yourself in Oslo.

Thanks,
Dave
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top