Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Macro to hide unused items

Posted on 2011-03-14
8
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 43

Author Comment

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

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 42

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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
 
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 42

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

618 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