# Clear certain cells



## JOEE1979 (Dec 18, 2022)

Hello everyone

UNITMechanicW/O #ActivityClassificationStatus1212Anthony Ayou4525bootBDNOn Hold1236joan52455engine8646Completed2122jim654541415151On Hold4152Bratish Panjara48258556446Running RepairTSS
 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 (Dec 18, 2022)

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 (Dec 18, 2022)

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 (Dec 18, 2022)

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 (Dec 18, 2022)

breynolds0431 said:


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


----------



## JOEE1979 (Dec 18, 2022)

.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 (Dec 18, 2022)

Michael M said:


> 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 (Dec 18, 2022)

JOEE1979 said:


> .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 (Dec 18, 2022)

breynolds0431 said:


> 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.
> 
> 
> ```
> ...


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 (Dec 18, 2022)

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
```


----------



## JOEE1979 (Dec 18, 2022)

Hello everyone

UNITMechanicW/O #ActivityClassificationStatus1212Anthony Ayou4525bootBDNOn Hold1236joan52455engine8646Completed2122jim654541415151On Hold4152Bratish Panjara48258556446Running RepairTSS
 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.


----------



## JOEE1979 (Dec 18, 2022)

Michael M said:


> 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.
> 
> 
> ```
> ...


final.xlsxABCDEF10UNITMechanicW/O #ActivityClassificationStatus111212Anthony Ayoungsdcsboot5454On Hold12212251651654541415151On Hold13144152Bratish Panjaratnam123456789056446Running RepairCompleted15212251651654541415151On Hold164152Bratish Panjaratnam123456789056446Running RepairTSS17181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980Sheet1


----------



## Michael M (Dec 18, 2022)

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

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

End Sub
```


----------



## Michael M (Dec 18, 2022)

Just spotted....
change the ws. from this line to Activesheet

```
.SetRange *ws*.UsedRange
```


```
.SetRange *Activesheet*.UsedRange
```


----------



## JOEE1979 (Dec 18, 2022)

Michael M said:


> Just spotted....
> change the ws. from this line to Activesheet
> 
> ```
> ...


now its giving me a debug error, and highlighting the .Apply


----------



## Michael M (Dec 18, 2022)

Is there any data in column "F" when you removed "completed" lines ??
Seems to work fine for me !


----------



## JOEE1979 (Dec 18, 2022)

The "completed" line and all others are deleted, I believe I should change it to ClearContent (it keeps moving the other stuff below line 74 up)


----------



## JOEE1979 (Dec 18, 2022)

By replacing .Delete with .ClearContents
It now works and dont mess up the layout
Only issue is it chnages the colour of the lines when it sorts
I think I might have no choice but to leave it white


----------



## Michael M (Dec 18, 2022)

Try

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

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

End Sub
```


----------



## JOEE1979 (Dec 22, 2022)

I'm not sure why this is happening.
This macro works fine if sheet is unlocked.
I have "A475:F540" UNLOCKED under formatting tab
When I lock the sheet, (the only box checked is "Select unlocked cells") 
I'm getting an error

The error shows;
Run-time error '1004':
Sort method of Range class failed



Sub Repairs()
'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("F474:F540")
    If c.Value = "Completed" Then
        'will clear the active row's columns A through F
        Range(Cells(c.Row, 1), Cells(c.Row, 6)).ClearContents
    End If
Next c

'Sort by column A
Range("A474:F540").Sort Key1:=Range("A474"), _
                     Order1:=xlAscending, _
                     Header:=xlYes
End Sub



shift update.xlsmABCDEF474Unit #Assigned toW/O #ActivityW/O TypeStatus4751Adam Marquis11Post MTOVendor4761Ajitpal Lota11Post MTOVendor4771Adam Marquis11Post MTOVendor4781Akbar Fazlali11Post MTOVendor4791Adam Marquis11Post MTOVendor4801Alan Persaud11Post MTOVendor4811Ajitpal Lota11Post MTOVendor4821Akbar Fazlali11Post MTOVendor4831Adam Marquis11Post MTOVendor4841Ajitpal Lota11Post MTOVendor4851Akbar Fazlali11Post MTOVendor4861Adam Marquis11Post MTOVendor4871Ajitpal Lota11Post MTOVendor4881Akbar Fazlali11Post MTOVendor4891Adam Marquis11Post MTOVendor4901Adam Marquis11Post MTOVendor4911Alan Persaud11Post MTOVendor4921Ajitpal Lota11Post MTOVendor4931Akbar Fazlali11Post MTOVendor4941Adam Marquis11Post MTOVendor4951Ajitpal Lota11Post MTOVendor4961Adam Marquis11Post MTOVendor4971Ajitpal Lota11Post MTOVendor4981Ajitpal Lota11Post MTOVendor4991Ajitpal Lota11Post MTOVendor5001Ajitpal Lota11Post MTOVendor5011Ajitpal Lota11Post MTOVendor5021Ajitpal Lota11Post MTOVendor50311Akbar Fazlali1111Post MTOVendor50411Akbar Fazlali1111Post MTOVendor50511Ajitpal Lota1111Post MTOVendor506htyAkbar FazlalihtyhtyPost MTOVendor507hytAjitpal LotahythytPost MTOVendor508hytAjitpal LotahythytPost MTOVendor509rhAjitpal LotarhrhPost MTOVendor510rhAkbar FazlalirhrhPost MTOVendor511rhAjitpal LotarhrhPost MTOVendor512rhAjitpal LotarhrhPost MTOVendor513tAdam MarquisttPost MTOVendor514tAjitpal LotattPost MTOVendor515tAjitpal LotattPost MTOVendor516thyAjitpal LotathythyPost MTOVendor517yAlan PersaudyyPost MTOVendor518yAdam MarquisyyPost MTOVendor519yAdam MarquisyyPost MTOVendor520yAjitpal LotayyPost MTOVendor521yhAdam MarquisyhyhPost MTOVendor522ytAkbar FazlaliytytPost MTOVendor523ytAjitpal LotaytytPost MTOVendor524ytAjitpal LotaytytPost MTOVendor525526527528529530531532533534535536537538539540Shift UpdateCells with Conditional FormattingCellConditionCell FormatStop If TrueA475:F540Expression=MOD(ROW(),2)=0textNOCells with Data ValidationCellAllowCriteriaB475:B540List=EmployeeE475:E540List=WO_TypeF475:F540List=Status


----------



## Michael M (Dec 22, 2022)

Put this line , BEFORE the "For Each" line

```
Sheets("Sheet1").Unprotect
```

AND this line


```
Sheets("Sheet1").Protect
```
Before the "End Sub" line

Change the sheet name to suit if it isn't sheet 1


----------



## JOEE1979 (Dec 18, 2022)

Hello everyone

UNITMechanicW/O #ActivityClassificationStatus1212Anthony Ayou4525bootBDNOn Hold1236joan52455engine8646Completed2122jim654541415151On Hold4152Bratish Panjara48258556446Running RepairTSS
 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.


----------



## JOEE1979 (Dec 27, 2022)

So I got the formula, but now I need to modify it a bit. If I want one of 2 values "Completed" or "Tripped"

How would I write that?


Sub Repairs()
'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("F475:F507")
    If c.Value = "Completed" Then
        'will clear the active row's columns A through F
        Range(Cells(c.Row, 1), Cells(c.Row, 6)).ClearContents
    End If
Next c


----------

