Solved: Dynamically hide unused rows

[dalea]

[vba]
Option Explicit

Sub HideRows()
'
' HideRows Macro
' Macro recorded 4/2/2009 by dalea
'
Dim rHide As Range
rHide = Application.Range.Value.=Hideme
rHide.EntireRow.hidden = True
'End Sub

[/vba]

I am designing a two worksheet workbook as an event planner to be saved as a template when I'm through. The first worksheet is named "Variables". This will be the worksheet were the user will enter all variable information. The only area they will be allowed to enter on the "Budget" page is the "Actual" column which is shaded in blue.

As you can see the template provides a maximum of 49 expense categories. In most cases the user will have less than the maximum. On the "Budget" worksheet I've constructed a string in cell F1 which describes the unused rows. I've assigned this to the named range "Hideme"; i.e., Hideme = Indirect(F!).

In the code above I'm trying to hide these unused rows so that when the report is printed it won't show a bunch of empty rows between the last used row and the "Totals" line.

I know it's not difficult, but I seem to have a mental block and no one in my office to ask. Please help with the code so that it does what I need.

P.S. I miss my morning coffee more often than I miss reviewing all the new threads every morning. If Mr Gore had not already invented the Internet, they would have to invent it just to host this group of geniuses.

[Bob Phillips]

Try this

[vba]

Sub HideRows()
Dim rHide As Range
Set rHide = Range("A" & Application.CountA(Range("A7:A55")) - Application.CountBlank(Range("A7:A55")) + 7 & ":A55")
rHide.EntireRow.Hidden = True
End Sub
[/vba]

[dalea]

Sir Bob - Exactly what I needed. Live long and prosper.