VBA to Name Blocks of Cells with Specific Values

[jasoncg1]

Hi,

I am working with a sales data spreadsheet. The spreadsheet's columns are: A) Store#, B) Description, C) Delivered, D) Scanned, E) Inventory, F) Sell Through, G) Previous Day, and H) Past 3 Days.

I have the sales reports spreadsheet set up with auto-filters, so I select the store number first and then run my VLOOKUP formulas in an ordering spreadsheet in the range of data that filters based on the store number. My question is whether there is a way to automate looking up data based on a certain range. For example, store number 2623 data shows up in rows 3:58. Store 2665 shows up in rows 114:178 on today's reports. Tomorrow's reports, however, may be different in new items are added. So when I run my VLOOKUP for store 2623, the range is different than any other store, and can change day by day.

I figured this can be done by naming each range with the store number or name of the location, but I'm not sure how to do that with VBA. Currently, there are 29 stores that need to be named in ranges.

As is, the sales reports come out in a very messy format with randomly merged cells, and extra columns and rows blank in between. I created a macro that cleans all of that up and puts it into a nice auto-filter format with no blank rows or columns in the data range.

Any help is greatly appreciated!

Thanks,
Jason

 

[mikerickson]

Are all of a store's rows together?

 

[jasoncg1]

Yes, when I run my clean-up macro to put the report into a useable format, it sorts first by store number.

 

[mikerickson]

If your clean up macro sorts column A (store#) ascending, then MATCH(1234, A:A, 0) is the first row for store #1234 and MATCH(1234, A:A) is the last Row for store #1234

So =INDEX($A:$A, MATCH(1234, $A:$A, 0), 1 : INDEX($H:$H, MATCH(1234, $A:$A), 1) is the range of cells in columns A:H for that store.