decreasing list of many customers contain zero and precede it

alhagag · May 18th 2021

hi expert

I need efficient way to decrease the list of many customers which contain value is zero and any value precede zero so every time it the balance reaches to zero when pay the debit and again the balance become not zero so what I want ignore the earlier balances which zero and relating of it as you see in sheet "account" highlighted by yellow color should ignore them and I expected result in sheet result it depends on COL E

which value contains zero and any value precede zero should ignore it , and I have this macro but it only ignores the row contains zero in COL E

I know this is complicated request , any suggestion do that automatically, please ?

Code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "B1" Then Exit Sub

Dim Rg As Range
Set Rg = [result!G1:H2]

Rg = Application.Index(Array(Array("account2", "balance"), Array([B1], "<>0")), 0, 0)
[A5].CurrentRegion.AdvancedFilter 2, Rg, [result!A6:H6]
Rg.ClearContents

With Sheets("result")
   .[A2:D3] = [account!C2:F3].Value
   .[B1] = Target.Value
End With

End Sub

Display More

Justin Doward · May 19th 2021

Hi Alhagag, you can try this:

Code

Sub After0()
Dim lstrw As Long, x As Long
Dim ws As Worksheet: Set ws = Worksheets("account")
Dim ws2 As Worksheet: Set ws2 = Worksheets("result")
Dim MyArr As Variant
lstrw = ws.Cells(Rows.Count, "E").End(xlUp).Row
For x = lstrw To 6 Step -1
    If ws.Cells(x, "E").Value2 = 0 Then
        lstrw = x + 1
        MyArr = ws.Range("A" & lstrw & ":H" & ws.Cells(Rows.Count, "H").End(xlUp).Row)
        ws2.Range("A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(UBound(MyArr, 1), UBound(MyArr, 2)) = MyArr
        GoTo MyExit
    End If
Next x
MyExit:
End Sub

Display More

It works on the example you sent.

alhagag · May 19th 2021

thanks

you seem misunderstood but not show as I put in expected result in sheet result should show all data for any customer with formatting and borders there are some data are missed , it should show all the data with formatting and borders, topics .. etc. I don't write any thing in sheet result please see the result in sheet result

decreasing list of many customers contain zero and precede it

Participate now!

Share