• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Macro to hide unused items

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
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 4
  • 4
1 Solution
 
Saqib Husain, SyedEngineerAuthor Commented:
Sorry forgot the file
13A-3.xls
0
 
dlmilleCommented:
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
 
dlmilleCommented:
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.

 
dlmilleCommented:
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
 
Saqib Husain, SyedEngineerAuthor Commented:
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
 
Saqib Husain, SyedEngineerAuthor Commented:
This comment was for the first comment. I have seen the subsequent comments after posting
0
 
dlmilleCommented:
ok - let me know how my last post :) goes.

Dave
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now