• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To sum Exact below last cell of Dynamic Named Range , if not all, but random cell have value

C

Chirag R Raval

Member
Dear Sir,

I Want target exact last cell of named range, so I can put sum formula with use of offset method there,

Please note that , this dynamic named ranges random cells have value, not all cell have value . Some cells are empty Or blank & some have value..not fully filled this range..

In other words , how to reference / target/ access /define exact next cell below Dynamic named range end if this range not have all cell filled?

how can we reference/target/access/ define..., first ,OR last Or middle cells as dynamically ?

screen shot for requirement/Situation ...is below

named range sum.jpg

hoe you little guidelines..

Regards,
Chirag Raval
 
Dear Sir @Hareesh_LKD,

Thanks for your effort.

I also can put simple formula

=sum(myrng) in any cell of the sheet & it give result.

but how to catch exact cell, below last cell of this range ?
can you explain about your formula ?
how its return cell below last of range? how its return sum ? how it can be done with vba (Ctrl+Shift+Enter)?

you can see result of your formula in below screen shot if it enter in any cell..

somethijg wrong-2.jpg

please suggest.

Regards,
Chirag Raval
 
Hi Chirag, try the following code, it will select the cell below the named range
Code: 
Sub myrangelastcell()

    Dim r As Range
    Dim a As Variant
    Dim b As Variant
  
  
    Set r = Range("myRng")

    a = Split(r.Address, ":")
  
    b = UBound(a)
  
    Range(a(b)).Offset(1, 0).Select

    ActiveCell.Value = WorksheetFunction.Sum(r)
  
End Sub
 
Hi !
Chirag R Raval said:
how can we reference/target/access/ define..., first ,OR last Or middle cells as dynamically ?
Click to expand...
For this kind of need a code must avoid to select anything
to be efficient, so just respecting TBTO rule :​
Code: 
Sub Demo1()
    [myrng].Cells([myrng].Count)(2).Value = Application.Sum([myrng])
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Hareesh_KLD said:
try the following code, it will select the cell below the named range
Click to expand...
Code: 
Sub myrangelastcell()
    Range(Split([myrng].Address, ":")(1))(2).Value = Application.Sum([myrng])
End Sub
And works only if myrng's worksheet is the active worksheet …
But no matters if it's the active worksheet or not with my code !

So whatever the active worksheet :​
Code: 
Sub myrangelastcell()
    V = Split([myrng].Address, ":")
    [myrng].Parent.Range(V(UBound(V)))(2).Value = Application.Sum([myrng])
End Sub

Last but not least, without any offset :​
Code: 
Sub Demo2()
    [myrng].Cells([myrng].Count + 1, 1).Value = Application.Sum([myrng])
End Sub

In case of a multi-columns source range enhance with .Rows.Count …

Think, But Think Object !
​
 

Hi Belleke !

You misunderstood as the total must be after the last cell of named range
even if blank as you can see in first screenshot …
​
 
Hi Mark,
You maybe right (I am Dutch and mis understood) I saw Dynamic named range and I went from there:oops:
 
Dear Sir @MarcL, Sir @Hareesh_KLD And Madam Belleke,

All's Code construct is mindblowing, Really super solutions..

When i continuos try that how can capture some point of this range? & really... there are struck in my mind that "Address" property can go with this as a solution but dont know how to get last cell or row of this dynamic named range because it is return in combined form.."A1:A45" etc..so i stop there...i also struck that point that how to get only last /end of this range..

& here from your all, i get 3 amazing slutions ...really great learning really appricated your efforts & knowledge.. Sir @Hareesh_KLD's split and array approach to catch last point of this range can make a funda of learning about split & array...

can you explain about "Range(a(b)).Offset(1, 0).Select?" because i have some basic knowledge that range should be formed "ColRow:ColRow"
so can't understand "Range(a(b)). approach...


Sir @MarcL 's Little Wonder is also amazing & mind blowing approach..with Brackets "[myrng].Cells([myrng].Count + 1, 1).Value=" super one liner strock.. grat ..first time learning about we can get whole named or simple range.

"In case of a multi-columns source range enhance with .Rows.Count"


"range.count" (Can you spread some more ight on this sir?) really can become must be learned point if anyone working with range.

below also must to learn..who work on range..can need more explain..for knowledge.

Sub myrangelastcell()
Range(Split([myrng].Address, ":")(1))(2).Value = Application.Sum([myrng])
End Sub

Madam @Belleke 's approach about firstly, get the last row of dynamic range that approach/point/concept also appreciated

Code: 
Sub belle()
Dim LR As Long
    With ActiveSheet
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LR, "A").Value = Application.Sum([myrng])
    End With
End Sub

Bottom to Top approach to catch Last Cell of this range...good..

but if we have something filled cells bwlow this named range that time /situation Only Top to Bottom approach only can help..or another middle way may can direct reference / access /pointing... this named range approach..& though last cell/row/point is become very important & base thing , whole base is become this named range's last cell/row/point...

