Using VBA select cells with values and name

[AChuckle]

Hi-

Currently I have a program that imports a set of names, phone numbers, locations, etc. in a worksheet, that is saved to a public server, into a tab in a second spreadsheet when a certain checkbox is selected. As part of that process the imported information is also named depending on the column. The only problem is that my programming selects all cells in a column starting at the second line but I only want it to select the cells with values and then name the range.

The first row will always contain a header that should not be named with the rest of the range.

For example
Name: Address:
John doe 123 street
Jane Doe 156 Street

Out of this I would want John Doe and Jane Doe to be called "Names" and 123 street with 156 street called "Address". I do not want anything after the third line in this example included in the named range.

This is my current code to name the first two ranges in the first tow columns (the other columns use the same code with different range names):

Sheets("Names").Range("A2:A" & Range("A2").End(xlDown).Row).Name = "Names"
Sheets("Names").Range("B2:B" & Range("B2").End(xlDown).Row).Name = "Address"

Thanks!

 

[MrKowz]

I would recommend defining these with the name manager rather than with VBA. That way if the size of the list changes, you don't need to runt he VBA code again.

Try defining the following named ranges:

Name:
=Names!$A$2:INDEX(Names!$A:$A,MATCH(REPT("z",255),Names!$A:$A))

Address:
=Names!$B$2:INDEX(Names!$B:$B,MATCH(REPT("z",255),Names!$B:$B))

 

[AChuckle]

Thanks for the fast reply. Unless I am mistaken Name Manager comes with Excel 2007 but not with 2003. Sadly, the company software is still Excel 2003 so Name Manager is not an option.

Did forget to mention that the named ranges are being added to validation lists at the end of the macro code. So a named range that is equal to only the cells with values in them would eliminate a bunch of blank annoying blank spaces.