Hide a visible rows

[Guest]

Hello
Is it possible to hide all visible rows (except row 1) evertime the workbook
is saved and closed . (I want to later on unhide the rows and create a pivot
table )

 

[Guest]

Hello Let me clarify my problem. I created a form that has 8 columns .
Whenever a sales rep makes an error they fill out the form and send a copy to
3 other people. After the sales rep makes the entries in the form and sends
it out they save and close the file . What I want to do is when the file is
saved and closed to hide the row with the information, so when the next
person opens the form it appears empty.(I want the first row with the
labels to be visible. ) So by hiding them I can create a pivot table at the
end of the month Thank you

 

[Dave Peterson]

Instead of hiding the rows, maybe just using data|Table would be a better way to
allow the users to input data.

=======
But if you want to hide the rows, I think hiding them when the workbook is
opened will be an easier design.

If you hide them when the workbook is saved, it might irritate the current user
if he/she still needs to see that data.

If you hide them when the workbook is closed, then you'll have to save that
workbook. And if the user made a massive error and wanted to close without
saving, you may be saving that massive error.

I'd use a macro that hides the rows when the workbook is opened:

Option Explicit
Sub Auto_Open()
With Worksheets("sheet9999")
.Select 'make it the active sheet
.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)).EntireRow.Hidden = True
End With
End Sub

This goes in a General Module--not behind a worksheet and not behind
ThisWorkbook.

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

 

[Guest]

Dave Peterson
Thank you for your thoughts and clear explanation. I did as you suggested
used the macro that hides the rows when the workbook is opened.
FANTASTIC....you're great thank you

 

[Guest]

Hello I'm having a problem with the macro below. Everything is fine until
.....If the file is opened and no entries are made ,the file is save, the next
time the file opens the first line or row a is hidden . ??? Help thanks

 

[Dave Peterson]

I expected a little data in column A.

Try this instead:

Option Explicit
Sub Auto_Open()
Dim LastRow As Long
With Worksheets("sheet9999")
.Select 'make it the active sheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRow > 1 Then
.Range("a2:A" & LastRow).EntireRow.Hidden = True
End If
End With
End Sub