LinkBack URL
About LinkBacks
I used this code to delete anything in column A that ends in Z, 1 and all consecutive numbers except for the largest number. It works when I use it on a data set of one or two weeks but when I start running this macro on a whole year's worth of data I get an error. The error is highlighted below as such:
'Delete Z and 1, consecutive number --> keep only the largest
Dim LR As Long, LR1 As Long
Dim Rng As Range, cel As Range, i As Long
Application.ScreenUpdating = False
With Sheets("5data") '<----Change Sheet Name
LR = .Range("A" & .Rows.Count).End(xlUp).Row
LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Range(.Cells(LR + 1, "A"), .Cells(LR1, "A")).EntireRow.Delete
Set Rng = Range("B2:B" & LR)
With Rng
For i = LR To 1 Step -1
If Right(Rng(i).Value, 1) = "Z" Or Right(Rng(i).Value, 1) = "1" Then
Rng(i).EntireRow.Delete
End If
Next
End With
End With
With Sheets("5data") '<----Change Sheet Name
LR = .Range("A" & .Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & LR)
With Rng
For i = LR To 1 Step -1
If Val(Rng(i).Offset(-1, 0).Value) = Val(Rng(i).Value - 1) Then
Rng(i).Offset(-1, 0).EntireRow.Delete
End If
Next
End With
End With
Application.ScreenUpdating = True
Register To Reply
Bookmarks