Subtotal

[ashish mehra]

Hi all,

I have data that keeps on changing & I would like to use Subtotal in VBA at the lastrow.

Regards,
AM

 

[ashish mehra]

File attached.

 

[NARAYANK991]

Hi Ashish ,

Something like this :

Public Sub InsertSubtotalFormula()
           Dim Lastrow As Long
           Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
           Cells(Lastrow + 1, "A").Formula = "=SUBTOTAL(3,A2:A" & Lastrow & ")"
End Sub

Narayan

 

[ashish mehra]

Thanks Narayan,

That works perfect!

Regards,
AM

 

[Somendra Misra]

Hi Ashish,

I don't know the exact model where you are going to use this, but if this calculation is not on the display sheet and you are using in intermediate calculation than why not define a named formula to get last row and use SUBTOTAL in top row (as illustrated in the file).

Pros which I feel:
1. I don't have to run the sub-routine every time.
2. I don't have to scroll down in case of large data set to see the total.

Ignore this comment if VBA is must.

Regards,

 

[ashish mehra]

Hi SM,

Thanks for your valuable comments.

I am preparing an automate report in which I am missing on the Subtotal step; without this step my project was incomplete; in case the number of rows are increased. However, I will consider your suggestion.

One thing I have not understood;

the Lastrow function; Is it in-built, UDF or ?

Regards,
AM

 

[Somendra Misra]

@ashish mehra

Lastrow is the named formula to find last row with data. Find it in Name Manager.

Regards,

 

[ashish mehra]

Thanks SM,

I missed to check Name Manager.

Regards,
AM