Solved

Macro to hide unused items

Posted on 2011-03-14
8
368 Views
Last Modified: 2012-05-11
In the attached file I would like to suppress printing of unused items. Unused items are those items whose total is zero.

I would like to have two macros:

- one of them would hide only the data rows and leave the header and total rows visible. eg rows 167 to 168

- the second macro should hide the header and total rows in addition to the data rows. eg rows 166 to 169.

In both cases I would like the macro to be fired automatically only for printing. I know this can be done with the worksheetbeforeprint event. But how can I restore it to all visible once the print is done?

Saqib
0
Comment
Question by:Saqib Husain, Syed
  • 4
  • 4
8 Comments
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 35135305
Sorry forgot the file
13A-3.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35135541
I like to use features already available in Excel, if I can help it, and avoid potentially unneeded macros (adds to maintenance).

So - give this a thought...

If your rows are not changing (just the data in the form), may I suggest you create a View?  You can hide rows/columns and complete print settings where everything looks perfect in print view or test print, then you can SAVE that perspective by creating a Custom View in Excel 2007, that's the VIEW, WORKBOOK VIEWS menu area of the ribbon, then save that with a name.  Then you can create an additional view, where you hide other things and set other print settings, and SAVE that with a name as well.  Finally, a NORMAL view would be created.

You can use a macro, or manually select the VIEW you want to print.  Its simple enough by hand or by macro.

Let me know if this helps or you need further assistance down this path.

See attached...
Dave
13A-3-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35135644
I've gone ahead and added two macros to hide all but header and totals, and one to go back to normal view.

See attached.

I'm a bit confused by "- the second macro should hide the header and total rows in addition to the data rows. eg rows 166 to 169." - wouldn't that be hiding all the rows except for the page header???

Regardless, hopefully, this gives you the solution you're looking for - otherwise, please advise...

Below, see the code for the HeaderTotal View and the Normal View:

Dave


Sub createHeaderTotalView()
Dim myCell As Range, maxRows As Long
Dim myHidden As Range
Dim first As Boolean

Application.ScreenUpdating = False

    maxRows = Range("J" & Rows.Count).End(xlUp).Row
    Range("A1", Range("A" & maxRows)).EntireRow.Hidden = False
    
    first = True
    For Each myCell In Range("A1", Range("A" & maxRows))
        If myCell.Value <> "" Or myCell.Offset(0, 1).Value = "Total" Then
            'must be a header or total row
            'do nothing
        ElseIf Not first Then
            Set myHidden = Union(myHidden, myCell)
        Else
            Set myHidden = myCell
            first = False
        End If
    Next myCell
    
    myHidden.EntireRow.Hidden = True
                        
Application.ScreenUpdating = True
End Sub
Sub createNormalView()
Dim maxRows As Long
Application.ScreenUpdating = True

    maxRows = Range("J" & Rows.Count).End(xlUp).Row
    Range("A1", Range("A" & maxRows)).EntireRow.Hidden = False
    
Application.ScreenUpdating = False
End Sub

Open in new window

0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35135751
Ok - I've improved this a bit.  My assumption is that the macros would be as follows:

For BOTH macros, you want to hide unused items (sorry, I had to read this a couple times :).

FIRST macro - hides all data rows that are "unused", total is zero, but includes header/total rows, regardless of total value

SECOND macro - hides all data rows that are "unused", and header/total rows if they are unused as well...

I'm a bit curious how you want to deal with two consecutive headers, but I made the assumption that we only deal with headers that have detail lines, to see if this suits your needs.

Here's the FIRST macro:
Sub createNoUnusedButHeaderTotalView()
Dim myCell As Range, maxRows As Long
Dim myHidden As Range
Dim first As Boolean

Application.ScreenUpdating = False

    Range("A1", Range("A" & Rows.Count)).EntireRow.Hidden = False 'just in case something is hidden
    maxRows = Range("J" & Rows.Count).End(xlUp).Row
    
    first = True
    For Each myCell In Range("A1", Range("A" & maxRows))
        If myCell.Value <> "" Or myCell.Offset(0, 1).Value = "Total" Then
            'must be a header or total row
            'do nothing
        ElseIf Not first And myCell.Offset(0, 9) = 0 Then 'checks for zero in detail line
            Set myHidden = Union(myHidden, myCell)
        ElseIf myCell.Offset(0, 9) = 0 Then 'checks for zero in detail line
            Set myHidden = myCell
            first = False
        End If
    Next myCell
    
    myHidden.EntireRow.Hidden = True
                        
Application.ScreenUpdating = True
End Sub

Open in new window


And, here's the SECOND macro: You'll notice, I used the same approach for the first part, including Total lines to flag as to be hidden, then I scanned the prospective hidden range, "myHidden" for the Total line (presumes that all detail lines are then zero/unused, then looked upward to find the header for elimination...
Sub createNoUnusedView()
Dim myCell As Range, maxRows As Long
Dim myHidden As Range
Dim first As Boolean

Application.ScreenUpdating = False

    Range("A1", Range("A" & Rows.Count)).EntireRow.Hidden = False 'just in case something is hidden
    maxRows = Range("J" & Rows.Count).End(xlUp).Row
    
    'first pass - just eliminate detail and total records
    first = True
    For Each myCell In Range("A1", Range("A" & maxRows))
        If myCell.Value <> "" Then
            'must be a header or total row
            'do nothing
        ElseIf Not first And myCell.Offset(0, 9) = 0 Then 'checks for zero in detail and total line
            Set myHidden = Union(myHidden, myCell)
        ElseIf myCell.Offset(0, 9) = 0 Then 'checks for zero in detail and total line
            Set myHidden = myCell
            first = False
        End If
    Next myCell
    
    'second pass - eliminate headers
    
    For Each myCell In myHidden 'examine each hidden row to see if line above is header line
        If myCell.Offset(0, 1) = "Total" Then 'so need to eliminate header
            If Not first Then
                Set myHidden = Union(myHidden, myCell.End(xlUp)) 'so go up to the item number
            Else
                Set myHidden = myCell
                first = False
            End If
        End If
    Next myCell
    
    myHidden.EntireRow.Hidden = True
                        
Application.ScreenUpdating = True
End Sub

Open in new window


Finally, the Normal View macro, simply unveils all the hidden rows:
Sub createNormalView()
Dim maxRows As Long
Application.ScreenUpdating = True

    Range("A1", Range("A" & Rows.Count)).EntireRow.Hidden = False 'unhide everything
    
Application.ScreenUpdating = False
End Sub

Open in new window


If this is what you're looking for, its simple enought to add the statements:

Printout
call createNormalView


to the end of each of the macros you desire to print immediately upon selection, then revert back to the normal view.

For your convenience, I've left those two commands out, so you can see the dynamic of the macro without incurring printing costs :)

As you need to SELECT which operation you want, there's no need to tie this to the print macro, as you create the event by selecting the option.  I've put some option buttons on your report which could just as easily be run with a control sequence, from the Developer's ribbon, or from other buttons per your choice as you decide how you want this to work.

I hope you find this useful.

Cheers,

Dave
13A-3-r2.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 35136040
Dave,

Looks like you did not understand the question fully. I need to hide the rows only if the total is zero.

And this can vary.

I have hundreds of such files and need to print only the functional items.

Saqib
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 35136044
This comment was for the first comment. I have seen the subsequent comments after posting
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35136056
ok - let me know how my last post :) goes.

Dave
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 35136568
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now