Simple Loop to find blank in cell A3:A32 and text in cell D3:D32

[jules-57]

Hello all,

Looking for a simple loop or filter that looks in cell D3:D32 for a word "CLASS SIZE". If that word exists, and if there is a corresponding empty cell in A3:A32, then delete the entire row. Otherwise, leave it alone and move down to the next cell in column D and check that until D33 and quit.

Can someone help with this?

Thank you.

 

[jules-57]

Anyone with an idea on this task?

 

[HalfAce]

Hi,
A quick loop for that would look something like this.

Sub DeleteRows()
For ThsRw = 32 To 3 Step -1
    If Cells(ThsRw, "D").Value = "CLASS SIZE" And Cells(ThsRw, "A") = "" Then Rows(ThsRw).Delete
Next ThsRw
End Sub

Note that, as written, it is case sensitive. (It will ONLY delete the row(s) if the value in column D is in all CAPS.)
If you don't want it to be case sensitive, you can simply add the line:
Option Compare Text
in the code module above the routine.

Hope it helps.

 

[jules-57]

Thank you. If I wanted it to be explicit to a specific sheet, say sheet1, named scores, how would I do that?

 

[HalfAce]

To make it work only on a sheet names 'scores', from any sheet in the workbook, you can do this.

Sub DeleteRows()
    [B][COLOR="#FF0000"]With Sheets("scores")[/COLOR][/B]
        For ThsRw = 32 To 3 Step -1
            If [B][COLOR="#FF0000"].[/COLOR][/B]Cells(ThsRw, "D").Value = "CLASS SIZE" And [B][COLOR="#FF0000"].[/COLOR][/B]Cells(ThsRw, "A") = "" Then [B][COLOR="#FF0000"].[/COLOR][/B]Rows(ThsRw).Delete
        Next ThsRw
    [B][COLOR="#FF0000"]End With[/COLOR][/B]
End Sub

Hope it helps.

 

[jules-57]

That worked great. Thank you!

One more simple question: what if the string - which was originally "CLASS SIZE" - actually had a variable like "CLASS SIZE 999" which I did not wish to include but still wanted to find and delete. In other words, the code above is a truncated version of the actual text. Would your "Option Compare Text" work for this, or is there something else needed?

Thank you.

 

[jules-57]

I think found the answer using the "Like" command. thank you again for your help.

 

[HalfAce]

Yes, you found one of the two suggestions I would have offered. (The other would have been to try using "If InStr".)

(The Option Compare Text line simply tells vba to ignore the text case of whatever it's looking at, so it could be capitals, lower case or any combination and vba will see them all the same.)

You're most welcome. Glad it helped.