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

 

[DRJ]

    For x = 2 to range("Q65536").End(xlup).Row
        If Range("Q" & x).Value <> "" and Range("Q" & x).Value <> "Offer Accepted" Then Range("Q" & x).ClearContents
    Next x

 

[jillian728]

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

Thanks Jacob.

This worked perfectly. This is something that I will be able to use over and over again.

 

[Yogi Anand]

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

Hi Jillian:

In addition to Jacob's, you may also try ...

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

 

[DRJ]

Glad to help

Take Care

 

[jillian728]

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

Thanks for the additional method Yogi. I'll try that out too.

Quick question - I know that <> is used in formulas for does not equal. What would I put in place of <> to represent "does not contain". I'm not sure if there is an option for this.

Thanks!

 

[DRJ]

You mean if it has part of the value? You can try

If range("A1").Value not like ("ab*")

use * for the wildcard

 

[Yogi Anand]

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

Hi Jacob:

I think it should be ...

If not Range("A1").value like ("ab*") ....

What do you think?

 

[DRJ]

Yeah your right. That's what I get for not typing up the code in vbe. I tried mine in vbe and excel got pissed right away.

Thanks

 

[Yogi Anand]

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

Let us make it even ...

If not Range("A1").value like ("*ab*") ...