Link to home
Create AccountLog in
Avatar of varesources
varesourcesFlag for Afghanistan

asked on

How do I make a macro that deletes rows based on a cell value?

I have data in an Excel sheet that looks like the following:

2       10        20  <
1       30        30  <-----------These are one group and would be deleted
                     50  <
Empty Row
1        50       50  <
3        15       45  <-----------These are one group and would be deleted
                     95  <
Empty Row
2         20       40   <
3         50       150 <----------These are one group and would not be deleted
4         5         20   <
                      210
Empty Row

I need a macro that will delete all the rows of data where the number in bottom right cell is lower than 100 but leave the rows where the bottom right number is 100 or above. There are more columns than show here but to make it simple they are not shown. The total value on my sheet is row Y.

                   
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

You example is a bit unclear to me.  What do you mean when you say bottom right?  Also confused about row y and it's significance...
Avatar of wchh
wchh

One option
Sub DeleteGroup()
    Dim i As Long
    Dim j As Long
    j = 1
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
     If ActiveSheet.Cells(i, 1).Value = "" Then
        If ActiveSheet.Cells(i, 3).Value <> "" Then
            If ActiveSheet.Cells(i, 3).Value < 100 Then
                Rows(j & ":" & i).Select
                Selection.Delete Shift:=xlUp
                i = i - (i - j)
            End If
        Else
            j = i
        End If
     End If
    Next i
End Sub

Open in new window

Avatar of varesources

ASKER

jocasio123, The bottom right = 50, 95, and 210. Thos numbers are in the y column.

wchh, trying it now. Thanks!
Changed 3 to 25 for y column
Sub DeleteGroup()
    Dim i As Long
    Dim j As Long
    j = 1
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
     If ActiveSheet.Cells(i, 1).Value = "" Then
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Y Column
            If ActiveSheet.Cells(i, 25).Value < 100 Then 'Y column
                Rows(j & ":" & i).Select
                Selection.Delete Shift:=xlUp
                i = i - (i - j)
            End If
        Else
            j = i
        End If
     End If
    Next i
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of wchh
wchh

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you, seems to be just what I need. Double checking the results.
Fast response and perfect answer. Thank you!