Macro to create range names - Excel 97 1

[sgreenwood]

I have a report that I create daily that requires naming several ranges. I am trying to figure out a macro that would automate the naming of the ranges, but so far I’m having NO luck at all!

Obviously the area of these ranges changes daily...today range A is 4000 rows, tomorrow maybe 4250, etc. Does anyone have any idea of how I could use a macro to automate this process?

I’m thinking there must be some way to say start here…and include all data until field "X" changes then create range name...now continue on with next section until the field changes again...and continue until all ranges are named.

The data is structured as follows:

“Grower Type”, “Commodity”, “Variety”, “Grower Number”, “Daily Units”, “Daily FOB”


I want to create ranges that include the columns “Variety” through “Daily FOB” and for each “Grower Type”.

For example Range A = Company Farmed
Range B = Outside Growers
Range C = Joint Venture

To be honest with you….I am completely lost trying to figure this one out. (and have probably confused everyone else by now!!)

Any ideas anyone might have about this would be GREATLY appreciated.

Thanks,
Steve

 

[JVFriederick]

Instead of trying to use a macro, just enter a formula into the range name address. Paste this formula :

=OFFSET(X!$B$5,1,2,COUNTA(X!$B:$B)-1,1)

where :
X is the sheet name
$B$5 is the starting point location
1 = now go one more row down from B5
2 = now go two more columns from B5 (so actually start at D6)
COUNTA(X!$B:$B) = count all records in column B, subtract 1 because there is some text in column B we don't want to count. This is how many rows long the range name should be starting at D6.
1 = How many columns wide the range name should be. Sounds like in your example this may need to be greater than 1.

The reason you use column B to name column D is because B will always have a value, sort of like an index key. Data in D may or not be present.

 

[sgreenwood]

Thanks very much for your response! I'm not sure if this will work in my situation though.

The second part of your formula

"$B$5 is the starting point location"

is where I see the problem I believe my problem comes into play. What I actually need to do with this data is create about 10 different ranges names each day. The only "starting point location" I know would be the first range in the data. Each day as the data grows the "starting location" for each range will be changing. That's really the crux of my problem...how to tell the macro (or formula for that matter) where to end this range and start a new one.

Thanks very much for your response!

 

[JVFriederick]

The formula I provided will automatically adjust your range names according to how much data you have each day.

Is your data contained within a single column ? If yes, then what I wrote will not work.

If your data exists in different columns, then the range name formula will work very well.

 

[sgreenwood]

I'm afraid I'm just being dense here...I can't get the formula to work. I've placed a massively simplified example of my data below:

A B C D E F G H I J K L M N O
1 X A A A A A 1 1 1 1 1 1 1 1 1
2 A Sub Total 1 1 1 1 1 1 1 1 1
3 X B B B B B 1 1 1 1 1 1 1 1 1
4 B Sub Total 1 1 1 1 1 1 1 1 1
5 X Total 2 2 2 2 2 2 2 2 2
6 Y C C C C C 1 1 1 1 1 1 1 1 1
7 C Sub Total 1 1 1 1 1 1 1 1 1
8 Y D D D D D 1 1 1 1 1 1 1 1 1
9 D Sub Total 1 1 1 1 1 1 1 1 1
10 Y Total 2 2 2 2 2 2 2 2 2
11 Grand Total 4 4 4 4 4 4 4 4 4


What I'm trying to accomplish is to automatically (through formula or macro) create (in this example) two range names.

Range 1 = CELLS D1-O5

Range 2 = CELLS D6-O10

I want to start a new range every time the data in column "A" changes...I think the catch might be that there will be numerous "blank" cells in column "A" because of all of the subtotals. In reality each of these ranges have eight levels of subtotals...meaning column "A" is often empty.

This is why I thought there might be a way to accomplish this using a macro?

Thanks again for your time and help.

 

[DaleWatson123321]

Hi Steve,

I took your data and created an Excel file, and the following VBA code performs the task you require - that of creating Range Names automatically, based on the data in the database.

If you have any difficult with the code, please don't hesitate to ask for the file - just email me and I'll return the file.

If anyone else out there could also use this example, again, simply email me, and and send you the file.

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

=============
START OF CODE
=============

Dim endrow As String
Dim value1 As String
Dim value2 As String
Dim firstrow As String
Dim lastrow As String
Dim value_initial As String
Dim addr1 As String
Dim addr2 As String
Dim coord As String
Dim range_name As String

Sub App_Start()
Initialization
Find_End_Row
Go_Start
Create_Names
Go_Start
End Sub


Sub Initialization()
endrow = ""
value2 = ""
End Sub

Sub Find_End_Row() ' finds last row - used in termination of process
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(10, 0).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
endrow = ActiveCell.Row
End Sub

Sub Go_Start() 'goes to the starting point - top of the data
Range("toprow&quot.Select
ActiveCell.Offset(1, 0).Select
End Sub

Sub Create_Names() ' identifies top/bottom row of each name,
' then calls subroutine "Set_Name" to create the name
firstrow = ActiveCell.Row
Do
If endrow = ActiveCell.Row Then Exit Sub
value1 = ActiveCell.Value
value2 = ActiveCell.Offset(0, 2)

If value2 = "Total" Then
Set_Name
firstrow = ActiveCell.Row + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub Set_Name() ' subroutine from "Create_Name" above - creates each Range Name
lastrow = ActiveCell.Row
addr1 = "D" & firstrow
addr2 = "O" & lastrow
coord = addr1 & ":" & addr2
range_name = "Range_" & value1
Range(coord).Name = range_name
End Sub

===========
END OF CODE
===========

 

[sgreenwood]

Thanks VERY much Dale, I will give this a try this morning.

Steve