URGENT!! Use macro button to hide empty columns and rows...HELP!!!!

[Hawk]

Can anyone provide help with a time sensitive request?? Before I
begin, I have to say that I am an Accountant, not a programmer, so
please keep that in mind if/when you respond...

I have a workbook that contains two worksheets. One is a data entry
sheet that users will input employee data into and the other sheet is
the actual report that contains all formulas that pull info from the
data entry page. On the data entry page, the sheet begins with 30
columns that the user will input employee information into. One column
represents one employee. However, all 30 columns are not always
needed. I would like to create a macro (with a button) that will hide
the empty columns just for the purposes of printing the document so
that empty columns are not printed on the page. (Note: The empty
columns do contain formulas.)

I've searched for solutions on the web and I've come up with the
following, however, when I run the macro all 30 columns get hidden even
if there is data in them:

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82
Sheets("Box").Select
Dim col As Range
For Each col In Columns("C:AF")
If Application.Count(Range(Cells(4, col.Column), Cells(8, col.Column)))
= 0 Then
col.Hidden = True
End If
Next
End Sub

Also, all the data entered on the data entry page links to another
sheet, which is the report. However, the report contains employee
information in the rows, instead of columns. So, one row represents an
employee. I need a macro (with a button) that will hide the empty rows
for printing purposes so that blank rows will not appear on the report.
The only problem is that there is a formula in EVERY cell on the
report. So I need something that will look at column "A" and if the
formula result is "0" it will hide the entire row. I don't have a
possible solution for this one because I'm stuck on the first problem.

I really hope that I was clear. Anyone's help would be greatly
appreciated. I really need to have this done by tomorrow, if possible.
THANK YOU!!!

 

[Norman Jones]

Hi Hawk,

Some questions:

(1) If each employee column is for data entry, with calculations being
effected on the report sheet, why are there formulas in the employee column?

(2)Are the columns which are not 'empty' (i.e. have data entered)
contiguous?

(3) Does each non-empty employee column neccessarily have data in a given
row, such row otherwise being completely empty (no data, nor formula)?

For your Report sheet, you can autofilter, setting a custom filter criterion
for column A to Not equal to 0. This will hide the rows that you do not wish
to print.

 

[Kurt]

Here's a quick hack. It's dirty but it works. You can modify the parameters
as you see fit.

....kurt

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82
'Sheets("Box").Select
Dim col, row As Integer
Dim hideme As Boolean
For col = 1 To 5 'Columns "A" - "E"
hideme = True
For row = 10 To 82
If Cells(row, col).Value <> "" And Cells(row, col).Value <> 0 Then
hideme = False
End If
Next row
If hideme Then
Columns(col).Select
Selection.EntireColumn.Hidden = True
End If
Next col
End Sub

 

[Rowan]

Your macro says it's purpose is to hide columns with no data in rows 10:82
but actually checks rows 4:8. Change the line:

If Application.Count(Range(Cells(4, col.Column), Cells(8, col.Column))) = 0
Then

to read

If Application.Count(Range(Cells(10, col.Column), Cells(82, col.Column))) =
0 Then

(watch out for the line wrapping)

For your second request autofiltering the data where A does not equal 0
would be the easiest way to do it but if you want a macro to hide the rows
use something like this:

Sub hide_rows()

Dim endRow As Long
Dim ColA As Range
Dim Cell As Range

Sheets("Report").Select
endRow = Cells(Rows.Count, 1).End(xlUp).Row
Set ColA = Range(Cells(2, 1), Cells(endRow, 1))
For Each Cell In ColA
If Cell.Value = 0 Then
Cell.EntireRow.Hidden = True
End If
Next Cell

End Sub

Hope this helps
Rowan

 

[Hawk]

Hi Kurt,

Forgive me but can you please tell me how to modify my parameters to
meet my needs (Again, I'm not a programmer)

For the following line of code: If Cells(row, col).Value <> "" And
Cells(row, col).Value <> 0 Then ...what do I insert for each
"(row,col)"??

 

[Hawk]

Norman,

Answers:

(1) The data entry sheet also serves a detailed report. There are
formulas to sum certain figures in the columns. (e.g. 1 - the total
cash receipts of one employee, 2 - the total credit card receipts,
etc.)

(2) Yes the non-empty columns are contiguous. Columns A and B of the
sheet will always need to be printed. Columns C thru AF are the
columns that may or may not need to be hidden, depending on the number
of employees that work in a given day. Columns AG and AH have
cumulative totals that sum each row and will always be printed, as
well.

(3) In the non-empty columns, there may or may not be data in every
row. It depends on the employee. However, I don't want to hide any
rows on the data entry sheet. Even if a column has data in one row
from rows 10 thru 82, I want the column to be visble. I only want to
hide the column if there is no data (except the sum formulas - and
their result is zero) in every row from 10 thru 82.

 

[Norman Jones]

Hi Hawk,

Try:

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82

Application.ScreenUpdating = False
With Sheets("Box")
Dim col As Range
For Each col In .Range("C10:AF82").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0

Next
End With
Application.ScreenUpdating = True
End Sub

 

[Hawk]

Thanks Norman!!!

It worked...two more questions:

(1) Actually, the macro worked before I protected my worksheet,
however, once I apply the protection I get a run time error because I
have set the protection settings to not allow the hiding of columns or
rows.

(2) I only want to hide the empty columns for the purposes of printing,
can you tell me what to add to the end of the code so that the hidden
columns become visible again after printing?

Can you insert the necessary lines of code that I need to accomplish
the 2 points above and paste the entire routine in your response?
Also, I plan to link this macro to a button that will be on the data
entry sheet. Currently, I have the code on Module 1...is that where it
should be for printing purposes?

Thanks again for you help!!! You just don't know how many countless
hours I've spent on this...

 

[Norman Jones]

Hi Hawk,

Insert a standard module into the workbook holding the data and reporting
sheets and copy / paste the following code:

Option Explicit
Public Const PWORD As String = "opensaysme" '<<====CHANGE!!

'================================>>
Sub PrintDataSheet()
With ThisWorkbook.Sheets("Box")
.Unprotect Password:=PWORD
Call Hide_EmptyColumns
.PrintOut
Call Unhide_EmptyColumns
.Protect Password:=PWORD
End With
End Sub
'<<=================================
'=================================>>
Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82
Dim col As Range
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Box")

For Each col In .Range("C10:AF82").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0
Next

End With
Application.ScreenUpdating = True
End Sub
'================================>>
'<<================================
Sub Unhide_EmptyColumns()
'To Unhide columns with no data in rows 10:82

Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Box")
.Range("C10:AF82").Columns. _
EntireColumn.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

'=================================>>

Add a Commandbutton (from the Controls Toolbar) to the Box sheet. Give the
CommandButton a suitable caption (Print Report, say). DoubleClick the button
and between the lines:
Private Sub CommandButton1_Click()
and
End Sub

insert the line:

Call PrintDataSheet

Hit Alt-F11 to return to Excel.

Press the button ... Read the printed report.


PS Make sure that you replace the password from "opensaysme" to your own
password text.