Clear certain cells

[JOEE1979]

Hello everyone

UNIT	Mechanic	W/O #	Activity	Classification	Status
1212	Anthony Ayou	4525	boot	BDN	On Hold
1236	joan	52455	engine	8646	Completed
2122	jim	6545	4141	5151	On Hold
4152	Bratish Panjara	482585	56446	Running Repair	TSS

I'm stumped,
At the end of the day I would like to clear the contents of the line that has "completed" in the last column. and I would like for the table to auto sort.
The "status" column is a drop down if that makes a difference.
Keep in mind this part of my report is 74 lines long.
This spreadsheet has more columns and rows beside and under what you see, (so I do not want to delete rows for those reasons).
I need help please and thank you.

 

[Michael M]

A couple of questions
1. which columns are required to be cleared...as there are no visible headers ?
2. which column is the data to be sorted by ?

 

[breynolds0431]

Hi. Try the below in a normal module...

Sub EndODay()

'declares active worksheet
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
For Each c In ws.Range("F2:F" & lrow)
    If c.Value = "Completed" Then
        ws.Rows(c.Row).EntireRow.ClearContents
    End If
Next c

'Sort by column F
With ws.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=ws.Range("F2:F" & lrow), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ws.UsedRange
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

 

[JOEE1979]

On the green section, I would like to clear the "completed" line, to be sorted by "status" column (without interrupting any of the other sections).
The section is goes to line 74. Thank you

 

[JOEE1979]

Hi. Try the below in a normal module...

Sub EndODay()

'declares active worksheet
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
For Each c In ws.Range("F2:F" & lrow)
    If c.Value = "Completed" Then
        ws.Rows(c.Row).EntireRow.ClearContents
    End If
Next c

'Sort by column F
With ws.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=ws.Range("F2:F" & lrow), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ws.UsedRange
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

 

[JOEE1979]

.SortFields.Add2 Key:=ws.Range("F2:F" & lrow), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

It didnt work, the above was highlighted to be debuged

 

[JOEE1979]

A couple of questions
1. which columns are required to be cleared...as there are no visible headers ?
2. which column is the data to be sorted by ?

On the green section, I would like to clear the "completed" line, to be sorted by "status" column (without interrupting any of the other sections).
The section is goes to line 74. Thank you

 

[breynolds0431]

.SortFields.Add2 Key:=ws.Range("F2:F" & lrow), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

It didnt work, the above was highlighted to be debuged

Thanks for the image. The below has been updated so the first row starts on row 10 (row with headers). The clearcontents macro will now be limited to the active row and columns A through F.

Sub EndODay()

'declares active worksheet
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
For Each c In ws.Range("F10:F" & lrow)
    If c.Value = "Completed" Then
        'will clear the active row's columns A through F
        ws.Range(ws.Cells(c.Row, 1), ws.Cells(c.Row, 6)).ClearContents
    End If
Next c

'Sort by column F
With ws.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=ws.Range("F10:F" & lrow), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ws.UsedRange
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

 

[JOEE1979]

Thanks for the image. The below has been updated so the first row starts on row 10 (row with headers). The clearcontents macro will now be limited to the active row and columns A through F.

Sub EndODay()

'declares active worksheet
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
For Each c In ws.Range("F10:F" & lrow)
    If c.Value = "Completed" Then
        'will clear the active row's columns A through F
        ws.Range(ws.Cells(c.Row, 1), ws.Cells(c.Row, 6)).ClearContents
    End If
Next c

'Sort by column F
With ws.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=ws.Range("F10:F" & lrow), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ws.UsedRange
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

This is an improvement, 3 issues;
1. it don't stop at line 74, it keeps going
2. it deletes the "Completed" line (which is good) but it does not sort and move everything (so it leaves a blank line)
3. the debug still highlights as previous

 

[Michael M]

Maybe, but it would have been easier if you had posted the dat rather than an image.....using XL2BB...see my sig for downloading same.

Sub EndODay()
'declares active worksheet
Dim ws As Worksheet
'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
For Each c In ws.Range("F10:F74")
    If c.Value = "Completed" Then
        'will clear the active row's columns A through F
        Range(ws.Cells(c.Row, 1), ws.Cells(c.Row, 6)).Delete
    End If
Next c

'Sort by column F
With ws.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=ws.Range("F10:F74"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ws.UsedRange
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub