VBA Hiding Rows
 

I have a table of data with dates in the first column and then a bunch
of other data across the row. On top of this I have a beginning date
cell and an ending date -- I would like to use VBA to hide the rows
with dates outside of the period (bounded by the begin/end dates). How
do I do this?

Also if it could update as the begin/end dates change -- that would be
ideal.

VBA Hiding Rows
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B1")) Is Nothing Then
Cells.Rows.Hidden = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value < "" And _
(Cells(i, "A").Value < Range("A1").Value Or _
Cells(i, "A").Value Range("B1").Value) Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

wrote in message
ps.com...
I have a table of data with dates in the first column and then a bunch
of other data across the row. On top of this I have a beginning date
cell and an ending date -- I would like to use VBA to hide the rows
with dates outside of the period (bounded by the begin/end dates). How
do I do this?

Also if it could update as the begin/end dates change -- that would be
ideal.

VBA Hiding Rows
 

Thanks -- I'm having a hard time with this -- I haven't used Visual
Basic really since I was in high school -- Could you explain what the
code is doing (i.e. how I can adapt it to my spreadsheet)?

VBA Hiding Rows
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:

Handle errors, and reset events in this case

Application.EnableEvents = False

This is just to stop this code creating a cascade of changes

If Not Intersect(Target, Me.Range("A1:B1")) Is Nothing Then

This checks whether cells A1 and B1 have changed or not, which is where I
assumed you would store your two dates. Change to suit.

Cells.Rows.Hidden = False

Unhides all rows in case there is one hidden that would not now be hidden
due to a changed date

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1

Find the bottom row and step back one row at a time

If Cells(i, "A").Value < "" And _
(Cells(i, "A").Value < Range("A1").Value Or _
Cells(i, "A").Value Range("B1").Value) Then

If the date in the current row in column A (change to suit) is less than the
earlier stored date or greater than the later stored date, then ...

Rows(i).Hidden = True

Hide the row

Else
Rows(i).Hidden = False

This is actually redundant.

End If
Next i
End If

ws_exit:
Application.EnableEvents = True

Set events back on again.

End Sub

Note where the code is stored as per my original reoply.

--
HTH

Bob Phillips

wrote in message
ups.com...
Thanks -- I'm having a hard time with this -- I haven't used Visual
Basic really since I was in high school -- Could you explain what the
code is doing (i.e. how I can adapt it to my spreadsheet)?

VBA Hiding Rows
 

You could trap the calculate event, but that would fire whatever changes, so
I think a format change is simpler.

--
HTH

Bob Phillips

wrote in message
oups.com...
Great thanks! Only one more thing -- sorry I forgot. This code would
be put on an output page where nothing could be changed -- the begin
and end dates reference another cell on another sheet. Is there a way
that code can be inserted which would execute the script if the values
simply changed (not typing a new date and hitting enter)?

If not, I may just change the format of my spreadsheet.