variable ranges using VBA

[luvbite38]

Hi,

Can someone please help me with following situation:

I have to sum a range, but the issue is the range will always be variable. see example below:
A B
1 x 1
2 x 1
3 x 1 < here I want a macro to select the range of all the x in col A 4 y 1
5 y 1 < here I want a macro to select the range of all the Y in col A
6 z 1 < here I want a macro to select the range of all the Z in col A
7 p 1 < here I want a macro to select the range of all the p in col A

How can i get macro select the variable range. I dont wanna name the ranges, as there may be 100s of diffirent types of items in column A.

please help

Kind Regards

 

[luvbite38]

Thanks a lot.... I really can't thank you enough.

Hopefully, I just need one last fav, is there a possibility, if I re-run the macro. It deletes the entire process and repeats this macro again. and add a Grand total at the bottom of the last page.

I am truely very thankful.

Regards,

 

[luvbite38]

by the way, you're supplied macro works a treat....this is exactly what I needed.

thanks a million

 

[Peter_SSs]

Hopefully, I just need one last fav, is there a possibility, if I re-run the macro. It deletes the entire process and repeats this macro again. and add a Grand total at the bottom of the last page.

Running the macro again after it has been run will not 'delete the process'. However, if you want the macro changed so that, starting with the original data, it does what it already does plus also adds a Grand Total row then try this version.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> STot()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, st, strw, t, gt<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    st = Array("Sub-Total", 1, "@ Dia 12", 12, "@ Dia 16", 16, _<br>        "@ Dia 20", 20, "@ Dia 24", 24, "@ Dia 28", 28, _<br>        "@ Dia 32", 32, "@ Dia 36", 36, "@ Dia 40", 40)<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> gt(LBound(st) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(st))<br>    <SPAN style="color:#00007F">ReDim</SPAN> t(1 <SPAN style="color:#00007F">To</SPAN> 40) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A4", Range("A" & Rows.Count).End(xlUp)).Resize(, 18)<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _<br>            Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        lr = Range("A" & Rows.Count).End(xlUp).Row<br>        a = .Offset(-.Row + 1).Resize(lr).Value<br>        i = .Row<br>        <SPAN style="color:#00007F">Do</SPAN><br>            i = i + 1<br>            <SPAN style="color:#00007F">If</SPAN> a(i, 3) = "" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">ReDim</SPAN> strw(LBound(st) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(st))<br>                <SPAN style="color:#00007F">For</SPAN> c = <SPAN style="color:#00007F">LBound</SPAN>(st) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(st)<br>                    <SPAN style="color:#00007F">If</SPAN> IsNumeric(st(c)) <SPAN style="color:#00007F">Then</SPAN><br>                        strw(c) = t(st(c))<br>                        gt(c) = gt(c) + t(st(c))<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        strw(c) = st(c)<br>                        gt(c) = st(c)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> c<br>                <SPAN style="color:#00007F">ReDim</SPAN> t(1 <SPAN style="color:#00007F">To</SPAN> 40) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> Cells(i, 1).Resize(, 18)<br>                    .Font.Bold = <SPAN style="color:#00007F">False</SPAN><br>                    .RowHeight = 25<br>                    .Value = strw<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                t(a(i, 3)) = t(a(i, 3)) + a(i, 18)<br>                t(1) = t(1) + a(i, 18)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> i < lr - 1<br>        <SPAN style="color:#00007F">With</SPAN> Cells(lr, 1).Resize(, 18)<br>            .Font.Bold = <SPAN style="color:#00007F">False</SPAN><br>            .RowHeight = 25<br>            .Value = gt<br>            .Cells(1, 1).Value = "Grand Total"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .EntireColumn.AutoFit<br>        .Resize(lr).RemoveSubtotal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[luvbite38]

Thank you so much, I should not ask more. You have already done alot for me...... I am very thankful for all your assistance Peter.

Kindest Regards,

 

[Peter_SSs]

Thank you so much, I should not ask more. You have already done alot for me...... I am very thankful for all your assistance Peter.

Kindest Regards,

Cheers, thanks for the feedback.

 

[luvbite38]

Hi Peter,

I have to make some changes in the procedure, and I was wondering you could help me again please.

Previously, you helped me do the sub and grand totals of each items in Column A.

I want to slightly change this to following:

Add three rows at the end of each groups and in New row 1 show the headings (i.e. For Dia 10, For Dia 12, For Dia 16 to 40 with the increment of 4). In second new row I want the sub-total of all the D500N bars (i.e. in column F) under each Dia and sub-total of all the bars for each category, Similarly third row should sum the Mass of all the R250N bars in Column F under each diameter.

Could you pls write some comments so I can follow it through.4

Kindest Regards,

 

[luvbite38]

In summary everything else is same, but only requires additional work done by a macro.

thanks

 

[Peter_SSs]

I don't understand the new requirements, so I am not sure if I can help.

Could you take the data from my first screen shot in post #10 and manually arrange that how you would like the results and post that screen shot?

Any further explanation that you can give to help describe how the results are obtained would also help.

 

[luvbite38]

Thanks Peter,

I have managed to resolve the issue by another approach. i.e. using the variable ranges.

But thanks a million for helping... :D