Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 1) <> ""
j = i + 1
Do While Cells(j, 1) <> ""
If Cells(i, 1) + Cells(j, 1) = 0 Then
Cells(j, 1).EntireRow.Delete
Cells(i, 1).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
i = i + 1
Loop
End Sub
Regards
Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "))") Then
j = i + 1
Do While Cells(j, 6) <> ""
If Cells(i, 6) + Cells(j, 6) = 0 Then
Cells(j, 6).EntireRow.Delete
Cells(i, 6).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
End If
i = i + 1
Loop
End Sub
Sub DelSumZero()
Application.ScreenUpdating = False
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "),--(C2:C1000000=" & Cells(i, 3).Address(False, False) & "),--(G2:G1000000=" & Cells(i, 7).Address(False, False) & "))") Then
Debug.Print i
j = i + 1
Do While Cells(j, 6) <> "" And Cells(i, 7) = Cells(j, 7)
If Cells(i, 6) + Cells(j, 6) = 0 And Cells(i, 3) = Cells(j, 3) Then
Cells(j, 6).EntireRow.Delete
Cells(i, 6).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
End If
i = i + 1
Loop
End Sub
Sub Q_28959762()
Dim wks As Worksheet, wksCrit As Worksheet
Set wks = Worksheets("Sheet1")
Application.ScreenUpdating = False
wks.Range("K1:M1").Value = Array("ABS", "EqNext", "EqPrev")
wks.Range("K2").Formula = "=ABS(F2)"
wks.Range("K2").AutoFill wks.Range(wks.Range("K2"), wks.Range("J2").End(xlDown).Offset(0, 1))
wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value = wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value
wks.Range("K2").CurrentRegion.Sort key1:=wks.Range("K1"), header:=xlYes
wks.Range("L2").Formula = "=K2=K3"
wks.Range("M2").Formula = "=K2=K1"
wks.Range("L2:M2").AutoFill wks.Range(wks.Range("L2:M2"), wks.Range("K2").End(xlDown).Offset(0, 1))
wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value = wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value
Set wksCrit = Worksheets("Sheet2")
wksCrit.Range("A1:B1").Value = Array("EqNext", "EqPrev")
wksCrit.Range("A2").Value = True
wksCrit.Range("B3").Value = True
wks.Range("K1").CurrentRegion.AdvancedFilter xlFilterInPlace, wksCrit.Range("A1:B3")
wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
wks.ShowAllData
wks.Range("K:M").Delete
wks.Range("A1").CurrentRegion.Sort key1:=wks.Range("A1"), header:=xlYes
wksCrit.Range("A1:B3").Clear
Application.ScreenUpdating = True
End Sub
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
How to transfer values from one userform in excel to another | 14 | 43 | |
Custom fill series | 12 | 38 | |
Problem to macro | 5 | 21 | |
rename csv files in a folder | 3 | 17 |
Join the community of 500,000 technology professionals and ask your questions.