Need to show Sub Total value after auto filter.

faizan haq

New Member
Joined
Jan 14, 2020
Messages
12
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. MacOS
Hi,

i am using below VBA code but i need to add show Subtotal value in column "K" after auto filter value "B", please advice


ABCDEFGHIJKLMNO
filter valueSO#D#ITEMDESCSTYLECODEARTICLEREGIONSIZEC.SIZETotalARTWORKPAGESCODEBATCH#
1​
GP043697
41816016​
504772​
62759440​
S2015MGFXYB04FM4476CHINAA128A128
58​
GP043697
6​
P517838BBATCH#72281
2​
GP043697
41816016​
504772​
62759440​
S2015MGFXYB04FM4476CHINAA140A140
295​
GP043697
6​
P517838BBATCH#72281
3​
GP043697
41816016​
504772​
62759440​
S2015MGFXYB04FM4476CHINAA152A152
792​
GP043697
6​
P517838BBATCH#72281
4​
GP043697
41816016​
504772​
62759440​
S2015MGFXYB04FM4476CHINAA164A164
627​
GP043697
6​
P517838BBATCH#72281
5​
GP043697
41816016​
504772​
62759440​
S2015MGFXYB04FM4476CHINAA176A176
268​
GP043697
6​
P517838BBATCH#72281
6​
GP043698
41816016​
504772​
62759440​
F20FTBGFX504GE4647OTHERSA42A/S
65​
GP043698
6​
P517838BBATCH#72281
7​
GP043698
41816016​
504772​
62759440​
F20FTBGFX504GE4647OTHERSA46A/M
123​
GP043698
6​
P517838BBATCH#72281
8​
GP043698
41816016​
504772​
62759440​
F20FTBGFX504GE4647OTHERSA50A/L
110​
GP043698
6​
P517838BBATCH#72281
9​
GP043698
41816016​
504772​
62759440​
F20FTBGFX504GE4647OTHERSA54A/XL
58​
GP043698
6​
P517838BBATCH#72281
10​
GP043698
41816016​
504772​
62759440​
F20FTBGFX504GE4647OTHERSA58A/2XL
23​
GP043698
6​
P517838BBATCH#72281
11​
GP043699
41816016​
504772​
62759440​
F20FTBGFX504GE4646OTHERSA38A/XS
4​
GP043699
6​
P517838BBATCH#72281
12​
GP043699
41816016​
504772​
62759440​
F20FTBGFX504GE4646OTHERSA42A/S
85​
GP043699
6​
P517838BBATCH#72281
13​
GP043699
41816016​
504772​
62759440​
F20FTBGFX504GE4646OTHERSA46A/M
152​
GP043699
6​
P517838BBATCH#72281
14​
GP043699
41816016​
504772​
62759440​
F20FTBGFX504GE4646OTHERSA50A/L
131​
GP043699
6​
P517838BBATCH#72281
15​
GP043699
41816016​
504772​
62759440​
F20FTBGFX504GE4646OTHERSA54A/XL
72​
GP043699
6​
P517838BBATCH#72281
Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Application.ScreenUpdating = False
' *** Change Sheet name to suit ***
Set Sh = Worksheets("Sheet1")
Set Rng = Sh.Range("B13:B100" & Sh.Range("A12:A1").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("B12:B100" & Sh.Range("A12:A1").End(xlUp).Row)
For Each Item In List
Rng.AutoFilter Field:=1, Criteria1:=Item
Sh.PrintOut
Rng.AutoFilter
Next Item
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Like this, adjusted for your row numbers??

=SUBTOTAL(109,K2:K16)
 
Upvote 0
Not working, can you adjust and share with me. thanks.
 
Upvote 0
Not working
That doesn't give us much to go on. ;)
In what way is it not working?

Perhaps I have not understood what you want.

Here is my sample sheet with no filter applied and the formula I suggested in K17

A_MrExcel.xlsm
ABCDEFGHIJKLMNO
1filter valueSO#D#ITEMDESCSTYLECODEARTICLEREGIONSIZEC.SIZETotalARTWORKPAGESCODEBATCH#
21GP0436974181601650477262759440S2015MGFXYB04FM4476CHINAA128A12858GP0436976P517838BBATCH#72281
32GP0436974181601650477262759440S2015MGFXYB04FM4476CHINAA140A140295GP0436976P517838BBATCH#72281
43GP0436974181601650477262759440S2015MGFXYB04FM4476CHINAA152A152792GP0436976P517838BBATCH#72281
54GP0436974181601650477262759440S2015MGFXYB04FM4476CHINAA164A164627GP0436976P517838BBATCH#72281
65GP0436974181601650477262759440S2015MGFXYB04FM4476CHINAA176A176268GP0436976P517838BBATCH#72281
76GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA42A/S65GP0436986P517838BBATCH#72281
87GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA46A/M123GP0436986P517838BBATCH#72281
98GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA50A/L110GP0436986P517838BBATCH#72281
109GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA54A/XL58GP0436986P517838BBATCH#72281
1110GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA58A/2XL23GP0436986P517838BBATCH#72281
1211GP0436994181601650477262759440F20FTBGFX504GE4646OTHERSA38A/XS4GP0436996P517838BBATCH#72281
1312GP0436994181601650477262759440F20FTBGFX504GE4646OTHERSA42A/S85GP0436996P517838BBATCH#72281
1413GP0436994181601650477262759440F20FTBGFX504GE4646OTHERSA46A/M152GP0436996P517838BBATCH#72281
1514GP0436994181601650477262759440F20FTBGFX504GE4646OTHERSA50A/L131GP0436996P517838BBATCH#72281
1615GP0436994181601650477262759440F20FTBGFX504GE4646OTHERSA54A/XL72GP0436996P517838BBATCH#72281
172863
Subtotal
Cell Formulas
RangeFormula
K17K17=SUBTOTAL(109,K2:K16)



Now if I filter column B for the value GP043698 I get this.

A_MrExcel.xlsm
ABCDEFGHIJKLMNO
1filter valueSO#D#ITEMDESCSTYLECODEARTICLEREGIONSIZEC.SIZETotalARTWORKPAGESCODEBATCH#
76GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA42A/S65GP0436986P517838BBATCH#72281
87GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA46A/M123GP0436986P517838BBATCH#72281
98GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA50A/L110GP0436986P517838BBATCH#72281
109GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA54A/XL58GP0436986P517838BBATCH#72281
1110GP0436984181601650477262759440F20FTBGFX504GE4647OTHERSA58A/2XL23GP0436986P517838BBATCH#72281
17379
Subtotal
Cell Formulas
RangeFormula
K17K17=SUBTOTAL(109,K2:K16)


What result(s) do you expect after this filter?
 
Upvote 0
i need this result in VBA code after auto filter, please check my first mail with VBA code.
 
Upvote 0
i need this result in VBA code after auto filter,
If you put it in before the first AutoFilter then it will still be there after each AutoFilter. You could record a macro of putting it in manually and then you could use that recorded code near the start of your macro. If you need to remove it again after the last AutoFilter and print is done then also record that macro to get the code.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top