Use VBA to hide rows based on the cell value

[andrewb90]

Hello All,

I am a little stumped on this, and unfortunately the macro recorder is no good here...

What I am trying to do is look at the active sheet and if there is no data in D5, then that row would be hidden. I would want to continue all the way down to row 70, however, Rows 15, 25, & 38 would need need to be excluded.

Any assistance would be greatly appreciated.

Thanks,

Andrew

 

[andrewb90]

I'm not likely to to have more than 40 blanks, but out of curiosity, why 40?

 

[Rick Rothstein]

I'm not likely to to have more than 40 blanks, but out of curiosity, why 40?

First off, I made a mistake, it is not 40 but about 31 instead... note though, that is not 31 cells, but rather, 31 areas of blanks (if you had, say, 10 blanks in a row, the address for those cells will take up as many characters as a single cell reference). The text argument for the Evaluate function is limited to 255 character... the address list returned by the Evaluate function will look this...

D8:D8 D9:D9 D13:D13 D15:D15 and so on..

For most of the addresses, the row number will be two digits so an area of blank cells will require 8 characters to describe it (seven characters for the address and one character for the blank between addresses). So, 255 divided by 8 equals 31.875, so rounding down to 31 gives the maximum number of areas before the overall text string returned by the Evaluate function becomes too large. Even at 31 blank areas, that would mean they were all single cells, that would mean nearly have the cell in Column D would need to be blank before the code ceased working.

 

[Michael M]

In that case, would that not suggest that the codes by either Warpigl3t or myself, would be more robust in the event of the OP expanding his / her ranges ??
I certainly agree that yours is far more efficient, given the conditions you mention.

 

[Rick Rothstein]

note though, that is not 31 cells, but rather, 31 areas of blanks (if you had, say, 10 blanks in a row, the address for those cells will take up as many characters as a single cell reference).

Apparently, the above statement is not true... the 31 cell limit is absolute, even if the blank cells are next to each other. As it turns out, the output from the Evaluate function is cell-based, not area-based... it appears that no "optimization" to reduce addresses of adjacent cells takes place in the output from the Evaluate function.

 

[Rick Rothstein]

Apparently, the above statement is not true... the 31 cell limit is absolute, even if the blank cells are next to each other. As it turns out, the output from the Evaluate function is cell-based, not area-based... it appears that no "optimization" to reduce addresses of adjacent cells takes place in the output from the Evaluate function.

I have completely removed the 31 cell limit... virtually any number of cells within the range D5:D70 can now be blank... as a worse-case scenario, as long as at least one cell is displaying a value, all the rest of the cells in Column D can be blank and they will properly be hidden...

[table="width: 500"]
[tr]
	[td]Sub HdeRowsWhereD5D70IsBlank()
  Rows("5:70").Hidden = False
  Range(Replace(Application.Trim(Join(Evaluate("TRANSPOSE(IF(D5:D70="""",ADDRESS(ROW(D5:D70),4,4,1),""""))"), " ")), " ", ",")).EntireRow.Hidden = True
  Range("15:15,25:25,38:38").EntireRow.Hidden = False
End Sub[/td]
[/tr]
[/table]

 

[andrewb90]

Rick,
Thanks for explaining how that other code works. Slowly but surely I'm expanding my knowledge of Excel.
In theory I shouldn't have more than 20 or so blank lines, but it is always good to have a backup, as the user may end up removing some names from the schedule therefore freeing up more lines.