Solved

Macro to hide unused items

Posted on 2011-03-14
8
367 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
Comment Utility
Sorry forgot the file
13A-3.xls
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
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
Comment Utility
This comment was for the first comment. I have seen the subsequent comments after posting
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok - let me know how my last post :) goes.

Dave
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
Comment Utility
Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

771 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

15 Experts available now in Live!

Get 1:1 Help Now