×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Name a Range of Cells - Number of Cells is Variable

Name a Range of Cells - Number of Cells is Variable

Name a Range of Cells - Number of Cells is Variable

(OP)
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.

CODE --> VBA

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

RE: Name a Range of Cells - Number of Cells is Variable

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...

CODE

Dim rCar As Range

For Each rCar in [Table1[Cars]]

Next rCar 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

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

CODE

'
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
Thank you thank you Skip. You are a scholar and a gentleman. :)

Thanks!!


Matt

RE: Name a Range of Cells - Number of Cells is Variable

And there aren't many of us left! 🤓

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
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

RE: Name a Range of Cells - Number of Cells is Variable

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

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...

CODE

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 B2:D5. 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
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

RE: Name a Range of Cells - Number of Cells is Variable

blush sorry blush

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
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!!

CODE --> VBA

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

RE: Name a Range of Cells - Number of Cells is Variable

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
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.

CODE --> 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

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
And a few internet searches later:

CODE --> VBA

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

RE: Name a Range of Cells - Number of Cells is Variable

Quote:

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

CODE

Dim rCell As Range

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Name a Range of Cells - Number of Cells is Variable

(OP)
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

RE: Name a Range of Cells - Number of Cells is Variable

...and to you as well.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close