Hiding/Unhiding Rows

I have a report that has rows that do not need to be shown at all times.  To accomodate this, I have a column with a True/False formula, and a macro that goes through all rows and hides those rows where the formula is false.  This part works just fine, each row is hidden/unhidden in a fraction of a second.  The problem comes after I either print, preview, or change the print settings.  Then it takes 3+ seconds for each row to hide/unhide.  This happens whether I run the hide/unhide macro or if I manually hide/unhide the rows.

Any ideas?  I've never seen anything like this.

Shoe
ShoeBootyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NoggyCommented:
Have you tried putting a breakpoint at the start of the macro? You should then be able to see if your macro is being called after the print et al.. If it is, it may be being called several times as part of an event.

BTW you don't say how you're triggering this macro. I presume it's using a Worksheet Event macro (e.g. Worksheet_Activate()) or something similar?
0
NoggyCommented:
I've simulated your "problem" and I can't duplicate it. As it's just with settings that involve the printer, I think it may be a printer problem.

Does this also happen in other workbooks that don't have this macro after you've selected Print et al.? I've had a PC where opening Page Settings, preview etc. took ages because of a printer problem.
Does this happen to other PCs using the same printer? With your macro workbook running too?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShoeBootyAuthor Commented:
Noggy,

I haven't noticed this problem in other workbooks, but this is the only workbook that I use that requires this type of functionality.  Tomorrow, I'll try it on other PC's some that use the same printer and some that don't.

thanks!

PS - Just for clarification - the macro isn't the problem.  It's the TIME it takes to hide/unhide the rows.  This particular report has 500+ rows on it and if it takes over 3 seconds to hide/unhide each row, well,,,,you can do the math.  To answer your question about triggering the macro, it is assigned to a button.  This particular macro is one of several that are kicked off by the button.  The button will clear my raw data, run a query against an Access database for new raw data, copy that data to the data sheet, calculate because the report and its formulas are vlookups, then go hide the rows that are not needed.

Thanks!
Shoe
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NoggyCommented:
Oh, it takes 3 secs to hide/unhide EACH row? Wow.

Simple question: I presume you have Application.ScreenUpdating = False at the start and Application.ScreenUpdating = True at the end of the macro? Screen updating does take ages for these sorts of things.
0
antratCommented:
Hi ShoeBooty

try this>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
XL: Resetting the Last Cell Fixes Memory/Printing Problems

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a

--------------------------------------------------------------------------------


SUMMARY
Microsoft Excel keeps track of all the cells that you use in a worksheet by using an "activecell" table (also called a "dependency" table). In some cases, the last cell in this table may refer to a cell that is outside of the worksheet area that you are using. When this occurs, random access memory (RAM) or printing problems may result. You may be able to correct these problems by resetting the last cell in the table so that it falls within the area of the sheet that you are actually using.



MORE INFORMATION
This behavior is changed in Excel 97 and Excel 98. When you save the workbook, the last cell pointer is automatically updated to reflect the last cell that contains data.

To reset the last cell address in the versions of Microsoft Excel listed at the beginning of this article, you can use either of the following methods to clear the unnecessary information (data and formatting) from the unused columns and rows of the worksheet. After you remove the extraneous information, save the sheet to force Microsoft Excel to rebuild the "activecell" table.

NOTE: When you use either method, you may receive an "Out of Memory" message as Microsoft Excel attempts to clear and delete the selected range. If you receive this error message, use smaller row or column ranges when you clear data.

Method 1
Use the following steps to reset the last cell by manually deleting excess rows and columns:


Select all rows or columns that do not contain data by selecting the appropriate row or column headings.

TIP: One way to do this is to press F5 and type the appropriate reference (F:IV for columns or 5:16384 for rows).


On the Edit menu, click Clear, and then click All.


On the Edit menu, click Delete.


On the File menu, click Save (click Save As if you want to keep the original file).


Method 2
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
Use the following steps to find and delete all the unused cells:


