Hi. I have created a spread sheet that is over 100 columns wide and 85 rows
high. This 100 x 80 range draws its information from another worksheet
within my workbook, however, when i print it i would like it to print all off
the data to the left of the page. For example, if there is data in columns
D, E AND F, no data in G, H and I, and then data again in J, K and L, I want
it to ignore G, H and I when it prints so that columns F and J are side by
side. I hope this makes sense. I can provide you with a copy of the
document if you need it so you can see for yourself what it is i am trying to
achieve.

Many thanks
Matt

Matt

Select any columns with no data and FormatColumnHide.

They will not print.


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 18:23:02 -0700, Matt wrote:

Hi. I have created a spread sheet that is over 100 columns wide and 85 rows
high. This 100 x 80 range draws its information from another worksheet
within my workbook, however, when i print it i would like it to print all off
the data to the left of the page. For example, if there is data in columns
D, E AND F, no data in G, H and I, and then data again in J, K and L, I want
it to ignore G, H and I when it prints so that columns F and J are side by
side. I hope this makes sense. I can provide you with a copy of the
document if you need it so you can see for yourself what it is i am trying to
achieve.

Many thanks
Matt

Many thanks Gord.

I may use this way if i cannot find a more efficient way, but could you tell
me if there is a way that excel can scan for these empty columns
automatically and hide them? Basically, the spreadsheet is designed to log
wakeup calls for a hotel, so Column A has a list of times ranging from 4:00am
to 11:00am with 5 minute intervals, then the next 100 columns are allocated
to each bedroom in the hotel. The formulas i have entered will scan another
worksheet in my workbook for a wakeup call and then flag up the room number
in the appropriate column on the row that matches the time requested
(confused yet?). Now, if for example, room 170 (column M) and room 70
(Column D) both request a call at 7:00am, there are another 8 columns between
these room, if those rooms request calls at a different time to 7:00am, then
there will be a huge gap between rooms 70 and 170. I am tryin to make that
gap disappear so that it is easier to read. I am not fussed about how it
looks on screen, it's only when it prints that it needs to be easy to read.
I'm sorry for being a pain in the a** but i appreciate your input. :P

Kind regards
Matt

"Gord Dibben" wrote:

Matt

Select any columns with no data and FormatColumnHide.

They will not print.


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 18:23:02 -0700, Matt wrote:

Hi. I have created a spread sheet that is over 100 columns wide and 85 rows
high. This 100 x 80 range draws its information from another worksheet
within my workbook, however, when i print it i would like it to print all off
the data to the left of the page. For example, if there is data in columns
D, E AND F, no data in G, H and I, and then data again in J, K and L, I want
it to ignore G, H and I when it prints so that columns F and J are side by
side. I hope this makes sense. I can provide you with a copy of the
document if you need it so you can see for yourself what it is i am trying to
achieve.

Many thanks
Matt

Matt

One question.

Must the entire column be blank before it is hidden?

If so, this code by Jim Thomlinson works for me.

You said you had 85 rows and 100 columns so code is amended to those specs.

Sub SelectBlanks()
'Jim Thomlinson Aug 30, 2006
Dim rng As Range
Dim rngAll As Range
Dim l As Long
Set rng = Range("A1:A85")
For l = 1 To 100
If Application.WorksheetFunction.CountBlank(rng) _
= rng.Cells.Count Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Hide These??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Hidden = True
End If
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.

OR change it to a BeforePrint sub in Thisworkbook and it will run when you
print.


Gord

On Wed, 6 Sep 2006 19:28:01 -0700, Matt wrote:

Many thanks Gord.

I may use this way if i cannot find a more efficient way, but could you tell
me if there is a way that excel can scan for these empty columns
automatically and hide them? Basically, the spreadsheet is designed to log
wakeup calls for a hotel, so Column A has a list of times ranging from 4:00am
to 11:00am with 5 minute intervals, then the next 100 columns are allocated
to each bedroom in the hotel. The formulas i have entered will scan another
worksheet in my workbook for a wakeup call and then flag up the room number
in the appropriate column on the row that matches the time requested
(confused yet?). Now, if for example, room 170 (column M) and room 70
(Column D) both request a call at 7:00am, there are another 8 columns between
these room, if those rooms request calls at a different time to 7:00am, then
there will be a huge gap between rooms 70 and 170. I am tryin to make that
gap disappear so that it is easier to read. I am not fussed about how it
looks on screen, it's only when it prints that it needs to be easy to read.
I'm sorry for being a pain in the a** but i appreciate your input. :P

Kind regards
Matt

