Excel-VBA: create named Range in row until end of cell content

Question

Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row).

In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ??

i.e. I would like to create a named range per column and each range shall start from row-2 until the end of each column-length!

Thanks for any help on this !

Do you want to create VBA Range variables which are set equal to these ranges or do you want to, in VBA, create workbook (or worksheet) names for those ranges? If the latter -- this sort of thing is easy enough to do though you might run into the awkward fact that not every column header is a valid name ("C" and "R" for some odd reason are rejected if you try to use them for the interface. VBA might be able to override this, though that might have subtle side effects if so) — John Coleman
– John Coleman, Commented Jun 22, 2015 at 11:44

Raystafarian · Accepted Answer · 2015-06-22 12:06:32Z

2

Sure you can use this snippet to find the last filled cell in a column and use that row number to set your range.name - just replace the "A" with whatever column you'd like.

Sub test()
Dim lastrow As Integer
 lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 Range("A2:A" & lastrow).Name = "RangeColA"
End Sub

If you wanted to do it for each column you could try something like this -

Sub test()
Dim lastrow As Integer
Dim letter As String
Dim lastcol As Integer
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column


For i = 65 To 65 + lastcol
 letter = Chr(i)
 lastrow = Cells(Rows.Count, letter).End(xlUp).Row
 Range(Cells(2, letter), Cells(lastrow, letter)).Name = "RangeCol" & letter
Next

End Sub

edited Jun 22, 2015 at 12:06

answered Jun 22, 2015 at 11:44

Raystafarian

3,0622 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Coleman Over a year ago

the minus sign in the name defeats Excel's parsing

Raystafarian Over a year ago

@JohnColeman you are absolutely correct, I just changed it to match the Q format (without thinking). Fixed.

John Coleman · Accepted Answer · 2015-06-22 12:19:15Z

Here is another answer (inspired by Raystafarian's answer) that handles the case where the last used cell in the column appears at or above the second row and also gives more flexibility in the name:

Sub NameColumn(Col As String, Optional ColName As Variant)
    Dim n As Long
    Dim myName As String
    If IsMissing(ColName) Then ColName = "Range_" & Col
    n = Cells(Rows.Count, Col).End(xlUp).Row
    If n <= 2 Then
        Range(Col & "2").Name = ColName
    Else
        Range(Col & "2:" & Col & n).Name = ColName
    End If
End Sub

try various things in columns A through E (including leaving a blank column) then test it like this:

Sub test()
    NameColumn "A"
    NameColumn "B"
    NameColumn "C"
    NameColumn "D", "Bob"
    NameColumn "E"
End Sub

Collectives™ on Stack Overflow

Excel-VBA: create named Range in row until end of cell content

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related