Type the following code into a Visual Basic module:
      ' Macro code starts here.
      Sub Reset_LastCell()

         ' Save the lastcell and start there.
         Set lastcell = Cells.SpecialCells(xlLastCell)
         ' Set the rowstep and column steps so that it can move toward
         ' cell A1.
         rowstep = -1
         colstep = -1
         ' Loop while it can still move.
         While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
            ' Test to see if the current column has any data in any
            ' cells.
            If Application _
                  .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
                  > 0 Then colstep = 0  'If data then stop the stepping
               ' Test to see if the current row has any data in any cells.
               ' If data exists, stop row stepping.
               If Application _
                     .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
                     > 0 Then rowstep = 0  
                  ' Move the lastcell pointer to a new location.
                  Set lastcell = lastcell.Offset(rowstep, colstep)
                  ' Update the status bar with the new "actual" last cell
                  ' location.
                  Application.StatusBar = "Lastcell: " & lastcell.Address
         Wend
         ' Clear and delete the "unused" columns.
         With Range(Cells(1, lastcell.Column + 1), "IV16384")
            Application.StatusBar = "Deleting column range: " & _
               .Address
            .Clear
            .Delete
         End With
         ' Clear and delete the "unused" rows.
         With Rows(lastcell.Row + 1 & ":16384")
            Application.StatusBar = "Deleting Row Range: " & _
               .Address
            .Clear
            .Delete
         End With
         ' Select cell A1.
         Range("a1").Select
         ' Reset the status bar to the Microsoft Excel default.
         Application.StatusBar = False
      End Sub



Switch to the sheet in which you want to reset the "activecell" table and click Macro on the Tools menu. In the list of macros, click Reset_LastCell, and then click Run.


On the File menu, click Save (if you want to keep the original file, click Save As).





REFERENCES
For additional information about resetting the last cell address in earlier versions of Microsoft Excel, see the following article in the Microsoft Knowledge Base:

Q100406 Excel: Resetting Last Cell Address Fixes Memory/Printing Problems

Additional query words: 5.00a 5.00c large size convert conversion XL5 XL7 kbtshoot

Keywords : kbcode kbprg xlloadsave PgmOthr xlprint
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbinfo


Also you might want to try
Application.Calculation = xlCalculationManual at the start of your code and Application.Calculation = xlCalculationAutomatic at the end.


I assume you are using a For Each statement to hide the rows.


antrat

0
ShoeBootyAuthor Commented:
Here is the code I use to hide/unhide:

For r = 1 to 500
If Cells(r, c) = False Then
Rows(r).Hidden = True
Else
Rows(r).Hidden = False
End If
Next r

I tried this morning - opened the workbook, and went to the first row.  MANUALLY (Format,Row,Hide) hid the row - took a fraction of a second.  Did this several times using undo/redo buttons.  Each time it takes only a fraction of a second.
Then I print previewed the report.

I repeated the above test.  Each time, the row hide/unhide took about 3 seconds.  Long enough for the hourglass to come up and flip a couple of times.

The only visible difference is the print range is now outlined by dashes - don't know if that makes a difference or not.
0
ShoeBootyAuthor Commented:
Noggy and antrat,

This is beginning to look more like a printer problem rather than an excel problem.  

I made a test sheet and repeated my test from earlier this morning.  It acted exactly the same.  (taking longer to hide/unhide after printing)

I uninstalled my printer and tried again.  This time the same workbook hides rows properly.

I'll get with our hardware support person here to make sure I'm using the correct printer drivers and that I have the most current copy.

Noggy, since you were the first to mention checking printer settings, post as answer and I'll award points.

Thanks!
Shoe

0
NoggyCommented:
Cheers, ShoeBooty. Though do bear in mind antrat's suggestion about resetting the last used cell.

To accept a comment as an answer, just go to the comment you want to accept as the answer and there should be an underlined link to the RHS of the comments's title, saying "Accept as Answer" or something like that.
This is EE's (rather sensible) answer to people having to duplicate their comments as answers too (thus taking up more valuable server space).

However, if this doesn't appeal to you, let me know and I'll post  an actual answer.
0
ShoeBootyAuthor Commented:
Noggy,

Here are some comments from a memo I sent to our PC support person:

I have created a spreadsheet that has 175 rows of text in Column C.  Each cell from C1 to C175 contains either TRUE or FALSE.  I have a macro that starts at the top of the column and goes through the entire list.  Each time it finds the cell value to be FALSE, it will hide the row.  This macro is attached to a button labeled "Hide".  There is another macro that will unhide all rows, it is attached to a button labeled "Unhide".

The issue I am seeing is with the time it takes to hide or unhide a row.  For documentation, I timed this process.  I timed hiding one row at a time by manually selecting Format,Row,Hide from Excel's menu bar.  I also timed the duration of the macro.  Here is a chart that shows the time taken:

PC               1Row          Macro
1                  < 1 sec      1 sec
2                   < 1 sec      1 sec
3                  < 1 sec      1 sec
4                  < 1 sec      1 sec

Next, I printed the sheet. Then I ran the time tests again.  Here are the results:

