Hide Rows With Blank Cells - VBA (So close!)

[Excel Pooh]

Good afternoon world of smarter people than I. I am trying to create a macro that first unhides all rows on a worksheet, then checks all rows to see if there is content in Column E and/or Column H. If they are BOTH blank, I would like those entire rows to then be automatically hidden. I have Frankenstein-ed together an attempt from searching here and other forums and it has gotten me close, but still leaves some random rows visible when they have no content. Could anyone please let me know where I've gone wrong or even show me something fresh that is better? I appreciate your time, code is below.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual

Rows("3:700").Hidden = False

For Each c In Range("E3:E700")
If c.Value = "" And c.Offset(3).Value = "" Then Rows(c.Row).Hidden = True
Next c

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Welcome to the Board!

but still leaves some random rows visible when they have no content.

The following moves 3 rows down, not 3 columns across:

c.Offset(3)

Try this instead:

c.Offset(, 3)

 

[Joe4]

Just to clarify, the format of Offset is:

OFFSET(rows, columns)

So you need to move to the second argument (what the "," does).
If you only list one number, it applies it to rows.

I usually include both arguments, so I would write what you are trying to do like:

OFFSET(0, 3)

as that makes it clearer to me.

 

[Excel Pooh]

That does the trick!

Thank you for your kindness when dealing with such a rookie mistake.

 

[Joe4]

Thank you for your kindness when dealing with such a rookie mistake.

No worries!

We respect the effort trying to put it all together yourself.
We like people who aren't afraid to try, and are more than happy to help them make corrections where necessary.

 

[Excel Pooh]

Is there a quick way to make it so that instead of selecting the pre-defined range (in this case rows 3-700) that this formula could be changed to first unhide all rows and then look at the conditions of all rows to see if it needs to be hidden or not?

Hopefully I'm describing that in a way that makes sense. Basically, the range of 3-700 was completely arbitrary and this sheet will eventually get bigger than 700 rows.

 

[Excel Pooh]

No worries!

We respect the effort trying to put it all together yourself.
We like people who aren't afraid to try, and are more than happy to help them make corrections where necessary.

Not sure if I need to actually directly reply to your posts for it to send you a notification. I haven't used an online forum in about ten years haha so apologies if I'm double-posting for no reason...

Is there a quick way to make it so that instead of selecting the pre-defined range (in this case rows 3-700) that this formula could be changed to first unhide all rows and then look at the conditions of all rows to see if it needs to be hidden or not?

Hopefully I'm describing that in a way that makes sense. Basically, the range of 3-700 was completely arbitrary and this sheet will eventually get bigger than 700 rows.

 

[Joe4]

Yes, I do see all your replies. Sorry, I was offline the rest of the day yesterday.

Typically, we do not want to run the code against every row on the sheet, as that is usually overkill, is very slow, and inefficient.
Usually, we can dynamically determine the last row of used data, and just go that far.

One way to do that is to identify a column that ALWAYS has data for every row that has data, and use that to determine the last row we need to go down to.
If that was column"A", we can write that like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Then, we can amend your code to only run it down that far like this:

Rows("3:" & lastRow).Hidden = False

For Each c In Range("E3:E" & lastRow)

If that is not a good way of doing it because every column may contain blank cells, then we may be able to use "Used Range" instead, to find the last row, as shown here: VBA codes to determine Last Used Row & Last Used Column in Excel

Hope that helps.