# Adding filtered column cells • ### Question

• Hi all, I have the following problem:
On a sheet of an excel workbook filter the "A" column and select only the cells that have the value 5406, I need to do a subtraction of the second cell minus the first cell filtered, the cells need to subtract are located in the "B ", I hope you can help me, greetings
Wednesday, October 1, 2014 11:57 PM

• In Cell C2 you could have entered a formula like

`=IF(A2=5406,B2-A2,"")`

This would subtract the value of A2 from B2 and stick that in Column C if you have dragged the formula down.

If you needed a macro to work on this you could consider something like this

```Sub Main()

'populate column A with random value
[A2] = 5406
[A3] = 9999
[A4] = 12
[A5] = 5406
[A6] = 19
[A7] = 5406
[A8] = 999
[A9] = 123

' constant value for column B
[B2:B9] = 55

SubTract

End Sub

Sub SubTract()
Application.ScreenUpdating = False

Dim col As Range, cell As Range
Set col = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

col.AutoFilter Field:=1, Criteria1:=5406

For Each cell In col.Offset(1, 0).SpecialCells(xlCellTypeVisible)
cell.Offset(0, 2) = cell.Offset(0, 1) - cell
Next

Sheet1.AutoFilterMode = False
End Sub```

• Marked as answer by Thursday, October 2, 2014 3:22 PM
Thursday, October 2, 2014 10:44 AM

### All replies

• In Cell C2 you could have entered a formula like

`=IF(A2=5406,B2-A2,"")`

This would subtract the value of A2 from B2 and stick that in Column C if you have dragged the formula down.

If you needed a macro to work on this you could consider something like this

```Sub Main()

'populate column A with random value
[A2] = 5406
[A3] = 9999
[A4] = 12
[A5] = 5406
[A6] = 19
[A7] = 5406
[A8] = 999
[A9] = 123

' constant value for column B
[B2:B9] = 55

SubTract

End Sub

Sub SubTract()
Application.ScreenUpdating = False

Dim col As Range, cell As Range
Set col = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

col.AutoFilter Field:=1, Criteria1:=5406

For Each cell In col.Offset(1, 0).SpecialCells(xlCellTypeVisible)
cell.Offset(0, 2) = cell.Offset(0, 1) - cell
Next

Sheet1.AutoFilterMode = False
End Sub```

• Marked as answer by Thursday, October 2, 2014 3:22 PM
Thursday, October 2, 2014 10:44 AM
• thank you very much is just what I needed, Greetings
Thursday, October 2, 2014 3:23 PM