PC               1Row          Macro
1                  2 sec          1 min 32 sec
2                  < 1 sec      5 sec
3                  < 1 sec      7 sec
4                  < 1 sec      5 sec

For purposes of this test, I timed the process on four different configurations.  The configurations are as follows:

PC 1 is Windows 95 with Excel 97 and HP LaserJet 4000 Series PCL 6 printer driver.

PC 2 is Windows NT with Excel 2000 and HP LaserJet 4000 Series PCL 6 printer driver.

PC 3 is Windows NT with Excel 97 and HP LaserJet 5si printer driver.

PC 4 is Windows 95 with Excel 97 and HP LaserJet 5si printer driver.

PC 1 and PC 4 are the same machine, with different printers installed.

As you can see, the time taken has increased dramatically on the machine where I was running Windows 95, Excel 97 and using the HP LaserJet 4000 Series PCL 6 printer driver.  No changes were made to the spreadsheet, or any code during this test.  The only difference between the first test and the second test is that no contact had been made with the printer driver for the first test.

0
antratCommented:
Hi shoebooty

It seems that whenever Excel prints it leaves behind a dotted line on the printed sheet and it seems to be the cause of all your problems as exactly the same thing happens to me on different Pc's and diffferent Printers. That is whenever I try to hide and unhide rows after printing it becomes very slooooow .I have tried all sorts of ways to try and remove this line but The only way I have found that works is to close the Workbook and then re-open it, not the best of ways but it does work.

I have tried to search the MSKB but i'm unable to get through today.

antrat
0
FlorisMKCommented:
Could this simply be a matter of turning off the View Page Breaks option in Tools - Options?

What seems to be happening is that after printing, the page breaks have been calculated and have to be recalculated after every row hide.

So if you turn off the Page Breaks option after printing, performance may increase again...

In a quick test, hiding one row took .25 sec with Page Breaks and .01 sec without...

Use this code to turn off Page Breaks:

ActiveSheet.DisplayPageBreaks = False
0
FlorisMKCommented:
Difference is not so dramatic in my case, but I imagine your sheet has more data, so there are more page breaks to be recalculated.
Also, if this is the case, it is of course printer-dependent...
0
FlorisMKCommented:
' I used a workbook with about four
' pages of random data on sheet 1 and
' two result columns on sheet 2, and
' the following code to automatically
' test the difference:


Option Explicit


Public Sub Test()
   
   Dim i As Integer
   Const TestRuns = 100
   
   For i = 1 To TestRuns
      HideRow PageBreaks:=False
   Next i
   
   For i = 1 To TestRuns
      HideRow PageBreaks:=True
   Next i

End Sub


Public Sub HideRow(PageBreaks As Boolean)
   
   Dim sStartTime As Single, sEndTime As Single
   
   ActiveSheet.DisplayPageBreaks = PageBreaks
   
   sStartTime = Timer
   ActiveCell.EntireRow.Hidden = True
   sEndTime = Timer
   
   ReportTime sEndTime - sStartTime, PageBreaks

   ActiveCell.EntireRow.Hidden = False

End Sub


Private Sub ReportTime(Time As Single, PageBreaks As Boolean)
   Dim rng As Range
   Set rng = Worksheets(2).Range("A1").Offset(0, -PageBreaks)
   Set rng = rng.End(xlDown).Offset(1, 0)
   rng.Formula = Time
End Sub
0
calacucciaCommented:
FlorisMK,

what are you doing ?

Please read previous comments and tips. Actually ShoeBooty invited Noggy to post as an answer. I don't critisize the content of your postings, they look certainly valuable, I just don't like guys/gals jumping on unanswered questions with an answer regardless of question history. You could have said the same with just posting comments. Using the answer tool just gets people angry and causes trouble.

You can reject your own answer, if you don't want to be rejected (which will probably happen anyway), do so.

Calacuccia
0
FlorisMKCommented:
Dear, sweet calacuccia,

I'm proposing an answer that, as far as I can determine, solves the originally posed problem. Isn't that the purpose of Experts Exchange?

PS: ShoeBooty, I sincerely apologize for answering your question, if I shouldn't have. By all means, reject it if you feel you should.
0
antratCommented:
Hi FlorisMK
I think what calacuccia means is that if you are not sure you have an answer to anothers question then the right thing to do is to post it as a comment. and considering you started your answer with:
"Could this simply be a matter of turning off the View Page Breaks option in Tools - Options? "
means that you are not sure :) by locking the question with an "maybe answer" you place it in the not so well visited "Locked Question" zone away from the attention of others.


Anyway the problem goes a little deeper that just removing and or turning off page breaks as I have already tried that.

