If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Excel Macro to Filter and find sum of a variable column

Thread starter dranaba
Start date Jan 29, 2013
Tags

autofilter autosum endrow macro

D

dranaba

New Member

Jan 29, 2013

#1

HI,

See the table given below.

 [TABLE="class: MsoNormalTable, width: 144"]
<tbody>[TR]
[TD="width: 64"]

Item

[/TD]
[TD="width: 64"]

Qty

[/TD]
[TD="width: 64"]

Total Value

[/TD]
[/TR]
[TR]
[TD="width: 64"]

A

[/TD]
[TD="width: 64"]

-6

[/TD]
[TD="width: 64"]

-3.87

[/TD]
[/TR]
[TR]
[TD="width: 64"]

B

[/TD]
[TD="width: 64"]

-12

[/TD]
[TD="width: 64"]

-17.1

[/TD]
[/TR]
[TR]
[TD="width: 64"]

C

[/TD]
[TD="width: 64"]

-1

[/TD]
[TD="width: 64"]

-19.5

[/TD]
[/TR]
[TR]
[TD="width: 64"]

D

[/TD]
[TD="width: 64"]

-1

[/TD]
[TD="width: 64"]

-9.46

[/TD]
[/TR]
[TR]
[TD="width: 64"]

E

[/TD]
[TD="width: 64"]

2

[/TD]
[TD="width: 64"]

5.7

[/TD]
[/TR]
[TR]
[TD="width: 64"]

F

[/TD]
[TD="width: 64"]

1

[/TD]
[TD="width: 64"]

13.6

[/TD]
[/TR]
</tbody>[/TABLE]

My target was to get the SUM of the quantities(QTY) and "Total Value" with a negative value by using a macro and auto filter.The sums will be displayed in the bottom of each column.Which i succeeded. Please advice how i can use the same macro with a table with more rows for an example with 50 rows of data. See below code for the macro. Please advice. Thank you

Sub Macro16()
'
' Macro16 Macro
'

'
Range("B1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$7").AutoFilter Field:=2, Criteria1:="<0", _
Operator:=xlAnd
Range("B2:B8").Select
Range("B8").Activate
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-6]C:R[-3]C)"
Range("C2:C8").Select
Range("C8").Activate
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-6]C:R[-3]C)"
Range("E2").Select
End Sub

Excel Facts

Shade all formula cells

Click here to reveal answer

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Sort by date Sort by votes

MrVillareal

Well-known Member

Jan 29, 2013

#2

dranaba said:
HI,

See the table given below.

 [TABLE="class: MsoNormalTable, width: 144"]
<tbody>[TR]
[TD="width: 64"]
Item
[/TD]
[TD="width: 64"]
Qty
[/TD]
[TD="width: 64"]
Total Value
[/TD]
[/TR]
[TR]
[TD="width: 64"]
A
[/TD]
[TD="width: 64"]
-6
[/TD]
[TD="width: 64"]
-3.87
[/TD]
[/TR]
[TR]
[TD="width: 64"]
B
[/TD]
[TD="width: 64"]
-12
[/TD]
[TD="width: 64"]
-17.1

[/TD]
[/TR]
[TR]
[TD="width: 64"]
C
[/TD]
[TD="width: 64"]
-1
[/TD]
[TD="width: 64"]
-19.5
[/TD]
[/TR]
[TR]
[TD="width: 64"]
D
[/TD]
[TD="width: 64"]
-1
[/TD]
[TD="width: 64"]
-9.46
[/TD]
[/TR]
[TR]
[TD="width: 64"]
E
[/TD]
[TD="width: 64"]
2
[/TD]
[TD="width: 64"]
5.7
[/TD]
[/TR]
[TR]
[TD="width: 64"]
F
[/TD]
[TD="width: 64"]
1
[/TD]
[TD="width: 64"]
13.6

[/TD]
[/TR]
</tbody>[/TABLE]

My target was to get the SUM of the quantities(QTY) and "Total Value" with a negative value by using a macro and auto filter.The sums will be displayed in the bottom of each column.Which i succeeded. Please advice how i can use the same macro with a table with more rows for an example with 50 rows of data. See below code for the macro. Please advice. Thank you

Sub Macro16()
'
' Macro16 Macro
'

'
Range("B1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$7").AutoFilter Field:=2, Criteria1:="<0", _
Operator:=xlAnd
Range("B2:B8").Select
Range("B8").Activate
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-6]C:R[-3]C)"
Range("C2:C8").Select
Range("C8").Activate
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-6]C:R[-3]C)"
Range("E2").Select
End Sub

try this:

Range("B1").Select
Selection.AutoFilter

Dim FinalRow As Integer
FinalRow = Cells(Rows.Count, "C").End(xlUp).Row

ActiveSheet.Range("$A$1:C" & FinalRow).AutoFilter Field:=2, Criteria1:="<0", _
Operator:=xlAnd

Cells(FinalRow + 1, 2).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"
Cells(FinalRow + 1, 3).FormulaR1C1 = "=SUBTOTAL(9,R2C3:R[-1]C)"

Upvote 0

D

dranaba

New Member

Jan 30, 2013

#3

HI Mr Villareal , Thank you for the reply and unfortunately it doesn't work and delete the data on two bottom rows.

Upvote 0

MrVillareal

Well-known Member

Jan 30, 2013

#4

dranaba said:
HI Mr Villareal , Thank you for the reply and unfortunately it doesn't work and delete the data on two bottom rows.

how about this:

Range("B1").Select
Selection.AutoFilter

Dim FinalRow As Integer
FinalRow = Cells(Rows.Count, "C").End(xlUp).Row

ActiveSheet.Range("$A$1:C" & FinalRow).AutoFilter Field:=2, Criteria1:="<0", _
Operator:=xlAnd

Cells(FinalRow + 1, 2).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"
Cells(FinalRow + 1, 3).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"

Upvote 0

You must log in or register to reply here.

Similar threads

A

Question

Auto Update Sheets in Scheduler file

Replies: 6

Views: 531

Feb 17, 2024

anuradhagrewal

A

S

Question

VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns

Replies: 1

Views: 106

Oct 9, 2024

sijpie

N

Solved

Excel 2016 VBA - Pull data from multiple sheets to One with specificities

Replies: 3

Views: 290

Sep 26, 2024

mumps

Solved

Make VBA codes according to one specific sheet and not all entire workbook?

2

Replies: 11

Views: 344

Oct 9, 2024

Lacan

O

Question

Change MS Word Doc Properties via MS Excel VBA Macro

Replies: 3

Views: 630

May 6, 2024

NdNoviceHlp

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,223,723

Messages: 6,174,108

Members: 452,544

Latest member: aush

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top