CODEWITHSUNDEEP
excelconditional-statementsvba
user2501464
user2501464

Reputation: 13

Excel VBA conditional delete macro issue

I have a worksheet that contains some merged data from two different sources. There is one common/shared column - time/date.

Columns B-E contain data when columns F-G do not, and vice versa.

What I want to do, is go down column F, when I find a value, I want to go to column E and work up until I find a value. At that point, I want to check its value - if it is less than 4, then I want to delete the row that originally triggered the column E lookup.

Then, continue going down. I probably will need to do this in reverse (starting at the bottom, and working my way up) due to things I've found in the past, but am not sure yet.

So, my code that I'm working on right now is as follows - it doesn't work correctly, and I'm trying to troubleshoot it to make it work correctly, but am having difficulty. Any information/advice/help you could provide would be greatly appreciated.

 Set myrange = Sheets("Test Sheet").Range("F2", Range("F" & Rows.Count).End(xlUp))
    For Each mycell In myrange
        rrow = rrow + 1
            If IsEmpty(mycell.Value) = False Then
                For j = rrow To 0 Step -1
                    If IsEmpty(mycell.Offset(j, -1)) = False And mycell.Cells(j, -1).Value < 4 Then
                    mycell.Cells(rrow, -1).EntireRow.Delete
                    GoTo line
                    Else

                    End If
                Next j
line:

        Else





        End If
    Next mycell

Upvotes: 0

Views: 245

Answers (1)

John Bustos
John Bustos

Reputation: 19574

Try this out:

  Sub DeleteRows()

  Dim ColFRow As Long
  Dim ColERow As Long
  Dim ToDelete As Range

     For ColFRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        If (Not IsEmpty(Cells(ColFRow, 6).Value)) Then
           For ColERow = ColFRow To 0 Step -1
              If (Not IsEmpty(Cells(ColERow, 5).Value)) Then
                 If Cells(ColERow, 5).Value > 4 Then
                    If ToDelete Is Nothing Then
                       Set ToDelete = Cells(ColFRow, 1).EntireRow
                    Else
                       Set ToDelete = Union(ToDelete, Cells(ColFRow, 1).EntireRow)
                    End If
              End If
              Exit For
           End If
           Next ColERow
        End If
     Next ColFRow


     ToDelete.Delete


  End Sub

Upvotes: 1

Related Questions