Cells.PageBreak = xlNone made no diff at all. If you insert a page break after printing the dotted line or page break moves to where the new one has been inserted if you the remove all page breaks it goes back to where it was. As I said it appears the only way to remove it all together is to close and re-open the workbook.

antrat
0
FlorisMKCommented:
OK, antrat,

I'm sure removing _manual_ page breaks does not solve the problem. The problem is not in the _manual_, but in the (display of) _automatic_ page breaks, a Tools|Options|View-setting.
The display of automatic page breaks gets turned on whenever you do anything printer-related with your worksheet, like printing, previewing or changing page setup. And after they have been displayed, any change in the worksheet leads to a recalc of all automatic page breaks, which can take seconds. Turning off automatic page breaks gives you back these seconds.

The behaviour you describe when you turn off Cells.PageBreak confirms this: inserting/removing manual page breaks also forces a recalc of automatic page breaks.
Closing and reopening works as a solution, because, among other things, it turns off ActiveSheet.DisplayPageBreaks.

So I'm sure that turning off this option, either through Tools|Options|View|Page Breaks, or through that one line of code, gives ShoeBooty back his performance.
0
FlorisMKCommented:
To avoid any further annoyance, I'll repost my answer as a comment now, but I do think you're both overreacting, before you've even actually determined if the proposed answer works - it does.

And antrat: to determine that, you need to read it - Cells.PageBreak is something completely different than ActiveSheet.DisplayPageBreaks.
0
antratCommented:
FlorisMK, My apologies , you are quite right, I've learnt something myself. My intention was no to annoy you (sorry if it did).

 I was simply trying to point out the fact that by locking a question with a maybe answer,you will take the attention away from other experts.

antrat
0
FlorisMKCommented:
No harm done, let's see what ShoeBooty thinks of all our bickering... (-:
0
NoggyCommented:
Wow, I've been away for a couple of days and come back to find a plethora of comments :-) . As mentioned previously, it does seem to be a printer driver problem - especially from the results of ShoeBooty's tests. FlorisMK and antrat have both contributed tremendously with their comments and suggestions. Personally, I'm quite good at diagnosing from where problems probably derive but, as far as printers are concerned, I'm not really a fountain of information. This is mainly because I don't have a printer and don't like printing out - anything that does require printing, I send to my work email and use our company's resources :-) .
0
ShoeBootyAuthor Commented:
geezz, guys, I didn't mean to get you guys all riled up at each other.  I REALLY appreciate you three guys stepping up to the plate and helping me knock this problem out.  Here's what I'm going to do:

Noggy - You first brought up the issue of printer drivers - as you can see from the results of my test, I think it is driver/OS specific.  I will accept your comment as an answer for this question.

FlorisMK - Although I must say I agree with antrat and calacuccia about how to post maybe answers as comments, I applaude you for correcting it before I had to.  As far as your solution for turning off page breaks - on my test file it does work IF you don't have the print zoom set for Fit to 1X1 page.  If you set to xx% zoom, turning off the page break option helps tremendously.  I have tested this in my test file, I'll test it in my original file this afternoon.  If the results of that test come out as they have for the test file, I will post another question specifically for you with the same amount of points as I awarded Noggy.

antrat - Thanks for your input.  I, too, had found that closing the workbook and reopening would clear up the problem.  Unfortunately, the original file is one that is distributed nationwide, so I have to find a different solution.  Since I did not make that clear in my question/comments I will post a question specifically for you for one half the points awarded to Noggy and FlorisMK.

Again, thanks to each of you.
0
FlorisMKCommented:
Fair enough & glad the problem's solved now...
0
FlorisMKCommented:
PS: ShoeBooty, if you do decide to award points to me, could you comment about it here? I don't come into the Office (topic area) every day... (-:
0
NoggyCommented:
Cheers, ShoeBooty, for the points and grade  :-) . Have you had any luck with HP and the printer driver?
0
FlorisMKCommented:
And, ShoeBooty? Did it work?
0
ShoeBootyAuthor Commented:
Floris,

Sorry for the delay....I got called out of town on another project and just got back.  I'll run the test today or tomorrow.

Thanks!
Shoe
0
ShoeBootyAuthor Commented:
Floris,

I tested on my main spreadsheet and it seemed to work fine.  Thanks for your help.  Look for your points in a seperate question.

Shoe
0
NEWJACKCommented:
shoebooty,

I am trying to make rows hide when the workbook is opened based on a true/false(if function) formula ... i tried to use the code that you used but keep getting an error can you help?


thanks in advance

NewJack
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.