Clearing a data range based on a cells criteria!!

acousticlife81

New Member
Joined
Mar 29, 2017
Messages
12
Trying to clear B15: I43 based on if the cell E:E is "Complete". Then with the Ongoing and Pending status needing them to not delete and sort based on Assigned Date.

The following column does not need to be deleted. Columns K:M

Thanks - J

I cannot attach the file did the rules change.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Here is the code I am using

Dim lRw As Long, n As Long
lRw = Range("E" & Rows.Count).End(xlUp).Row
For n = 1 To lRw
If Range("E" & n).Value = "Complete" Then Range("E" & n).Cells.SpecialCells _
(xlCellTypeConstants, 23).ClearContents
Next n


Range("B15:N33").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"E15:E33"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("B15:J33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 
Upvote 0
sorry i posted the wrong code here it is?

Dim lRw As Long, n As Long
lRw = Range("E" & Rows.Count).End(xlUp).Row
For n = 1 To lRw
If Range("E" & n).Value = "Complete" Then Range("E" & n).EntireRow.SpecialCells _
(xlCellTypeConstants, 23).ClearContents
Next n

Range("B15:N33").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"E15:E33"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("B15:N33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 
Upvote 0
It is kind of confusing. Do you want it to clear Range B15:N33, skipping Columns K:M, if any Cell in Column E says "Complete"?
 
Upvote 0
For clearing the contents in column E to column I, I used this code and it worked.
Before trying save your sheet and then try it as Macros do not have undo option. And I am a first timer in VBA.

Code:
Sub deletecontents()
Dim lRw As Range
Dim n As Range
Set lRw = ThisWorkbook.ActiveSheet.Range("E15:E43")
For Each n In lRw
    If n.Value = "Complete" Then
    Range("b" & n.Row, "I" & n.Row).ClearContents
    End If
Next
End Sub
 
Last edited:
Upvote 0
How would I sort it alphabetically by column E. I tried recording the macro and pasting the macro in your code and it did not run. Here is the code.

Dim lRw As Range
Dim n As Range
Set lRw = ThisWorkbook.ActiveSheet.Range("E15:E43")
For Each n In lRw
If n.Value = "Complete" Then
Range("b" & n.Row, "J" & n.Row).ClearContents
End If
Next


Range("B15:M33").Select
ActiveWorkbook.Worksheets("A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("E15:E33"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("A").Sort
.SetRange Range("B15:M33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top