hide rows variable row length

[anduare2]

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]

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]

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