Name a Range of Cells - Number of Cells is Variable 1

[MattGreer]

I fail at searching, and thank you for your help in advance.

I have a worksheet, Sheet1, on which I have a list of cars in column A. It will always be in column A, and there will be a header "Cars" in cell A1. How do I select the cells and name the range?

I have this macro I recorded, but I can't figure out how to Name the Range if the number of cars in the list changes. The part I don't know how to work around is the reference to "R2C1:R39C1". That might change depending on how long the list is.

    Sheets("Sheet1").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="CarsList", RefersToR1C1:= _
        "=Sheet1!R2C1:R39C1"
    Range("A2").Select

I feel so dumb here. Thank you for your time.

Follow up question would be, how would I do the same if the list of Cars was a table? Or would that be a better solution overall? What I'm going to end up doing is looping through the list. Would it be easier to use that list of Cars in VBA and loop through each that way?


Thanks!!


Matt

 

[SkipVought]

Hi,

I'd make you car list into a Structured Table via Insert > Tables > Table (manually, one time). You can Name this new table something meaningful. Excel will automatically name your table Table1. As rows are added or deleted, the range will adjust.

Then your loop would look something like this...

Dim rCar As Range

For Each rCar in [Table1[Cars]]

Next rCar

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

If you'd rather use a Named Range, put this code in the Sheet1 Worksheet_Change event

'
    Range("A2").Select
    With Range(Selection, Selection.End(xlDown))
       ActiveWorkbook.Names.Add Name:="CarsList", RefersTo:= _
        "='" & .Parent.Name & "'!" & .Cells.Address(False,False)
    End With

From my iPad

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Thank you thank you Skip. You are a scholar and a gentleman.

Thanks!!


Matt

 

[SkipVought]

And there aren't many of us left!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Heyo skip, when I run the code for some reason it adds an additional row after the last entry. The cell is blank. Can't quite figure out why. Any thoughts?

Right now the other code that's based on the Named Range would require editing, and I gotta get this done for tomorrow. I'll implement the table-based solution tomorrow. In the meantime I would like to better understand how the extra row gets included?

Thanks!!


Matt

 

[SkipVought]

Does that extra cell have a SPACE character or any non-display character in it?

Right now the other code that's based on the Named Range would require editing

Not sure what "other code" you're referring to???

When dealing with tables that will be referenced in code, the spreadsheet superintendent must take precautions that stray DATA does not creep into the Sheet. This can happen when untutored users "BLANK" out the contents of a cell using the SPACE bar. OR, harder yet, when row(s) of formatted cells are "DELETED" using a key that only CLEARS text, leaving OTHER CELL FORMATTING, that appears to be DATA. Such DELETING must include SHIFTING cells to completely eliminate any and all cell formatting.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

In addition to what I stated previously, here's some code that can indicate a problem if the Selection is outside the expected range for the UsedRange on any given sheet, by observing that no rows or columns on the periphery of the Selection appear empty...

Sub ThisUsedRange()
   ActiveSheet.UsedRange.Select
End Sub

For any empty rows/columns, Select and Delete with the appropriate Shift of rows/columns.

A Test: On an new Sheet, enter any displayed character into cells B2 and D5. Then execute ThisUsedRange. The UsedRange will be B25. No empty rows/columns on the periphery.

Then enter a SPACE character in G7, and run ThisUsedRange again. Notice the empty rows/columns on the periphery-- rows 6:7, columns E:G. These are the rows/columns to Delete Shift.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Two odd things happening.

One, after the code is executed, if I click anywhere else on the sheet, the named range is created at that point. It keeps happening. Wherever I click, the named range follows the active cell. Two, which was mentioned, the named range extends one row below the used area.

I made a video: VBA Weirdness. In the video, I delete every other row and column and run the code.

If I select 'A1' at the beginning, the code works although I don't want to use A1 as a part of the named range of course. It's weird. If I Select or Activate cell A2, the named range is A3:A11. If I Select or Activate cell A1, it selects A1:A10.

The "other code", what I was referring to, is the code that loops over each car. So for each car in range, [do stuff].

I hate to bother you with something so simple but this is really confusing for me.


Thanks!!


Matt

 

[SkipVought]

sorry

'
    Range("A2").Select
    With Range(Selection, Selection.End(xlDown))
       ActiveWorkbook.Names.Add Name:="CarsList", RefersTo:= _
        "='" & .Parent.Name & "'!" & .Cells.Address([b]True,True[/b])
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

lol, I found some old Range selection code examples, so I ended up with this which works but sure is convoluted! I like what ya just posted there. Thanks!!

Sub NameARange()
    Dim TopCell As Range
    Dim BottomCell As Range
    
    Range("A2").Select
   
    If IsEmpty(ActiveCell) Then Exit Sub
    
    On Error Resume Next
    
    Set TopCell = ActiveCell
    If IsEmpty(ActiveCell.Offset(1, 0)) Then Set BottomCell = ActiveCell Else Set BottomCell = ActiveCell.End(xlDown)

    ActiveWorkbook.Names.Add Name:="CarsList", RefersToR1C1:=TopCell.Address & ":" & BottomCell.Address

End Sub

edit: Actually, I'm not sure this works. It's missing the reference to the sheet name, but the code seems to run "OK"?


Thanks!!


Matt

 

[SkipVought]

Had been away from my PC. My memory@78???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Man, if I knew half what you do at half your age I would be twice as competent as I am now.

I think... that made sense? Anyway.

I'm trying to loop through another table, picking off one of the columns. I can't quite figure out how to refer to a specific column in a table in VBA.

Sub LoopThroughTable()
    Dim tbl As ListObject
    Dim rGL1 As Range

    Set tbl = ActiveSheet.ListObjects("MyTable")

    For Each rGL1 In tbl.Range([GL1])
        Debug.Print rGL1.Value
    Next
End Sub

The Immediate window just shows one instance of the word "Index".

Thanks!!


Matt

 

[MattGreer]

And a few internet searches later:

Sub LoopThroughTable()
    Dim tbl As ListObject
    Dim LstRow As ListRow

    Set tbl = ActiveSheet.ListObjects("MyTable")

    For Each LstRow In tbl.ListRows
        Debug.Print Intersect(LstRow.Range, tbl.ListColumns("RowName").Range).Value
    Next LstRow

End Sub

Thanks!!


Matt

 

[SkipVought]

how to refer to a specific column in a [Structured] table

Dim rCell As Range

For Each rCell In [TableName[HeaderName]]
   Debug.Print Intersect(rCell.EntireRow, [TableName[AnotherHeader]]).Value
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Thanks Skip. After figuring out I didn't have to put quotes around the names I got that to work. Happy new year to you and yours!

Thanks!!


Matt

 

[SkipVought]

...and to you as well.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein