Name a range

[mirko]

Hi folks,

I have a quick (and probably easy for most of you) question.

I want to name a range in Excel (e.g A1:F19) but the range can change according to the number of entries the spreadsheet has.

This is what I have now:


With Worksheets("PropSurveys").Range("a1")
Range(.Offset(1, 0), .End(xlDown)).Name = "Locations"
End With

This will just get the range from A1 all the way down. How can I change it to take the range from A1 to F1 and all the way down?

Thanks a lot for the help.

[Jan Karel Pieterse]

Your code is error prone because the Range statement starts with no reference to a worksheet. It therefore points to the active sheet, which isn't necessarily PropSurveys. Change to:

With Worksheets("PropSurveys")
.Range(.Range("a1").Offset(1, 0), .Range("a1").End(xlDown)).Name = "Locations"
End With

To your question:

With Worksheets("PropSurveys")
.Range(.Range("a1:F1"), .Range("a" & .Rows.Count).End(xlUp)).Name = "Locations"
End With

[tstav]

Hi Mirko,

-Since you want your named range to reach as far as column F (column 6) but you want it to be flexible as far as the rows are concerned, and
-since your data maybe ragged (you may have blank cells)

you might also consider the following


With Worksheets("PropSurveys")
.Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).Resize(, 6).Name = "Locations"
End With