Automatically Hiding Blank Rows

[patryan22]

I have a system of IF functions that contain vlookups on a worksheet.
They pull data from a separate "raw data" page. Depending on certain
values in the data page, I have it set up so that some rows come up
entirely blank on my view page.

I would like to automatically hide these blank rows so that if the
right thing is changed on the data page, these rows will reappear with
the relevant information. Does this make sense? Anybody know how to
do this?

Thanks!

 

[Don Guillett]

You could do this with a worksheet_change event.
Right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$2" Then Exit Sub
If Target = 1 Then
Range("a1,a4,a8").EntireRow.Hidden = True
ElseIf Target <> 1 Then
Cells.EntireRow.Hidden = False
End If
End Sub

 

[patryan22]

Hi Don,

Thanks! The only problem is I am not fluent in the VB. In my sheet,
if column K is blank, I want the row to be hidden. If column K is not
blank, I want the row to appear. Could you tell me how to put that in
code?

Pat

 

[Don Guillett]

What do you mean by "col K is blank"?
nothing in the entire column?
nothing in a particular row of col k?
How about some more detail with EXAMPLES

 

[patryan22]

Oh I'm sorry, I definitely wasn't very clear.

This workbook is displaying information for a bank account. It contains
two worksheets. One is for data and updates. I have it set up so that
the accountant can go in at the end of each day and make any updates
with any transactions that occured during the day. In column K, I have
an IF function that displays the word "OUTSTANDING" if the transaction
in the row has not been offset.

The other worksheet is what the accountant can look at and have a nice
list of outstanding transactions. It is identical in positioning and
formatting to the data page. It uses VLOOKUPS to pull the information
from the data page, but only IF the cell in column K of the data sheet
has the word "OUTSTANDING" in it. If the way I just described that
makes sense, you can probably imagine that any outstanding transactions
show up while all others do not. For this reason, I have a bunch of
mostly blank rows that I would like to hide. I say "mostly" because in
column A, each row has a number (from 1 to 600) that the vlookups
reference. I do not want these numbers to change because the page
automatically updates based on activity in the data sheet.

I think that is about the best I can describe it in words. This
problem seems like something I would normally use an IF funtion for:
=IF(ISBLANK($K458),HIDE THIS ROW,DO NOTHING)

-or-

=IF($K458="OUTSTANDING",DO NOTHING,HIDE THIS ROW)

Thanks for all your help. Please let me know if you need any more
clarification.

Pat

 

[Don Guillett]

The macro recorder can be your friend.
Sub Macro10()
'
' Macro10 Macro
' Macro recorded 12/31/2005 by Don Guillett
'

'
Range("B1:B7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="abc"
Selection.AutoFilter Field:=1
End Sub

refined to

sub hideabc
Range("B1:B7").AutoFilter Field:=1, Criteria1:="abc"
end sub