HIDING ROWS AND COLUMS
 

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 :)

HIDING ROWS AND COLUMS
 

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.


Doo0592 wrote:
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 :)

HIDING ROWS AND COLUMS
 

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

Mission complete... nearly! :)


Doo0592 wrote:

Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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 wrote:
or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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" wrote in message
oups.com...
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 wrote:
or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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

Doo

Bob Phillips wrote:
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" wrote in message
oups.com...
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 wrote:
or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

You can't, you have to merge them.

--
HTH

Bob Phillips

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

"Doo0592" wrote in message
oups.com...
How do I compensate for having two workbook_open() subs in the
ThisWorkbook module?

Doo

Bob Phillips wrote:
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" wrote in message
oups.com...
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 wrote:
or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message

oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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

Bob Phillips wrote:
You can't, you have to merge them.

--
HTH

Bob Phillips

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

"Doo0592" wrote in message
oups.com...
How do I compensate for having two workbook_open() subs in the
ThisWorkbook module?

Doo

Bob Phillips wrote:
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" wrote in message
oups.com...
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 wrote:
or use code as I suggested <g

--
HTH

Bob Phillips

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

"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message

oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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.


Don Guillett wrote:
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)

HIDING ROWS AND COLUMS
 

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 :(

Doo
Alan wrote:
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.


Don Guillett wrote:
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.

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
oups.com...
Ahhh, silly me! Never thought to look there! :)

Thanks Alan x


Alan wrote:

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 wrote:
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


Bob Phillips wrote:

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" wrote in message
oups.com...
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 :)