VBA Naming Ranges

[AChuckle]

Hi Everyone-

I have some code that names a set of columns in 1 of 5 Excel tabs. This code has worked fine for months but now suddenly only selects the first 5 cells in column D and names the range "UWNames". I cannot figure out why it is suddenly and only in column D selecting 5 cells instead of all the cells in the column. Everything is running on XP and Excel 2003, soon to be converted to Windows 7 and Excel 2007.

My code for naming the ranges is below. It is not the best way to select cells but worked for what I needed. Any suggestion on fixing up the code so it only selects the cells with information in them starting at the second cell in the column would be appreciated as well.


Sheets("Names").Range("A2:A" & Range("A2").End(xlDown).Row).Name = "SSNames"
Sheets("Names").Range("B2:B" & Range("B2").End(xlDown).Row).Name = "SSPhones"
Sheets("Names").Range("C2:C" & Range("C2").End(xlDown).Row).Name = "SSIDs"
Sheets("Names").Range("D2:D" & Range("D2").End(xlDown).Row).Name = "UWNames"
Sheets("Names").Range("E2:E" & Range("E2").End(xlDown).Row).Name = "UWPhones"
Sheets("Names").Range("F2:F" & Range("F2").End(xlDown).Row).Name = "UWIDs"
Sheets("Names").Range("G2:G" & Range("G2").End(xlDown).Row).Name = "DUWNames"
Sheets("Names").Range("H2:H" & Range("H2").End(xlDown).Row).Name = "DUWPhones"
Sheets("Names").Range("I2:I" & Range("I2").End(xlDown).Row).Name = "DUWIDs"
Sheets("Names").Range("J2:J" & Range("J2").End(xlDown).Row).Name = "SSTLNames"
Sheets("Names").Range("K2:K" & Range("K2").End(xlDown).Row).Name = "SSTLPhones"
Sheets("Names").Range("L2:L" & Range("L2").End(xlDown).Row).Name = "SSTLIDs"
Sheets("Names").Range("M2:M" & Range("M2").End(xlDown).Row).Name = "SCNames"
Sheets("Names").Range("N2:N" & Range("N2").End(xlDown).Row).Name = "SCPhones"
Sheets("Names").Range("O2:O" & Range("O2").End(xlDown).Row).Name = "SCIDs"


Thanks

 

[circledchicken]

Hi,

Are the column headings (in row 1) the same as the names you want? Does the data end in column O? If so you may be able to reduce this to one step i.e.

Sheets("Names").Range("A1").CurrentRegion.CreateNames Top:=True

This is analogous to selecting your range that you want names in, with the top row containing the headings/names, and doing 'Insert > Names > Create > Create names from Top row' (alternatively keyboard shortcut CTRL-SHIFT-F3)

 

[AChuckle]

The first Row is all headers but they are not the same as the names given to the ranges. It also extends out past column O all the way to column AI but my coding was the same for each minus a different named range title.

I will try out that code and see if it works.

 

[AChuckle]

Tried the code but I am hving issues putting that named range into a data validation drop down list that is also created through VBA. Now the list is not even created. I tested the code on just the first column of the spreadsheet. Here is the code I had to create the drop down if it helps.

Sheets("Case Summary").Range("C49").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=SSNames"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[circledchicken]

Hi,

Yes, I think you would need to layout your data differently to get that to work.

Maybe try this alternative to your original post:

With Sheets("Names")
    .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "SSNames"
    .Range("B2", .Cells(Rows.Count, "B").End(xlUp)).Name = "SSPhones"
    .
    .
    .
End With

 

[nattasiray]

My techniques is using Dynamic Range Name.

1. Goes to the target cells
2. Press Ctrl+F3 to activated Name Manager Dialog box
3. press New button
4. Type the name that you want in to name: text box such as SSNames
5. Clear text at the refers to: text box.
6. type =offset(
7. click Tab Sheet Name
8. Click cell A1
9. type ,1,0,counta(
10. Click Column A Header
11. type )-1,1))
12. Click OK
13. Click Close

Testing

1. Try to add new data into blank cell below the last cell of column A
2. Press F5 to activate Go To Dialog box
3. type SSNames in to reference text box
4. click Go
5. See the excel select the range
6. delete the new data entry at the last cell of column A
7. Press F5 to activate Go To Dialog box
8. type SSNames in to reference text box
9. click Go
10. See the excel select the range

You will see that the selection will expand or shrink automatically depend on the number of data

This technique will eliminate the naming range step using VBA when the new data is adding or delete the data.

Hope it will be helpful information to you.

Best reguard

 

[AChuckle]

Thanks circledchicken. That works great and eliminates another issue I was having with the named ranges taking up the entire column rather than just the few cells with information in each column. Huge help!

 

[circledchicken]

Your very welcome,

However, I agree with nattasiray that dynamic named ranges may be a better alternative to avoid using code. In addition to nattasiray's post, here's a link that might help you get started:

http://www.ozgrid.com/Excel/DynamicRanges.htm