Simple Looping Help

[James thunderbolt]

Hi,

I need to learn how to create a simple loop of a macro, ive attached a sheet where there are two buttons that activate a macro, the first one creates a formula (it is just checking if the next cell is in ascending order) & the 2nd button deletes all those that have failed.

I would like a macro that loops between the 2 until the count of failed = 0 (failed numbers are iterated with an "x" in column B)

Greatly appreciate your guidance

 

[shrivallabha]

One question,
will there be same number in consecutive cells? If yes, what needs to be done?

 

[James thunderbolt]

The test macro already decides that if there are consecutive cells of the same number, it will delete the 1st one.

Thanks

 

[shrivallabha]

Modify the RunTest macro as below and verify results once.

Sub RunTest()
Dim i As Long, j As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 7 To LastRow
    For j = i + 1 To LastRow
        If Cells(i, "A").Value > Cells(j, "A").Value Then
            Cells(i, "B").Value = "X": Exit For
        End If
    Next
Next i
End Sub

 

[NARAYANK991]

Hi James ,

Why not combine both the macros in one , as follows :

Sub RunTest1()
    Dim LastCell As Long
    LastCell = Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row

    i = LastCell
    Minvalue = Sheets("Test").Range("A" & i).Value
    Do
      i = i - 1
      If Sheets("Test").Range("A" & i).Value < Minvalue Then
          Minvalue = Sheets("Test").Range("A" & i).Value
      Else
          Sheets("Test").Range("A" & i).EntireRow.Delete
      End If
    Loop While i > 6
End Sub

Narayan

 

[YasserKhalil]

Try this code based on Mr. Narayan's code

Sub Test()
    Dim I          As Long
    Dim minValue    As Variant
    Dim Rng        As Range
   
    With Sheets("Test")
        I = .Cells(Rows.Count, "A").End(xlUp).Row
        minValue = .Range("A" & I).Value
       
        Do
            I = I - 1
           
            If .Range("A" & I).Value < minValue Then
                minValue = .Range("A" & I).Value
            Else
                If Rng Is Nothing Then
                    Set Rng = .Range("A" & I)
                Else
                    Set Rng = Union(.Range("A" & I), Rng)
                End If
            End If
        Loop While I > 7
        Rng.EntireRow.Delete
    End With
End Sub

 

[James thunderbolt]

Thanks guys

These are great, @YasserKhalil - your code is very quick but not that much different to Narayan's, wondering what it is that is making it run so much faster ???

 

[NARAYANK991]

Hi ,

The reason is that the deletion takes place only once ; all the rows that need to be deleted are collected together , using the Union keyword , and deleted.

Doing anything on one element at a time is bound to be time-consuming ; deleting rows one by one is one such action.

Narayan