One important thing is to learn that we can offset any direction ,from any cell of named range , based Even if whole range is emply/blank, & there... just there, .Count .......Or Rows help to us.. (One Question here ..Count is Rows. count or Cells.Count? wich approach to use?)


Thanks again you all experts here..Hope 4th approach can be there.in someon's mind...just to learning .& sharing .. .maximum ...about how to view things as from many usefull aspects....because knlowledge can make many ways...

Just Great,

Regards,

Chirag Raval
 
Last edited:
Chirag R Raval said:
"In case of a multi-columns source range enhance with .Rows.Count"
Click to expand...
In your sample your range is a single column
from I147 (first cell) to I154 (last cell).
So [myrng].Count is the short way of [myrng].Cells.Count : 8 cells.
Any Range object has a Cells collection like here from .Cells(1) (I147)
to .Cells(8) (I154). So to find out the last cell of a Range object
from .Cells(1) to .Cells(n) (n as the last cell index) .Count property
gives this n index … Easy !

Now imagine your range has n rows by 2 columns like I147:J154.
[myrng].Count returns 16 as it's like .Cells.Count but if you want
to write something under last cell of first column (aka I155)
you must use [myrng].Rows.Count in order to get a result of 8 !
Just Think, But Think Object again …

Apply the same logic for any cells range (like CurrentRegion)…

The main reminder is the first cell :
[myrng].Cells(1) is the short way of [myrng].Cells(1, 1).
This is a flag, like a home 'cause from this range all is relative to this first cell.
For example if you want to write in a cell just under the range (row #155)
but 3 columns to the right : L155.
So check via Debug.Print or MsgBox to see what returns
• [myrng].Cells([myrng].Count).Offset(1, 2).Address
• [myrng].Cells([myrng].Count)(2, 3).Address
• [myrng].Cells(1, 1).Offset([myrng].Rows.Count, 3).Address
• [myrng].Cells([myrng].Rows.Count + 1, 4).Address …

See also relative to first cell (I147) what is the address of
• [myrng].Range("A1")
• [myrng].Cells(0)
• [myrng].Cells(0, 1)
• [myrng].Cells(1, 0)
• [myrng].Cells(0, 0)
• [myrng].Cells(1).Offset(-1, -1) …
​
Chirag R Raval said:
Sub myrangelastcell()
Range(Split([myrng].Address, ":")(1))(2).Value = Application.Sum([myrng])
End Sub
Click to expand...
From your range I147:I154 Split function returns an array of two elements
always starting from index zero for the first element so its second element
has an index of 1, with the delimiter ":" aka FirstCell:LastCell address.
So Split([myrng].Address, ":") is the entire array but followed by (1)
it means only the second element aka LastCell address : I154 …
So Range(Split([myrng].Address, ":")(1))
is like Range("I154") or [I154].
Now Range(Split([myrng].Address, ":")(1))(2) is like Range("I154")(2)
or like Range("I154")(2, 1) or like Range("I154").Offset(1, 0) or
like Range("I154").Offset(1) aka I155 …

What is a worksheet ?
Just a big array of cells where its first cell is
Range("A1") or [A1] or Cells(1) or Cells(1, 1) or
Columns(1).Cells(1) or Rows(1).Cells(1, 1) or …
Remember the flag, the home !
Check now what is the address of Cells(Columns.Count + 1)
in order to see what is first a worksheet ?
​
 
can you explain about "Range(a(b)).Offset(1, 0).Select?" because i have some basic knowledge that range should be formed "ColRow:ColRow"
so can't understand "Range(a(b)). approach...

Hi,

Assume you set myrng = "A3:A20"
when you use split using delimiter ":" to myrng.address it will divided in to "A3" and "A20"
in my code i used a as array. now array "a" contains A3 and A20,
lbound(a) = 0 i.e A3
Ubound(a)=1 i.e A20

i stored ubound(a) in b, means b = 1
therefore array a(0) = A3 and array a(1) = A20

hope i explained
 
Dear All Exprets, @MarcL & @Hareesh_KLD

Amazing Twist in fundamental Knowledge...

'The main reminder is the first cell :
[myrng].Cells(1) is the short way of [myrng].Cells(1, 1).
This is a flag, like a home 'cause from this range all is relative to this first cell."

Thanks for this amazing understanding..& effort to realize logic behind the scene or code....
I must take print & read repeatedly & must manually & do experiment in VBA on all elements of your guidelines , if I want to deeply understand things..

I constantly, step by step, repeat & clear this concept in mind if I (& All ) who really wish to grasp this amazing learning..

If there are raise some question in my mind regarding this , hope you can spread little focus on this..

Thanks you very much again for this , like never before found anywhere..
Thanks again for your all's valuable effort.


Regards,
Chirag Raval
 
You must log in or register to reply here.
Back
Top