Hide rows if all cells are blank

[Patish]

Hi

I'm looking for a Macro that, if all cells in a row or rows are blank it will hide all those rows. I have rather a large worksheet with a few thousand rows.

Many thanks

Patish

[CAntosh]

Something like this ought to work:

Please Login or Register  to view this content.

This procedure figures out your bottom row by looking at the last row in Column A. If it should look in a different column, then change the "1" in - LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - to the number of the column that reaches down to the last row.

[MarvinP]

Hi cantosh,

Did you know instead of using:

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

You can use the column letter, like:

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

I like to use the Column Letter instead of the number in my code.

[CAntosh]

Haha, yes I did. I habitually use the column number in my own code because it's a touch faster to type. Your comment, however, made me realize that having to type an explanation for the OP completely undid any efficiency I might've gained! I should probably start using the method that offers greater clarity when I'm posting for (potentially) newer users...

[Patish]

Hi Guys

I hope you don't mind if I attach a sample of the file. I don't want to waste your precious time.

Let me summarise for you:

- Columns A to E will never have blank cells.
- If you go to column BC. The one highlighted in Yellow. It is the column that contains the total of the all the amounts in the rows between columns F to BB. If any cell in column BC is blank I want to hide the entire row.

Does that sound better? I know, that's not really what I explained above. My apologies.

Many thanks.
Patish

[ptmuldoon]

Patish.

The code shared above should do as your asking. You just need to to adjust it to look at column BC like such

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Did you just want the blanks hidden?

Please Login or Register  to view this content.

If so this should work. However, some of your cells are showing a 0 value. This won't work for those.

[Patish]

Thanks guys, for your quick replies.

John, you asked if I want just the blanks hidden. The answer is no. If we take one row, for example. If there are no numbers in that row from columns F to BC (total will also be blank) I want that row to be hidden, including the contents of the cells from columns A to E. Bottom line I just want to see only the rows where there are numbers. The rest must be hidden.

Now, here's the thing! The total may not always be in column BC. So, is there a way for me to be able to select the column that contains the total of the row and use that column to hide the blank rows?

Thanks

Patish