code needed to establish a dynamic range in active worksheet, starting at a1 and extending n number of cols, and n number of rows

[jdahlke]

needs to cover a1 through the bottom right cell with a value in it. just like hitting end home.
I need a simple code for this, I've researched online and can't find anything simple. Need to name the range "rangex"




Thank you!

 

[jdahlke]

Using parts of the code I posted in the mini-blog article I referred you to, this single (albeit long) line of code will do what you asked for...

Range("A1:" & Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).Address).Select

This should work amazingly!! Thank you!

could i say "activerange.name=rangex " to name it?

 

[jdahlke]

thank you rick! how do i now name the range?

 

[Rick Rothstein]

thank you rick! how do i now name the range?

Once you have selected the range, you can execute this line of code to name that selection...

Selection.Name = "TheNameYouWantToGiveThisRange"

However, if that is your ultimate goal (only to name the range), then you do not need to select the range first as you can name it directly (very seldom is it necessary to select a range before you do something with it).

Range("A1:" & Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).Address).Name = "TheNameYouWantToGiveThisRange

 

[jdahlke]

Once you have selected the range, you can execute this line of code to name that selection...

Selection.Name = "TheNameYouWantToGiveThisRange"

However, if that is your ultimate goal (only to name the range), then you do not need to select the range first as you can name it directly (very seldom is it necessary to select a range before you do something with it).

Range("A1:" & Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).Address).Name = "TheNameYouWantToGiveThisRange

excellent. Thank you sir.