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

Board Regular
Joined
Jul 16, 2012
Messages
59
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!
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Mindpsyche

Well-known Member
Joined
Mar 19, 2012
Messages
760
Try this:

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 16, 2012
Messages
59
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

Well-known Member
Joined
Mar 19, 2012
Messages
760

ADVERTISEMENT

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

Well-known Member
Joined
Mar 19, 2012
Messages
760
Just tried that and it did exactly the same thing, not exactly sure what you want. Post some data if you can please.
 

jdahlke

Board Regular
Joined
Jul 16, 2012
Messages
59

ADVERTISEMENT

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

jdahlke

Board Regular
Joined
Jul 16, 2012
Messages
59
hey mindpsyche, thanks for your help, but unfortunately the code given came up with an error when run.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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

Well-known Member
Joined
Mar 19, 2012
Messages
760
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top