Use VBA to hide rows based on the cell value

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
Code:
[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]
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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