Unhide Hidden Rows

[Don Wells]

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.

Try the following

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Long
    If Target.Address = "$A$36" Then
        For i = 16 To 35
          If Range("A" & i).EntireRow.Hidden Then
              Range("A" & i).EntireRow.Hidden = False
              Exit For
          End If
        Next i
        Cancel = True
    End If
End Sub

[adam]

Thanks Don. That was the code I was looking for.
Moreover, If I may ask is it possible to unhide the rows if I lock the sheet with only unlocking the cell content A36 so that the user by mistake could not delete any data in rest of the sheets cell.

[Rudi]

You could have something like this.
Note: The protection is not using a password. It will protect the sheet from accidental deletes by using sheet protection without password.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Long
    ActiveSheet.Unprotect
    If Target.Address = "$A$36" Then
        For i = 16 To 35
          If Range("A" & i).EntireRow.Hidden Then
              Range("A" & i).EntireRow.Hidden = False
              Exit For
          End If
        Next i
        Cancel = True
    End If
    ActiveSheet.Protect
End Sub

[adam]

Thanks for the code Rudi. But I wonder where I have to add your code to the following code. I mean I want to use the following code with your code in the same sheet. I hope my question is clear.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Application.ScreenUpdating = False
    
    If Not Intersect(Target, Range("H7:H7")) Is Nothing Then
        Cancel = True
        If Target.Value = "PASS" Then
            Target.Value = "FAIL"
            
        Else
            Target.Value = "PASS"
        End If
    End If
    Application.ScreenUpdating = True
   End Sub

[HansV]

Copy the code Rudi posted, but without the first line Private Sub... and without the last line End Sub.
Paste it into the code you already have, below the line Application.ScreenUpdating = False.

[adam]

Thanks for the help Hans.

I'm having the following code to show up the print diaglog

Code: Select all

Sub ShowPrintDialog()
Application.Dialogs(xlDialogPrint).Show
Application.ScreenUpdating = True
End Sub

What I want is, help to modify the following code with the help of the above code so that when the macro button is clicked the code would first hide the empty rows form the range mentioned in the code and then show the print dialog.

Moreover, if there are no empty rows withing the range mentioned in the code of the active sheet I want the code to directly show the print dialog.

Code: Select all

Sub HideEmptyRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rw As Long
For rw = 16 To 35
Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")
Next rw
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I hope I have made my question clear.

[StuartR]

All you need to do is to add the line

Code: Select all

Application.Dialogs(xlDialogPrint).Show

to your second macro, just before the line

Code: Select all

Application.ScreenUpdating = True

Alternatively, you could add the line

Code: Select all

HideEmptyRows

immediately after

Code: Select all

Sub ShowPrintDialog()

in your first macro.

[adam]

Thanks StuartR for the help & the modification. But with the following modification I'm getting the error message "sub or function not identified"

Code: Select all

Sub ShowPrintDialog()
HideEmptyRows
Application.Dialogs(xlDialogPrint).Show
Application.ScreenUpdating = True
End Sub

I would be happy if you could point out what have been done wrong in here.

[HansV]

Stuart assumed that the macro HideEmptyRows is present in a module in your workbook.
If not, you will have to add it.

[adam]

If I may ask Where should I add it

[HansV]

In any module.

[adam]

Hans, are you specifying that I should add a macro to any module as follows

Code: Select all

Sub()
HideEmptyRows
End Sub

[StuartR]

Hans, are you specifying that I should add a macro to any module as follows

Code: Select all

Sub()
HideEmptyRows
End Sub

Adam,

You ALREADY have a macro called HideEmptyRows in Post 12770 above. You just need to include this macro into a module.

[adam]

Well, I have included the MACRO HIDE EMPTY ROWS long time back into a module. This macro on click hides empty rows from the given range of the active sheet.

Having this macro I thought I would modify it with the Sub ShowPrintDialog() macro so that when I press the print macro all the empty rows of from the given range of the sheet gets hidden and then the print dialog opens. So I raised the question on Post 12770.

And according to the answer I got from Stuart, I modified the two codes into one as follows.

Code: Select all

Sub PrintDialog()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rw As Long
For rw = 26 To 35
Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")
Next rw
Application.Calculation = xlCalculationAutomatic
Application.Dialogs(xlDialogPrint).Show
Application.ScreenUpdating = True
End Sub

What I got confused later was the part of the answer in which, Stuart had said as

Alternatively, you could add the line
HideEmptyRows
Immediately after
Sub ShowPrintDialog()

By mentioning HideEmptyRows Stuart didn’t mention () part. That’s why I got confused.

But now I have figured out what Stuart has mentioned. It’s the () thing that had made the confusion.

Anyway thanks for the help. And I really appreciate that.

[adam]

The code posted in Post=12796 Shows print dialog by hiding empty rows from the active sheet.
If this code is to be changed so that it hides only the rows from the sheet “Previous”
How should I change it?

[HansV]

Change the instances of Range(...) to Worksheets("Previous").Range(...)

[adam]

Thanks Hans. It works as required.