“If…Then” with an “And”

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
maybe
Code:
For Each cell In ActiveSheet.Range("AS16:AS575")
If cell.Value = "" And cell.Offset(0, -3).Value = "" Then cell.EntireRow.Hidden = True
Next cell
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
Code:
 ""
and a pair from word,
Code:
””
which won't work as desired
 
Upvote 0
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):
Code:
For rw = 575 to 16 step -1
  If cells(rw,"AS").Value = "" And cells(rw,"AP").value = "" Then rows(rw).delete
next rw
 
Upvote 0
Hmm, nice pickup
But post #3 says HIDE.
At least now both bases are covered.

Cheers
 
Upvote 0

Forum statistics

Threads
1,222,181
Messages
6,164,428
Members
451,894
Latest member
480BOY

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top