Clear Content in Row - Based on value in column

[Bos34567]

Hello...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need a little bit help... I think it should be easy code.. Looking to clear content in a row based on a value in a column.

I am trying to clear all contents in a row based on the value of "Yes" in Column AA. The range is "AA14" through range "AA2500".

I have this formula: =IF(AND(+A14=0,+B14=0),"Yes","No") in "AA14" and it goes down for each row.

If "Yes" appears in that column, based on zero in A & B, I need to clear the whole row including the "yes" formala column and it goes to the next row until it is done.

Of course, if there is an easier way of clearing content in a row based on what is given, I am open to clear content a different way..

Thanks for your help in advance.
Bill

 

[mvptomlinson]

Hi Bill, try:

Sub delRows()
Dim i As Long
Application.ScreenUpdating = False
For i = 2500 To 14 Step -1
    If UCase(Cells(i, "AA").Value) = "YES" Then Cells(i, 1).EntireRow.ClearContents
Next i
Application.ScreenUpdating = True
End Sub

 

[Bos34567]

A quick test, it works great. I am going to run it in my workbook tomorrow. I appreciate the quick response...

 

[Bos34567]

Hello..
I used the code in the workbook. It is very slow. There is a lot of data to sort through. We are in Column GT for the Yes part and we are down to 3500 rows.... In my test, it was AA and 2500 rows with very limited data...

Here is the code:

Sheets("RESULTS INPUT").Select

Dim i As Long

Application.ScreenUpdating = False

For i = 3500 To 14 Step -1
If UCase(Cells(i, "GT").Value) = "YES" Then Cells(i,1).EntireRow.ClearContents
Next i
​

Application.ScreenUpdating = True
​

Not sure how to speed it up... There is data in column A - GD.

Bill

 

[mvptomlinson]

How slow is slow? With screenupdating off, it should be reasonably quick unless your worksheet is recalculating during the process. For that you could just add a line to set calculation to manual first, and then set it back to automatic afterward.

 

[Bos34567]

Hello.. It was really slow... Like 5 minutes to do about 20 lines...

Anyway, I figured out the issue this morning, looking through some post...

Yes, you are correct. The calculation was my problem... I put this code inbetween the code you sent..

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

The code you had above....

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True

End With

Thanks for responding back....

Bill

It is excel 07

 

[mvptomlinson]

At the end, you should set .Calculation back to calcmode, since that was the previous state before setting it to manual (and presumably why you stored it in a variable at the beginning of the macro).

.Calculation = calcmode
.ScreenUpdating = True

 

[Peter_SSs]

Instead of looping through all those rows, you might also consider something like this if speed is an issue. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> calcmode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        calcmode = .Calculation<br>        .Calculation = xlCalculationManual<br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("GT13", Range("GT" & Rows.Count).End(xlUp))<br>        Intersect(.EntireRow, Columns("A:GT")).Sort Key1:=Range("GT14"), _<br>            Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _<br>            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal<br>        .AutoFilter Field:=1, Criteria1:="=Yes"<br>        .Offset(1).EntireRow.ClearContents<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .Calculation = calcmode<br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[Bos34567]

Hello... Sorry not to respond sooner... I was traveling for the holiday break. I did try that second way. That worked and did make it speed up. The filter was not needed for this workbook due to links... However, it is very useful code.

 

[crook_101]

Hi,

This may come in useful one day - I cannot remember the original author but it has had a few incarnations, nice and easy for "users"....

Ian.

Sub DeleteRowTool()
Dim rRange As Range
Dim strCriteria As String
Dim lCol As Long
Dim rHeaderCol As Range
Dim xlCalc As XlCalculation
Const strTitle As String = "Row Delete Tool"
    On Error Resume Next
Step1:
        Set rRange = Application.InputBox(Prompt:="Select range including header range" _
        , Title:=strTitle & " Step 1 of 3", Default:=ActiveCell.CurrentRegion.Address, Type:=8)
        
        If rRange Is Nothing Then Exit Sub
         Application.Goto rRange.Rows(1), True
    
Step2:
        lCol = Application.InputBox(Prompt:="Please enter relative column number of evaluation column" _
        , Title:=strTitle & " Step 2 of 3", Default:=1, Type:=1)
        
        If lCol = 0 Then Exit Sub
Step3:
        strCriteria = InputBox(Prompt:="Please enter a single criteria." & _
        vbNewLine & "Eg >5 OR <10 OR Cat* OR *Cat OR *Cat*" _
        , Title:=strTitle & " Step 3 of 3")
        
    If strCriteria = vbNullString Then Exit Sub
    
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
        
    ActiveSheet.AutoFilterMode = False
    
    With rRange
      .AutoFilter Field:=lCol, Criteria1:=strCriteria
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    With Application
        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   On Error GoTo 0
End Sub