Delete Rows and Sort

[csch123]

I have a repetitive task at work that needs to be done, and I was hoping that there was a way to build a macro to take care of this. I am attaching a spreadsheet to give you an idea what I need to do.

I have a set of data that always begins on cell B6. The data set can extend for over 1000 rows, with a code beginning with 3L at the bottom (3LV115 in this example). I've been asked to sort the data set by column B (excluding the 3L code from my sort so it stays at the bottom), and then delete any rows that have a cell in column B that starts with the letter m. For this example, I would need to remove m1, m2, m3, and m5. Once these are deleted, I have to sort the data again by column I. Once sorted, I need to delete any rows that have a value of 0 in column I, again excluding the row that has a cell in column B that begins with 3L.

Summarized:
1. Sort data by column B, excluding 3L cell.
2. Delete rows with cells beginning with M in column B.
3. Sort data again by column I, excluding 3L cell.
4. Delete rows with value of 0 in column I, excluding 3L cell.

The 3L code can have longer extensions on it and changes daily, but those two letters always stay constant. Is there a solution to this?

[patel45]

can you have many rows starting with 3L ?

[csch123]

No. The 3L code only shows up once. I should also mention that the sort functions need to start at row 6. There are other headers above that row.

[patel45]

is the row starting with 3L always the last row ?

[csch123]

Yes. That is always the last row.

[patel45]

Please Login or Register  to view this content.

[csch123]

Thank you! It almost works perfectly. It doesn't delete all rows that have a value starting with M. It deletes most, but not all. I am trying to see why that may be.

[csch123]

I figured out that the code doesn't delete any rows with M if that specific row also has a value in column I that is > or < 0. So for instance:

M1 = 1
M2 = 2
M3 = 0
M4 = 0

It would only delete the rows where a value in I is equal to 0 (M3 & M4).

[patel45]

attach your current file with the problems highlighted

[hotrod4571]

Sorry for jumping in but I have have a macro that populates a "repair" sheet. I do this monthly. The problem is that is duplicates entries. Can I alter my macro to keep duplicate entries being made, or maybe a macro that can sort, and delete duplicate rows. I'll paste that right to the "view Code" section of the repair page thanks

Sub mtest()
Dim rng As Range
Dim xRow As Long
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String

QuestionToMessageBox = "Have you entered all of the monthly mileage?"

YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Mileage ledger")

If YesOrNoAnswerToMessageBox = vbNo Then
MsgBox "Please enter all of the monthly mileage before clicking on this button!"
Else

Sheet1.Unprotect Password:="A203"
Sheet2.Unprotect Password:="A203"
Sheet3.Unprotect Password:="A203"
Sheet4.Unprotect Password:="A203"
Sheet5.Unprotect Password:="A203"
Sheet6.Unprotect Password:="A203"
Sheet7.Unprotect Password:="A203"
Sheet8.Unprotect Password:="A203"
Sheet9.Unprotect Password:="A203"
Sheet10.Unprotect Password:="A203"
Sheet11.Unprotect Password:="A203"
Sheet12.Unprotect Password:="A203"
Sheet13.Unprotect Password:="A203"
Sheet14.Unprotect Password:="A203"
Sheet15.Unprotect Password:="A203"

With Sheets("A010")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Copy Destination:=Sheets("Repair").Range("A1")
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A030")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A040")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A050")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A090")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A100")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A140")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("A310")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("TPU")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("CIO")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
End With
Set rng = Nothing
With Sheets("BVHQ")
xRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:p" & xRow)
With rng
.AutoFilter Field:=14, Criteria1:="MVA", Operator:=xlOr, Criteria2:="Body Shop"
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
rng.Offset(1, 0).Copy Destination:=Sheets("Repair").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
.Rows.AutoFilter
End With
Sheet1.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet2.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet3.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet4.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet5.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet6.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet7.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet8.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet9.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet10.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet11.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet12.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet13.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet14.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
Sheet15.Protect Password:="A203", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True
End With
MsgBox ("You have sucessfully updated the Repair ledger.")
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then Cells(Target.Row, 15) = Now()
End Sub

[csch123]

I've attached the excel file with your code in place. Running the macro seems to keep any row with a value in cell B starting with M and a variance in column I of < or > 0.

[csch123]

Patel45,

Did you find out what the possible cause of that issue would be?

[patel45]

change this line
If Left(Cells(j, 2).Text, 1) = "m" Then Rows(j).Delete
to
If Left(Cells(j, 2).Text, 1) = "M" Then Rows(j).Delete