Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows variable row length
This started as an issue to set the scroll area to a static width (column a
to c) and a dynamic length (row 1 to end of data) which I never really got to work well. I am fairly handy with Macro's now, but am pretty clueless when it comes to VB. Most everything I have read here says to just hide columns and rows rather than try to set the scroll area, but that still is leaving me with the same basic problem. I can hide the columns since they are static with this bit of macro code Sheets("Sales").Select Columns("D:IV").Select Selection.EntireColumn.Hidden = True but how can I hide rows if my ending row keeps changeing depending on if I insert or delete rows?? I have seen several examples of the countif function used. So I can count the cells that have values, pass that number as a variable to my code and do an EntireRow.Hidden from that point to A65536, but I don't know how to pass that variable or read a value from a cell content into this type of macro coding. It may even be a different RowHidden keyword as far as I know. Rows("156:156").Select (assumes that the last current row with data is 156) Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Hidden = True Is there an easier way to hide these rows with a for/next or do/until kind of routine to find the ending row with data. (six of my rows between A1 and A156 are blank, so I planned to just do an +6, kind of modifier to the final variable to make sure I don't come up short on my data list and hide 6 rows by accident) For me simple is better, this is just a simple workbook that cuts/copy/pastes/sorts and sets some cell formats from one page to another then hides rows, columns and sheets that end users don't need to be concerned with. I have included snippets to disable the right click menu, shortcuts like ctrl+v and ctrl+x and disabled the 'save as' option as well. Protection is also enabled. All these things are to make the locked version of the front sheet for end users to only imput data into one column and return the sheet without any other changes (so my macro won't blow up upon execution) A long-winded explanation for a simple problem I am sure. So the more complete and detailed of a solution you can provide me would be wonderful. Or even ideas that put back on the right track, if i am heading down a path of darkness will be appreciated. as always, thanks in advance Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows variable row length
If you want to try changing the scrollarea, you could try this:
Option Explicit Sub Auto_Open() Dim LastRow As Long With Worksheets("Sales") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .ScrollArea = "A1:C" & LastRow End With End Sub If you want to try hiding the rows after the last used row in column A: Option Explicit Sub Auto_Open() Dim NextRow As Long With Worksheets("Sales") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Rows(NextRow & ":" & .Rows.Count).Hidden = True End With End Sub Essentially, both routines start at A65536 (in xl2003) and then go up to the last used cell in column A. anduare2 wrote: This started as an issue to set the scroll area to a static width (column a to c) and a dynamic length (row 1 to end of data) which I never really got to work well. I am fairly handy with Macro's now, but am pretty clueless when it comes to VB. Most everything I have read here says to just hide columns and rows rather than try to set the scroll area, but that still is leaving me with the same basic problem. I can hide the columns since they are static with this bit of macro code Sheets("Sales").Select Columns("D:IV").Select Selection.EntireColumn.Hidden = True but how can I hide rows if my ending row keeps changeing depending on if I insert or delete rows?? I have seen several examples of the countif function used. So I can count the cells that have values, pass that number as a variable to my code and do an EntireRow.Hidden from that point to A65536, but I don't know how to pass that variable or read a value from a cell content into this type of macro coding. It may even be a different RowHidden keyword as far as I know. Rows("156:156").Select (assumes that the last current row with data is 156) Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Hidden = True Is there an easier way to hide these rows with a for/next or do/until kind of routine to find the ending row with data. (six of my rows between A1 and A156 are blank, so I planned to just do an +6, kind of modifier to the final variable to make sure I don't come up short on my data list and hide 6 rows by accident) For me simple is better, this is just a simple workbook that cuts/copy/pastes/sorts and sets some cell formats from one page to another then hides rows, columns and sheets that end users don't need to be concerned with. I have included snippets to disable the right click menu, shortcuts like ctrl+v and ctrl+x and disabled the 'save as' option as well. Protection is also enabled. All these things are to make the locked version of the front sheet for end users to only imput data into one column and return the sheet without any other changes (so my macro won't blow up upon execution) A long-winded explanation for a simple problem I am sure. So the more complete and detailed of a solution you can provide me would be wonderful. Or even ideas that put back on the right track, if i am heading down a path of darkness will be appreciated. as always, thanks in advance Martin -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows variable row length
Thanks Dave,
It works beautifully. The scroll routine worked so well I decided to not even hide the rows. But I think I found a way to use that hide routine in another part of my code to fix my next problem. Thanks again Martin "Dave Peterson" wrote: If you want to try changing the scrollarea, you could try this: Option Explicit Sub Auto_Open() Dim LastRow As Long With Worksheets("Sales") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .ScrollArea = "A1:C" & LastRow End With End Sub If you want to try hiding the rows after the last used row in column A: Option Explicit Sub Auto_Open() Dim NextRow As Long With Worksheets("Sales") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Rows(NextRow & ":" & .Rows.Count).Hidden = True End With End Sub Essentially, both routines start at A65536 (in xl2003) and then go up to the last used cell in column A. anduare2 wrote: This started as an issue to set the scroll area to a static width (column a to c) and a dynamic length (row 1 to end of data) which I never really got to work well. I am fairly handy with Macro's now, but am pretty clueless when it comes to VB. Most everything I have read here says to just hide columns and rows rather than try to set the scroll area, but that still is leaving me with the same basic problem. I can hide the columns since they are static with this bit of macro code Sheets("Sales").Select Columns("D:IV").Select Selection.EntireColumn.Hidden = True but how can I hide rows if my ending row keeps changeing depending on if I insert or delete rows?? I have seen several examples of the countif function used. So I can count the cells that have values, pass that number as a variable to my code and do an EntireRow.Hidden from that point to A65536, but I don't know how to pass that variable or read a value from a cell content into this type of macro coding. It may even be a different RowHidden keyword as far as I know. Rows("156:156").Select (assumes that the last current row with data is 156) Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Hidden = True Is there an easier way to hide these rows with a for/next or do/until kind of routine to find the ending row with data. (six of my rows between A1 and A156 are blank, so I planned to just do an +6, kind of modifier to the final variable to make sure I don't come up short on my data list and hide 6 rows by accident) For me simple is better, this is just a simple workbook that cuts/copy/pastes/sorts and sets some cell formats from one page to another then hides rows, columns and sheets that end users don't need to be concerned with. I have included snippets to disable the right click menu, shortcuts like ctrl+v and ctrl+x and disabled the 'save as' option as well. Protection is also enabled. All these things are to make the locked version of the front sheet for end users to only imput data into one column and return the sheet without any other changes (so my macro won't blow up upon execution) A long-winded explanation for a simple problem I am sure. So the more complete and detailed of a solution you can provide me would be wonderful. Or even ideas that put back on the right track, if i am heading down a path of darkness will be appreciated. as always, thanks in advance Martin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select last 3 rows of variable length coloumn in excel 2003? | Excel Programming | |||
Reading Rows of Variable Length | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming |