Hiding rows conditionally

[mmccabe]

How would I go about hiding a particular row (B) UNLESS ther is data in the row above it (A). I am basically trying to hide unused space so that there is only one blank row (for a new entry) at any given time. Thanks for your help.
Mike

 

[NateO]

Hello Mike, your use of the word above mixed with A vs. B is throwing me... The following will hide the 2->whatever consecutive blank in Column B:

<font face=Courier New><SPAN style="color:darkblue">Public</SPAN> <SPAN style="color:darkblue">Sub</SPAN> hdMultiBlanks()
Intersect(Range([b1], [b65536].End(3)).SpecialCells(xlBlanks), _
    Range([b1], [b65536].End(3)).SpecialCells(xlBlanks). _
        Offset(1)).EntireRow.Hidden = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[mmccabe]

Nate, sorry if I was unclear. Perhaps I should go into more detail. I am creating a workbook to store my clients' financial data. Each of these clients will have their own sheet. On every sheet, each row (of the first 25) will correspond to a single account for the given individual. Because some of my clients may only have one or two accounts, while others may have up to 25, Im trying to show only rows with data in them, in addition to a single row for a new entry. A new form would have only one row (a blank one) showing, and 24 rows hidden. As I type finish entering the first account (row), the next row will unhide itself. I'm doing this to eliminate empty space and simplify the form. If possible, I would like to do this through formatting, rather than a macro. I'm not even sure if it can be done, but if you have any ideas, please let me know. Thanks again,
Mike

 

[NateO]

I would use VBA versus formatting x hundred thousand cells...

Which column do you enter Finish in?

 

[mmccabe]

For this part of the sheet, I would have a maximum of 25 rows (entries), with the data spanning from column A to column E. That is why I thought that formatting would be managable. I was hoping to finish without any special command, by and simply leave the remaining rows hidden or blank, with the exception of the one blank entry row.

 

[Yogi Anand]

How would I go about hiding a particular row (B) UNLESS ther is data in the row above it (A). I am basically trying to hide unused space so that there is only one blank row (for a new entry) at any given time. Thanks for your help.
Mike

Hi mmccabe:

I notice that you have had some valuable contributions from Nate -- wherein you have provided more details as to your real intent -- however, the following approach based on using AdvancedFilter might be of some interest to you ...

y040202h1a.xls
	A	B	C	D
1
2	TRUE
3	FALSE	TRUE
4
5	Field1
6	1
7	2
8	3
9
10
11	4
12	5
13	6
14
15
16
17	8
18	9
Sheet5 (2)

For the data presented in the preceding illutration, using the Advanced Filter to filter the list in place, the rows 10, 15, and 16 will get hidden.

You may optionally use the following code for to execute the AdvancedFilter ...

Range("A5:A18").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:B3"), Unique:=False

As I have stated above with your expanded scope, this approach may not be sufficient for what you intend to do -- but I thought let me share it with you anyway.

 

[mmccabe]

Thanks to both of you.
Mike.