Automatically UnHide rows as per requirement

M

Murtaza

How Can I Insert Rows as per requirement?

I am working on an Expense Template. I have spared around 20 rows for the
User but generally only 5-10 rows required.

Now Is it possible that I start with 3 rows and if user needs more space
then those hidden rows Unhide one by one as per the need arise.

I will protect the workbook.
 
D

Dave Peterson

You could give the users a macro that would:

ask how many rows to unhide
unprotect the worksheet
unhide those rows
reprotect the worksheet

Kind of like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myHiddenRows As Range
Dim HowManyToShow As Long
Dim CountOfShownRows As Long
Dim myCell As Range

Set wks = ActiveSheet

With wks
Set myHiddenRows = .Range("a12:A31")
HowManyToShow = CLng(Application.InputBox _
(Prompt:="How many rows to unhide?", Type:=1))

If HowManyToShow < 1 Then
Exit Sub
End If

.Unprotect Password:="hi"

CountOfShownRows = 0
For Each myCell In myHiddenRows.Cells
If myCell.EntireRow.Hidden = True Then
myCell.EntireRow.Hidden = False
CountOfShownRows = CountOfShownRows + 1
If CountOfShownRows >= HowManyToShow Then
Exit For
End If
End If
Next myCell

.Protect Password:="hi"
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You could even put a button from the forms toolbar on the worksheet. Then
assign this macro to that button to make it easy to run.
 
Top