Reconciliation
 

Hi,

I would appreciate any suggestions how to create a macro that delete
the rows with the same value in column A and the sum of values i
column B equal to zero.

Example:
Column A Column B
AAA 50
BBB 100
AAA -50

I was thinking of using the worksheet function SumIf, but am not sur
how exactly to do that.

Thanks.
Eduar

--
Eduar
-----------------------------------------------------------------------
Eduard's Profile: http://www.excelforum.com/member.php...fo&userid=2680
View this thread: http://www.excelforum.com/showthread.php?threadid=40057

Reconciliation
 

I think I'd use another column that contained that =sumif() formula:

=sumif(a:a,a1,b:b)
and drag down.

Then apply data|Filter|autofilter to that helper column and filter to show 0's.

Then delete the visible rows.

If I needed a macro, I'd record it when I did it manually.

Eduard wrote:

Hi,

I would appreciate any suggestions how to create a macro that deletes
the rows with the same value in column A and the sum of values in
column B equal to zero.

Example:
Column A Column B
AAA 50
BBB 100
AAA -50

I was thinking of using the worksheet function SumIf, but am not sure
how exactly to do that.

Thanks.
Eduard

--
Eduard
------------------------------------------------------------------------
Eduard's Profile: http://www.excelforum.com/member.php...o&userid=26802
View this thread: http://www.excelforum.com/showthread...hreadid=400571

--

Dave Peterson

Reconciliation
 

Another way as a macro:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim delRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
If Application.SumIf(.Range("a:a"), myCell.Value, _
.Range("b:b")) = 0 Then
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
Next myCell
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If

End Sub

Eduard wrote:

Hi,

I would appreciate any suggestions how to create a macro that deletes
the rows with the same value in column A and the sum of values in
column B equal to zero.

Example:
Column A Column B
AAA 50
BBB 100
AAA -50

I was thinking of using the worksheet function SumIf, but am not sure
how exactly to do that.

Thanks.
Eduard

--
Eduard
------------------------------------------------------------------------
Eduard's Profile: http://www.excelforum.com/member.php...o&userid=26802
View this thread: http://www.excelforum.com/showthread...hreadid=400571

--

Dave Peterson

Reconciliation
 

Thanks, Dave.

I'v tried the first option and it works as I expected.

Eduard


--
Eduard
------------------------------------------------------------------------
Eduard's Profile: http://www.excelforum.com/member.php...o&userid=26802
View this thread: http://www.excelforum.com/showthread...hreadid=400571