Create range based on value in column

[Rzrbkpk]

I need to create a ranges for a data set that changes everytime a report is run. The columns are a consistent order and count. The range would be based on the value in column B. I can work with A1 or index format. Here's an example of the data:

A B C D
U.S. New York Albany 200
U.S. New York Buffalo 10
U.S. New York Binghamton 30
U.S. Georgia Savannah 15
U.S. Georgia Atlanta 500

So my range for New York would be (A13) and for Georgia it would be (A45).

 

[SkipVought]

hi,

That is not a best and accepted practice for range names.

Rather consider this standard practice
[tt]
Country State City Amount
U.S. New York Albany 200
U.S. New York Buffalo 10
U.S. New York Binghamton 30
U.S. Georgia Savannah 15
U.S. Georgia Atlanta 500
[/tt]
where each column is NAMED using the column heading, quite simply using Formulas > Defined Names > Create From Selection > Create names in TOP row

No VBA required!

Excel's lookup and aggregation formulas are designed to work with ranges like this.

faq68-1331
faq68-5184

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Thanks for your response Skip. My apologies, the columns do have headers (actually the ones you indicated). The reason I'm using VBA is because my ultimate goal is to sort 20+ ranges by two other columns - Date(E) and Status(F) everytime the report is produced. The customer for the report is requesting that the report keep a non-sortable order (i.e. states in a specificied preference order) and then sorted by the other criteria within that grouping. I've managed to code the report to group all the states and produce them in the preferred order, but I can't figure out the date and status sort.

 

[SkipVought]

Well your original question and the example provided, has nothing to do with sorting!

Consequently, with the information you have provided, I fail to perceive your problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

I'll repost with a more accurate description of what I'm trying to accomplish.

 

[SkipVought]

Just explain it here!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

It sounds like all you need for the macro is to copy all of the data from the sheet to a clean sheet and then perform a SORT. The SORT would have a custom list for the City, and then by Status and Date (depending on how you want things grouped).

Alternately, you could apply FILTERS and possibly use the SUBTOTAL button. This might achieve a lot of the things you want with minimal coding.