Gaps in Dynamic Named Ranges

[murad5]

Hi Everyone,

I'm trying to speed up the calculation on a huge workbook I've inherited by converting it to use Dynamic Named Ranges. My problem is that on one sheet the data has gaps in it. As I understand it, the standard way to create a dynamic named range is to use Counta() in an Offset() formula. However, this will mean that I will be missing however many rows off the bottom of my range as there are blank rows in the range. Is there any simple formula I can use to get around my issue? Otherwise I think I'll just write some VBA to redefine the range using the on calculate event.

Thanks...

 

[SkipVought]

murad5,

If you data has unique headings then highlight your entire data & heading range and name the range(s) using menu item Insert/Name/Create - and then check the appropriate box where your headings reside.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[murad5]

Hi Skip,

That's really cool, I didn't know you could create a bunch of named ranges like that. Unfortunately, it doesn't seem to solve my problem. I don't think I was very clear on my previous post. Basically, the workbook has a sheet which stores prices and costs for components, and many sheets which lookup these values. Currently, these all use vlookups like so:
=vlookup(A1,Components!$A:$G,2,0)

I think the fact that it's looking up on the entire height of the sheet is slowing down the workbook immensely, especially as there are only around 300 to 400 rows being used on the components sheet at any given time. What I want to do is create a named rangeon which I can do lookups. What I would usually put into the named range to do this is:

=OFFSET(Components!$A$1,0,0,COUNTA(Components!$A:$A),6)

However, there are gaps in the data in column A, so the COUNTA is returning a smaller value than the last row I need. Can you think of anything to get around this?

Thanks...

 

[SkipVought]

Is there a column that has a value in it for every row that is used?

If, for instance, column X does...

=OFFSET(Components!$A$1,0,0,COUNTA(Components!$X:$X),6)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

You may be better off with a VBA solution in the worksheet_change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UsedRange.CreateNames _
        Top:=True, _
        Left:=False, _
        Bottom:=False, _
        Right:=False
End Sub

assuming that the top row has your headings.

Alternatively, if the CurrentRegion toolbar icon selects all your data...

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.CurrentRegion.CreateNames _
        Top:=True, _
        Left:=False, _
        Bottom:=False, _
        Right:=False
End Sub

which may be better since UsedRange can be unreliable if there are deletes occurring on your sheet.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[murad5]

Thanks for all your help Skip. In the end, I used match in the definition of the range:

=OFFSET(Components!$A$1,0,0,match("Contribution",Components!$A:$A,0),6)

Do you think that this is any better than before? It's still looking up on the entire column...

 

[SkipVought]

I assume that "Contribution" is in the last row of data. I'd op for the worksheet change approch myself if "Contribution" could be somehow modified or deleted.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Hi murad5 and Skip,

How about simply including the following in a Workbook_Open event... (or SheetCalculate event, or whatever event is best for the context). This will assign a (range) "name" to the used range. I've used "CompData" but of course you can use whatever name you prefer.

Sub Workbook_Open()
Worksheets("Components&quot.Select
ActiveSheet.UsedRange.Name = "CompData"
[A1].Select
End Sub

Then all formulas that reference the "CompData" range can be written like the following...
=VLOOKUP(A1,CompData,2,0)

Please note that in using any named range, it does NOT require that the "Worksheet" be referenced.

I hope this helps Please advise as to how this fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca