Delete Rows

[srosk]

Some rows will have a value in column AG equal to 1. I coded my macro to sort by value so all rows will be on top where AG = 1.

I am struggling to find code that will find the last row where AG = 1, and then delete all rows below. Can anyone help? Thanks!

 

[Logit]

.

Sub DeleteMyRows()


Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long
Dim trm As String


lr = Range("AG" & Rows.Count).End(xlUp).Row
trm = 1


 For i = Rows.Count To 2 Step -1
    
     If Range("AG" & i) = trm Then
        Range("AG" & i).EntireRow.Delete
     End If


Next i




End Sub

 

[srosk]

Thanks. I had something before, that if AG <> 1, then delete row. But I was talking with someone and they said the loop would provide inconsistent results.

Do you mind giving me a cliff notes of what this is actually doing? I see where Rows.Count counts the number of rows in the worksheet, but... I am somewhat lost by the logic you have written. Trying to learn as I go

.

Sub DeleteMyRows()


Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long
Dim trm As String


lr = Range("AG" & Rows.Count).End(xlUp).Row
trm = 1


 For i = Rows.Count To 2 Step -1
    
     If Range("AG" & i) = trm Then
        Range("AG" & i).EntireRow.Delete
     End If


Next i




End Sub

 

[Logit]

.

Option Explicit


Sub DeleteMyRows()




Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long
Dim trm As String




lr = Range("AG" & Rows.Count).End(xlUp).Row     'finds the last row in column AG
trm = 1




 For i = Rows.Count To 2 Step -1                'Goes to the bottom of the used rows in AG and
                                                'tests each row, going up, one row at a time. That's the
                                                'Step -1.  It stops at Row [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]  ... Rows.Count to 2
                                                'last row to row [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] .
                                                
     If Range("AG" & i) = trm Then              'Looking for "trm" which was set up above as the number 1
                                                'If "trm" is located in Col AG then
        Range("AG" & i).EntireRow.Delete        'Delete that entire row where 1 is found. The variable " i "
                                                'is the row
     End If


Next i                                          'This searching starts with "For i = ..." above goes thru
                                                'the code looking at the last used row, then "Next i" tells it
                                                'to go back up and start again at "For i = ..." but this time
                                                'it will be one row less or towards the top "Step - 1"


End Sub

 

[vcoolio]

Hello Srosk,

If I've understood your query correctly, you would like any row that does not have a value of 1 in Column AG deleted. If so, then the following code using autofilter may help:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
       .AutoFilter 33, "<>" & 1
       .Offset(1).EntireRow.Delete
       .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

You won't need to sort your data using this code but sorting may speed things up somewhat for you.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Logit]

.
vcoolio ... tks for pointing out what should have been obvious. Sometimes I read a question and it is turned around in my head. Go figure ...

Option Explicit


Sub RwDel()


Dim x As Long, trm As String


trm = 1


With Sheets("Sheet1")    '<--- edit Sheet as required
    For x = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Not Rows(x).Find(trm) Is Nothing Then Rows(x).Delete
    Next x
End With


End Sub

 

[vcoolio]

Hi Logit,

I have many a day like that!

Cheerio,
vcoolio.