HIDING ROWS AND COLUMS

[Doo0592]

Hi all,

I have noticed that you can hide all the columns and rows that you are
not using so the user cannot scroll past the relevant part of the
worksheet. I can hide all the column cause there aren't too many of
them when you scroll along but I tried this for the rows and it seems
to just keep going on forever! lol Is there another way I can hide ALL
the rows underneath so the end user can't scroll down?

Cheers

 

[Alan]

Do you want to hide all or would you be happy with restricting the
scroll area?

For the latter simply change the "ScrollArea" property for the
appropriate sheet.

 

[Bob Phillips]

Rows(n & ":" & Rows.Count).Hidden = True

where n is some starting number.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Doo0592]

Ok, showing my nooby colours on this one!

I have tried them both n neither have worked.. where do I put them and
do they need a private sub?

Doo

 

[Alan]

No sub required to restrict the scroll area ...

Go into the VBA editor but ensure that the Project and Properties
windows are visible. In the Project window click on and highlight the
sheet you want to restrict (e.g. Sheet1) and then scroll down to
"ScrollArea" in the Properties window. Click there and enter the range
you want to restrict to (e.g. A1:F6)

Good Luck!

 

[Doo0592]

Ahhh, silly me! Never thought to look there!

Thanks Alan x

 

[Doo0592]

Mission complete... nearly!

Ahhh, silly me! Never thought to look there!

Thanks Alan x

 

[Don Guillett]

You will have to do this each time you re-open so best to use a
workbook_open macro in the ThisWorkbook module to do it for you.

 

[Bob Phillips]

or use code as I suggested <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Doo0592]

Yeah, just noticed that

K, so I wrote this code and put in it ThisWorkbook module but nothing
happens?

Sub workbook_open()

Worksheets("sheet2").rows(30 & ":" & rows.Count).Hidden = True
Worksheets("sheet2").Columns(p & ":" & Columns.Count).Hidden = True

End Sub

Wot have I done wrong?

 

[Bob Phillips]

Sub workbook_open()

With Worksheets("sheet2")
.Rows(30 & ":" & .Rows.Count).Hidden = True
.Columns(16).Resize(, .Columns.Count - 16 + 1).Hidden = True
End With

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Doo0592]

How do I compensate for having two workbook_open() subs in the
ThisWorkbook module?

Doo

 

[Bob Phillips]

You can't, you have to merge them.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Doo0592]

Hi Bob,

I found a way to hide all the rows and columns I don't need without
using VB code. It's nice n easy:

Select first column -> crtl + shift + then left or right to select all
-> right click, hide.
Select first row -> ctrl + shift + then up or down to select all ->
right click, hide.

thanks for your help thou

Doo

 

[Alan]

Oops! Forgot about that but if you still would like to go down this
route you just need the line

Worksheets("Sheet2").ScrollArea = "$A$1:$P$30"

in your workbook_open macro ... or you can hide the columns.

 

[Doo0592]

Thanks Alan,

You'll never guess that after all the posts i've written over the past
week the workbooks just gone n corrupted itself My IT department
says there's too much in but I even tried copy n pasting it into a new
one sheets by sheet to see how the file size grew n when i got to the
second one it shot from 17.5Kb to 11.1MB... damn putas