Hide and printout area conditions
 

Hi all,

I´m trying to "clean" the worksheet from unused rows before printing
and then get a print out that only shows whats necessary. The "cleaned
area is different every time, depending wether there are numbers i
them or not. I´m using the code below, (thanks NOSPAM), but I get
very long "answertime" from this code. Is there anyway to modify it t
work from D5:D206? I think it would go faster if the area is specifie
to D5:D206, than to the bottom row, or.......?

I assume I have to modify it more than just change the area B:B
becouse that doesnt work.

Thanks in advance!

//Thomas


Sub CleanUporg()
Dim CurrentRow As Long
Dim UsedRows As Range
On Error GoTo Abort
Set UsedRows = ActiveSheet.UsedRange.Rows
For CurrentRow = UsedRows.Rows.Count To 1 Step -1
I
Application.WorksheetFunction.Sum(UsedRows.Rows(Cu rrentRow).Columns("B:B")
= 0 Then
UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
Else: UsedRows.Rows(CurrentRow).EntireRow.Hidden = False
End If
Next CurrentRow
' If only hiding for printing purposes, use the next two lines to prin
or
'preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Su

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

I'm confused. What exactly has to be true in order to hide a row?

--
Jim Rech
Excel MVP
"Jonsson " wrote in message
...
| Hi all,
|
| I´m trying to "clean" the worksheet from unused rows before printing,
| and then get a print out that only shows whats necessary. The "cleaned"
| area is different every time, depending wether there are numbers in
| them or not. I´m using the code below, (thanks NOSPAM), but I get a
| very long "answertime" from this code. Is there anyway to modify it to
| work from D5:D206? I think it would go faster if the area is specified
| to D5:D206, than to the bottom row, or.......?
|
| I assume I have to modify it more than just change the area B:B,
| becouse that doesnt work.
|
| Thanks in advance!
|
| //Thomas
|
|
| Sub CleanUporg()
| Dim CurrentRow As Long
| Dim UsedRows As Range
| On Error GoTo Abort
| Set UsedRows = ActiveSheet.UsedRange.Rows
| For CurrentRow = UsedRows.Rows.Count To 1 Step -1
| If
|
Application.WorksheetFunction.Sum(UsedRows.Rows(Cu rrentRow).Columns("B:B"))
| = 0 Then
| UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
| Else: UsedRows.Rows(CurrentRow).EntireRow.Hidden = False
| End If
| Next CurrentRow
| ' If only hiding for printing purposes, use the next two lines to print
| or
| 'preview then restore the worksheet
| 'ActiveWindow.SelectedSheets.PrintPreview
| 'ActiveSheet.Rows.EntireRow.Hidden = False
| Abort:
| End Sub
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Hide and printout area conditions
 

Hi,

I have column C, it´s always contains values and formulas.
From C5:C206
But, when column D, which contains formulas with lookupfunction don
generate a value, depending on whats in col C, I want that specifi
row/rows to be hidden.

Hope you understand what I mean!

Thanks in advance!!

//Thoma

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

Hi,
hide/show rows depending on value in column D:

Sub hide_rows()
For i = 5 To 206 'rows
If Range("D" & i).Value = 0 Then
Rows(i).EntireRow.Hidden = True
Else
Rows(i).EntireRow.Hidden = False
End If
Next i
End Sub

or shorter version

Sub hide_rows2()
For i = 5 To 206
Rows(i).EntireRow.Hidden = Range("D" & i).Value = 0
Next i
End Sub

Jare

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

Hi Jarek

Thanks for your help!

But it seems to be an error in your code. I get "undifinied variabel
as error messege. I think there is "For i" that is not definie
correctly.
Can you or somebody else help me with this, please?
I think we are on the right track here.

//Thoma

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

Hi,

add variable declaration at the first line:

Sub hide_rows2()
Dim i As Long
For i = 5 To 206
Rows(i).EntireRow.Hidden = Range("D" & i).Value = 0
Next i
End Sub

Jare

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

Hi, Jared! And all you out there

The code you, Jared, gave me works nice!
Thanks!
No, is it possible to do the same procedure but let the macro chec
several different columns, lets say 7 of them?

I have tried to write for instance"E", "G", "T" columns, instead o
just "E", but that dont work, of course.

Any idea´s?

//Thoma

--
Message posted from http://www.ExcelForum.com

Hide and printout area conditions
 

I'd like to propose another approach that you may or may not like...

Off to the right side of your data you could have a column of formulas that
determine whether the row is to be hidden. Let's say that column Z is out
of sight, so in cell Z5 you enter this formula:

=IF(SUM(D5,F5,G5)=0,TRUE,1)

You can include all the cells you want to check on row 5. Then copy this
formula from Z5 all the way to Z206. If the formula returns True the row is
be hidden, if it returns 1 it is not.

Then the macro can be as simple as this:

Application.ScreenUpdating = False
On Error Resume Next
With Range("Z5:Z206")
.Rows.Hidden = False ''not needed if all rows are visible to start
with
.SpecialCells(xlCellTypeFormulas, xlLogical).Rows.Hidden = True
End With

If you want you can hide column Z and no one will know it's there.

--
Jim Rech
Excel MVP
"Jonsson " wrote in message
...
Hi, Jared! And all you out there

The code you, Jared, gave me works nice!
Thanks!
No, is it possible to do the same procedure but let the macro check
several different columns, lets say 7 of them?

I have tried to write for instance"E", "G", "T" columns, instead of
just "E", but that dont work, of course.

Any idea´s?

//Thomas


---
Message posted from http://www.ExcelForum.com/

Hide and printout area conditions
 

Thanks Jim and Jarek!

I appreciate your help!

Both solutions work great!!

//Thoma

--
Message posted from http://www.ExcelForum.com