Unhide Hidden Rows

[adam]

Hi anyone!

I have rows hidden in my sheet with the range A36:A45.

I need your kind help to modify the following code so that when the macro button is clicked, it would ask me how many rows I want to unhide and then when I write the number of rows in the message box it unhides the number of rows for me.

The following code actually inserts rows. But I want it to be modified so that it unhides hidden rows.

Code: Select all

Sub InsertRow()
Dim Rng, n As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub

[HansV]

Change the lines

Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert

to

Range(ActiveCell, ActiveCell.Offset(Rng - 1, 0)).EntireRow.Hidden = False

[adam]

Thanks Hans. That works the way I just wanted.

[adam]

How should I modify the line
Range(ActiveCell, ActiveCell.Offset(Rng - 1, 0)).EntireRow.Hidden = False
If I want to unhide the rows from the range A16:A35

One more question if I may ask,

After I Unhide the hidden rows my worksheet's row; where I have to enter "Billing data" becomes a total of 10 rows excluding the header highlighted in yellow and the total row.

and the sheet "billing invoice" in spread from one sheet to two sheets. I have created a page break after the tenth row.

What I want to know is how to make the header highlighted in yellow appear on both pages when I unhide the hidden rows.

I have attached the document for your reference.

Thanks in advance.

[Rudi]

If I want to unhide the rows from the range A16:A35

To Unhide write: Range("A16:A35").EntireRow.Hidden=False

About the Heading appearing on both pages...I did not download your workbook sample as its a simple one liner of cade that you can slot into the macro.
The line of code is: ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

This sets print titles on row 1 and will cause the heading to reflect on all subsequent pages.

[adam]

To Unhide write: Range("A16:A35").EntireRow.Hidden=False

Hi Rudi,
I did try that code before you. But that code unhides all the rows from the range A16:A35.

What I want is to unhide any amount of rows from the range A16:A35.

Say for example I type 2 in the message box that appears when I click the macro button "unhide" I want only two rows to be unhidden from the range A16:A35.
Like that if I type 4 I want four rows to be unhidden from the range A16:A35.

I hope I have made my question clear

[Rudi]

Is this not the question that you asked in the beginning and which Hans has answered already? Sorry if I am misunderstanding?

This is what you asked in the first post:
I have rows hidden in my sheet with the range A36:A45. I need your kind help to modify the following code so that when the macro button is clicked, it would ask me how many rows I want to unhide and then when I write the number of rows in the message box it unhides the number of rows for me.

And this is what this code should do once you modified it with Hans's suggestion, like this:

Code: Select all

Sub UnhideRows()
Dim Rng As Long
    Application.ScreenUpdating = False
    Rng = InputBox("Enter number of rows required.")
    Range(ActiveCell, ActiveCell.Offset(Rng, 0)).EntireRow.Hidden = False
    Application.ScreenUpdating = True
End Sub

This code will unhide the amount of lines you type into the inputbox at the position of the activecell.

If you want to unhide specific rows between A36:A45, then it would be better to let the code unhide rows that you indicate? The question is, which rows between the range A36:A45 do you want unhidden if the code above does not meet your approval.

[adam]

Oops!

I did a mistake in my first question too. Instead of mentioning the range as A26:A35 I wrote A36:A45.

Im sorry & I apologize for that.
Meanwhile, If you download my example workbook you would get a clear picture of What Im wanting.

Actually I had setup a invoice template to fill data up to 10 rows. If the user wants add data to 11 rows he will click the button Unhide and write 1 in the message box so that a hidden row from the range A26:A35 opens and the user can enter data into that row.

same way if the user want to fill the invoice with fourteen rows. he will clcik the Unhide button and enter 4 to the message box so that 4 rows bcome visible from the range A26:A35 enabling the user to enter data.

Once again I apologise for my mistake.

I hope I have made my question clear this time.

[adam]

Once again I forgot to ask how could I make the above reply of mine to work the way as I have mentioned in it.

Your help would be kindly appreciated.

Thanks in advance.

[HansV]

I'd change the line

Range(ActiveCell, ActiveCell.Offset(Rng, 0)).EntireRow.Hidden = False

to

Range("A26:A" & (25 + Rng)).EntireRow.Hidden = False

You may want to add a check that Rng isn't too large.

[adam]

Thanks Hans. Your modification works.

On the other hand I'm sorry I couldn't understand what you meant by "You may want to add a check that Rng isn't too large"

By the way following is how I modified the code

Code: Select all

Sub Unhide()
Dim Rng, n As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
Range("A26:A" & (25 + Rng)).EntireRow.Hidden = False
End Sub

As I had asked previously I want the header highlighted in yellow to appear in the next page when I unhide a row.

As Rudi suggested I placed The line of code : ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" below the end sub of the above code but I guess I'm unable to make the highlighted header to appear on the next page. ( I haven't included that in the above version)

I would be happy If I'm detailed a bit more.

Thanks in advance

[HansV]

Activate the Page Layout tab of the ribbon.
Click Print Titles in the Page Setup section.
Click in the 'Rows to repeat at top' box.
Click in the worksheet, and select rows 1 to 15.
This will enter $1:$15 in the box.
Click OK.

x79.png

No code needed.

[HansV]

PS Your worksheet is named "Biiling Invoice". This should be "Billing Invoice".

[adam]

Thanks Hans. I got it working.

[adam]

The following code inserts rows above the row cell that is double clicked. I need help to modify the code so that when the cell A36 is double clicked, hidden rows from the range A16:A35 are unhidden.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   Target.Offset(0).EntireRow.Insert
   Target.Offset(-2).EntireRow.Copy Target.Offset(-2).EntireRow
   On Error Resume Next
   Target.Offset(-1).EntireRow.SpecialCells(xlConstants).ClearContents
   On Error GoTo 0
End Sub

[HansV]

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target = Range("A36") Then
        Range("A16:A35").EntireRow.Hidden = False
        Cancel = True
    End If
End Sub

[Don Wells]

Typo, Hans
If Target = Range("A36") Then

[HansV]

Thank you, Don. I'll correct it.

[HansV]

Here's a more dependable version:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$36" Then
        Range("A16:A35").EntireRow.Hidden = False
        Cancel = True
    End If
End Sub

[adam]

Thanks for the code Hans, But I would be happy I the code could be modified so that each time I double click the cell A36 a single row from the range A16:A35 is unhidden.

Meaning If I double click the cell A36 All the rows from the range A16:A35 will be unhidden.

Your code unhidden all the cells from the range with a one double click. I want each row to be unhidden separately by double click on cell A36.

I hope I have made my question clear.