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!

 

[Mindpsyche]

Try this:

Sub findrange()
    Dim rangex As Range
    Set rangex = ThisWorkbook.ActiveSheet.UsedRange
    rangex.Select
End Sub

 

[Rick Rothstein]

Unfortunately, the UsedRange suggestion of Mindpsyche may not always work correctly... UsedRange can, under certain circumstances, end up "remembering" cells that are no longer "in use". You may find this mini-blog article (and the subsequent discussion that followed from it) of interest...

Replacement for the Flawed UsedRange Property

 

[jdahlke]

thank you, but it should only select the values that would be selected if you do the following:
click on a1, hold shift, and key end home.

 

[Mindpsyche]

Thanks Rick, im still very new to vba so any feedback on my code is much appreciated, and i will have a look at that link

 

[Mindpsyche]

Just tried that and it did exactly the same thing, not exactly sure what you want. Post some data if you can please.

 

[jdahlke]

this is frustrating, cannot find a simple code to establish this range.

 

[jdahlke]

hey mindpsyche, thanks for your help, but unfortunately the code given came up with an error when run.

 

[Rick Rothstein]

this is frustrating, cannot find a simple code to establish this range.

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

 

[Mindpsyche]

What's the error? Once again, a more clearer explanation of how your sheet is set up and which range you need to be identified will enable more knowledgeable posters to help you.