“If…Then” with an “And”

[Brian F]

I an trying to say;

Delete each row in the range “AS59:AS616” if the cell in row “AS” is empty, EXCEPT for those in which the cell in the same row, 3 columns to the left is NOT empty. I’m just finding my way with vba and the “AND” function is hanging me up. Help would be appreciated.

For Each cell In ActiveSheet.Range("AS16:AS575")
If cell.Value = "" And cell.offset(0,-3)<>”” Then cell.EntireRow.Hidden = True

 

[p45cal]

change <>”” to =""
?

 

[Brian F]

I want to leave the rows in which the cell 3 columns to the left ARE NOT empty. Changing <>"" to ="" would say the opposite, that they are "" (empty).

using the code;

For Each cell In ActiveSheet.Range("AS16:AS575")
If cell.Value = "" Then cell.EntireRow.Hidden = True

Hides ALL the rows. I want them to remain visible if there is anything in the cell 3 columns to the left.

 

[Michael M]

maybe

For Each cell In ActiveSheet.Range("AS16:AS575")
If cell.Value = "" And cell.Offset(0, -3).Value = "" Then cell.EntireRow.Hidden = True
Next cell

 

[p45cal]

ADVERTISEMENT

I want to leave the rows in which the cell 3 columns to the left ARE NOT empty. Changing <>"" to ="" would say the opposite, that they are "" (empty).

.Hidden =True doesn't leave rows, it hides rows.

using the code;

For Each cell In ActiveSheet.Range("AS16:AS575")
If cell.Value = "" Then cell.EntireRow.Hidden = True

Hides ALL the rows. I want them to remain visible if there is anything in the cell 3 columns to the left.

That's not what I said. My suggestion changes:
If cell.Value = "" And cell.offset(0,-3)<>”” Then cell.EntireRow.Hidden = True
to:
If cell.Value = "" And cell.offset(0,-3)="" Then cell.EntireRow.Hidden = True
which, not having tested it, I think hides those rows where both cells are "", and is similar to Michael M's suggestion.

 

[Michael M]

p45cal
Yep, your right.
I only posted to try and clarify / confirm with the complete lines.
AND
Brian, you need to ensure the double quotes are the correct ones.
I note in your code you have a pair from Excel

 ""

and a pair from word,

””

which won't work as desired

 

[p45cal]

I note that Brian F in msg#1 says he wants to delete rows but the code he cites only hides rows.Should he want to delete rows the current loop, which steps down throuigh the rows, is in danger of tripping up. The loop will need to step upwards through the rows with the likes of (untested):

For rw = 575 to 16 step -1
  If cells(rw,"AS").Value = "" And cells(rw,"AP").value = "" Then rows(rw).delete
next rw

 

[Michael M]

Hmm, nice pickup
But post #3 says HIDE.
At least now both bases are covered.

Cheers