Can not select whole subtotaled range by code

[Chirag R Raval]

Dear All Experts,

on previously subtotalled range , macro already record all steps of selection this range , but if run that recorded macro , fail to select subtotalled range.

each steps images attached

step 1- normally select first row till A1 to F1



step 2-activate E1 (just press enter key 5 times)




Step-3 Just constantly pressed "Shift" key & press & release "End" key & press and release "Down Arrow" key result as below




step 4 just run recorded macro.



its just stop selection at total of that column, instead of till end of data.

I may times try as per below code , used with end with, with selection , but all time fail
to select this whole subtotalled range .

wondering that , if manually (key combinations) successful done, but
as per VBA its all time fail..

I just want that range to convert named range code is below. this is a part of big macro

sub ShrtMonthlyReport ()
Dim mymainrng As Range

ActiveSheet.Range("a1", ActiveSheet.Range("A1").End(xlToRight)).Select
    Selection.Cells(1, 5).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Columns(5).Select
           
                ActiveWorkbook.Names.Add Name:="MyMainRng", RefersTo:=Selection
                Application.Goto Reference:=ActiveWorkbook.Names("MyMainRng").Name

how to just select subtotalled all range?

regards,

Chirag Raval

 

[Marc L]

Hi !

See CurrentRegion and UsedRange in VBA inner help.
Or like in your previous threads and many others of this forum,
you can start from last cell of a column and use End(xlUp).

As a reminder, a good code does not need to select any range …
​

 

[Chirag R Raval]

Dear Sir @MarcL,

Thanks for your reply,
Actual purpose is not select it but make it just
Named range & through my code , can't define this range .

Main thing is columns A, B,C,& D have some blank parts,
So for properly define accurate range we use Column E or F due to
It's continues from top to bottom , & it's naturally dynamic because
Top is fixed but end is not..so it is common to go top to bottom .

This range, finally shaped after trimmed some rows and columns..& due to ( near sure)dought that may be
Some formatting remains outside of this range & hence current region & used range can cover this
Extra formatted rows & columns (which is not necessary in required range) in it so it's near advisable to go through and take stands for
Perfectly refer this range as take base on filled & continued range.

Ofcours, your bottom to top approach do its job ..& also it's can cover some coding to properly trap that bottom last cell,
but, one thought in mind That not found proper satisfied solution that why only hardly pressed Shift key and "End" key + "Down" key
Can work ? Why same process fail if we try to through VBA?
Why VBA drop/ sleep/ miss the grip from selection When activate one of its cell?

With and end with selection approach also fail..(Need to "Set" this selection there? )

If as another approach, may be send key if work,
How to code that continuesly pressed Shift key , till send
"End" key then "Down key? And then release shift key?

Hope there are found reason to fail & solution as
Either Send key approach or some different approach to surly & accurately define this range.

Regards,

Chirag Raval

 

[Marc L]

Down ?‼ No UP as yet explained :

you can start from last cell of a column and use End(xlUp).

So from last worksheet #row via Ctrl ↑ …
​

 

[Chirag R Raval]

Dear Sir @MarkL,

Thanks for your reply,
Really , I can't understand End(Xlup) as per your post no 2, how can construct code as it ?

I obey your guide & try to select this range, as per below.

Sub TestSel1()
   ActiveSheet.Cells(1, 6).Activate
         ActiveCell.End(xlDown).Activate
              Range(ActiveCell, ActiveSheet.Range("A1")).Select
End Sub

though its working but cant understand your post no 2 method.

But , after many try, as per below code (As solution of this thread)
I can select this range with End(Xldown) command..
as per below code

Sub TestSele()
ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlToRight)).Select
    Selection.Cells(1, 5).Activate
    Range(Selection, ActiveCell.End(xlDown)).Select
End Sub

Also attached Test Excel file with this code in module.

please guide about its properness or another approach.

Regards,
Chirag Raval

 

[Marc L]

Respecting TBTO rule :​

Sub Demo1()
    Debug.Print [A1].CurrentRegion.Address
End Sub

 

[Chirag R Raval]

Dear Sir,

Just Amazing, perfect, One Line Code , return Start To End of range...

but doubt is current region may have blanks but formatted rows & columns
how to overcome that doubt.?

Regards,
Chirag Raval

 

[Marc L]

Yes, like explained in VBA inner help, easy to read and try !

If the expected range contains an entire blank row or column,
CurrentRegion is not the way, better is to use End(xlUp) from
last worksheet #row or sometimes from UsedRange
or with the universal Range.Find in a reverse way …
​

 

[Chirag R Raval]

Dear Sir MarcL,

Thanks.
Matter is , below this big main range,
1 small range have some data in it & both ,
oftenly require to access & should be clearly defined..
so . End (xlUp) May lead to mess up , hence I prefer
Up to down .

As I already know that define range, based on selection , leads to huge bad impact on performance.
Actually I just want perfectly define this range , so I can access it..So just for clear concept , How to define
Require range, I start this thread base on selection so we can go step by step & can see on screen what our code do
& which modification require.
(Just want to see real effect on sheet, without help of watch window or
immediate window in VBA.) For just clear understanding the matter.

After acquire result based on Select..Now we reach at point that same range can be
define without select it.

How to trap / define this range ( A1 to F1 , & last cell of Column F).

Hope there way to create it without select it . In other words
Define / create range through just reference / pointing some points on sheet.

Regards,

Chicago Raval

 

[Marc L]

Different ways to define a Range object …

How to trap / define this range ( A1 to F1 , & last cell of Column F).

Yet shown in post #6, no?!

Or clearly explain what that means,
give us the address of the expected Range …
​