If statement for entire column rather than cell by cell...

[jillian728]

I'm sure that there is a smarter way to do this... Does anyone have any suggestions on how to apply this code to the entire column rather than cell by cell?

Columns("Q:Q").Select
If Range("Q2").Value <> "" Then
If Range("Q2").Value <> "Offer Accepted" Then
Range("Q2").Select
Selection.ClearContents
End If
End If
If Range("Q3").Value <> "" Then
If Range("Q3").Value <> "Offer Accepted" Then
Range("Q3").Select
Selection.ClearContents
End If
End If

 

[jillian728]

Re: If statement for entire column rather than cell by cell.

You're both great. Here is the code that I ended up with:


'**This code looks at column Q and deletes all cells that do not have the word Accepted in it.**'

For Each cell In Range("Q2:Q" & Range("Q65536").End(xlUp).Row)
If cell <> "" And Not cell Like "*Accepted*" Then cell.ClearContents
Next cell

 

[jillian728]

Re: If statement for entire column rather than cell by cell.

You're both great. Here is the code that I ended up with:


'**This code looks at column Q and deletes all cells that do not have the word Accepted in it.**'

For Each cell In Range("Q2:Q" & Range("Q65536").End(xlUp).Row)
If cell <> "" And Not cell Like "*Accepted*" Then cell.ClearContents
Next cell

 

[Yogi Anand]

Re: If statement for entire column rather than cell by cell.

Good job Jillian -- finally got it working -- now let us keep EXCELing!