Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 "Wanna Learn" wrote: 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 ) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 Wanna Learn wrote: 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 "Wanna Learn" wrote: 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 ) -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 "Dave Peterson" wrote: 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 Wanna Learn wrote: 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 "Wanna Learn" wrote: 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 ) -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 "Wanna Learn" wrote: 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 "Dave Peterson" wrote: 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 Wanna Learn wrote: 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 "Wanna Learn" wrote: 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 ) -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a visible rows
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 Wanna Learn wrote: 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 "Wanna Learn" wrote: 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 "Dave Peterson" wrote: 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 Wanna Learn wrote: 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 "Wanna Learn" wrote: 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 ) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Change PivotItems - Visible/Hide with VBA | Excel Worksheet Functions | |||
Using COUNTIF with visible rows only | Excel Worksheet Functions | |||
How do i analyse visible rows only in excel? | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
How do I limit the visible rows in Excel? | Excel Discussion (Misc queries) |