"Gord Dibben" wrote:

Matt

Select any columns with no data and FormatColumnHide.

They will not print.


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 18:23:02 -0700, Matt wrote:

Hi. I have created a spread sheet that is over 100 columns wide and 85 rows
high. This 100 x 80 range draws its information from another worksheet
within my workbook, however, when i print it i would like it to print all off
the data to the left of the page. For example, if there is data in columns
D, E AND F, no data in G, H and I, and then data again in J, K and L, I want
it to ignore G, H and I when it prints so that columns F and J are side by
side. I hope this makes sense. I can provide you with a copy of the
document if you need it so you can see for yourself what it is i am trying to
achieve.

Many thanks
Matt



Gord Dibben MS Excel MVP

Thanks Gord. I have absoloutly no clue about macros, but i'm gonna have a
bash anyway. Thankyou so much for your help and i'll let you know how i get
along.

Wish me luck :P

Matt

"Gord Dibben" wrote:

Matt

One question.

Must the entire column be blank before it is hidden?

If so, this code by Jim Thomlinson works for me.

You said you had 85 rows and 100 columns so code is amended to those specs.

Sub SelectBlanks()
'Jim Thomlinson Aug 30, 2006
Dim rng As Range
Dim rngAll As Range
Dim l As Long
Set rng = Range("A1:A85")
For l = 1 To 100
If Application.WorksheetFunction.CountBlank(rng) _
= rng.Cells.Count Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Hide These??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Hidden = True
End If
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.

OR change it to a BeforePrint sub in Thisworkbook and it will run when you
print.


Gord

On Wed, 6 Sep 2006 19:28:01 -0700, Matt wrote:

Many thanks Gord.

I may use this way if i cannot find a more efficient way, but could you tell
me if there is a way that excel can scan for these empty columns
automatically and hide them? Basically, the spreadsheet is designed to log
wakeup calls for a hotel, so Column A has a list of times ranging from 4:00am
to 11:00am with 5 minute intervals, then the next 100 columns are allocated
to each bedroom in the hotel. The formulas i have entered will scan another
worksheet in my workbook for a wakeup call and then flag up the room number
in the appropriate column on the row that matches the time requested
(confused yet?). Now, if for example, room 170 (column M) and room 70
(Column D) both request a call at 7:00am, there are another 8 columns between
these room, if those rooms request calls at a different time to 7:00am, then
there will be a huge gap between rooms 70 and 170. I am tryin to make that
gap disappear so that it is easier to read. I am not fussed about how it
looks on screen, it's only when it prints that it needs to be easy to read.
I'm sorry for being a pain in the a** but i appreciate your input. :P

Kind regards
Matt

"Gord Dibben" wrote:

Matt

Select any columns with no data and FormatColumnHide.

They will not print.


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 18:23:02 -0700, Matt wrote:

Hi. I have created a spread sheet that is over 100 columns wide and 85 rows
high. This 100 x 80 range draws its information from another worksheet
within my workbook, however, when i print it i would like it to print all off
the data to the left of the page. For example, if there is data in columns
D, E AND F, no data in G, H and I, and then data again in J, K and L, I want
it to ignore G, H and I when it prints so that columns F and J are side by
side. I hope this makes sense. I can provide you with a copy of the
document if you need it so you can see for yourself what it is i am trying to
achieve.

Many thanks
Matt



Gord Dibben MS Excel MVP

OK, I've had no joy :'(

I'll try and explain exactly how to spreadsheet is laid out and what i'm
trying to achieve. Maybe there is an easier way to get my goal.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

.....
.....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

Matt

Too involved for this old guy late at night.

I will save this post and perhaps have a look later tomorrow.

Gord

On Thu, 7 Sep 2006 15:37:02 -0700, Matt wrote:

OK, I've had no joy :'(

I'll try and explain exactly how to spreadsheet is laid out and what i'm
trying to achieve. Maybe there is an easier way to get my goal.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

....
....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

Gord Dibben MS Excel MVP

Thankyou Gord, I appreciate your help. I have posted the same query in
another part of the newsboard, so hopefully i will get some joy. :))

Matt

"Gord Dibben" wrote:

Matt

Too involved for this old guy late at night.

I will save this post and perhaps have a look later tomorrow.

Gord

On Thu, 7 Sep 2006 15:37:02 -0700, Matt wrote:

OK, I've had no joy :'(

I'll try and explain exactly how to spreadsheet is laid out and what i'm
trying to achieve. Maybe there is an easier way to get my goal.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

....
....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

Gord Dibben MS Excel MVP