Hiding empty rows before printing..

Why don't you set your print area to A1:E50?

Thanks Mark..but the problem is the range is not
fixed..its a1:e100..but sometimes its a1:50 or
a1:e80 or etc..thats why i need macro to do this..
any ideas ?

...and, how would a macro determine your
print range? Thanks Mark..but the problem is the range is not

: Why don't you set your print area to A1:E50?

i'm hopiing like..it will detecting the empty
rows and hide it..so i can print the sheet without
the border and colour....and then unhide the rows
that i hide after i print the sheet..so i guess it
required two macros..

...and, how would a macro determine your print range? : Thanks Mark..but the problem is the range is not : fixed..its a1:e100..but sometimes its a1:50 or : a1:e80 or etc..thats why i need macro to do this.. : any ideas ? :

Well, if you set the print range to include only
cells with data there's nothing to hide. Is there
a particular column whose contents can be
reliably used to determine the extent of the
print range? i'm hopiing like..it will detecting the empty rows and hide it..so i can print the sheet without the border and colour....and then unhide the rows that i hide after i print the sheet..so i guess it required two macros.. : ...and, how would a macro determine your : print range?

Try this code for your macro:

Sub Macro1()
'
' Macro1 Macro
' Macro created 8/3/2001 by Cory'
' Keyboard Shortcut: Ctrl+q
'
Dim lastrow As Integer
lastrow = 65
Application.ScreenUpdating = False
Range("c1").Select
Do Until ActiveCell.Row > lastrow
If ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
End If
Loop
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Did that work?

Cory

Sorry, forgot to explain...

Range("C1") is where your first formula would be. I assume since you have formulas with blank cells, you're using an IF that'll hide zeros and error values. Set lastrow = the last row with formulas in it.

When this code runns, it starts with the first formula and checks to see if the result's blank. If it is, it hides the row and moves onto test the next one. It'll do this until the "lastrow" you specified...

Cory

hehe..thanks cory ..it works.but how can i
unhide back the rows..coz maybe other user range
is different..i need to unhide back the rows after
print the sheet..many thanks cory

Try this code for your macro: Sub Macro1() ' ' Macro1 Macro ' Macro created 8/3/2001 by Cory' ' Keyboard Shortcut: Ctrl+q ' Dim lastrow As Integer lastrow = 65 Application.ScreenUpdating = False Range("c1").Select Do Until ActiveCell.Row > lastrow If ActiveCell.Value <> "" Then ActiveCell.Offset(1, 0).Select Else ActiveCell.EntireRow.Hidden = True ActiveCell.Offset(1, 0).Select End If Loop Range("A1").Select Application.ScreenUpdating = True End Sub Did that work? Cory

That depends...

If you're printing by standard means (File --> Print... etc.) then you should use another macro to unhide them.

Sub Macro3()
'
' Macro3 Macro
' Macro created 8/3/2001 by Cory
'
' Keyboard Shortcut: Ctrl+w
'
Application.ScreenUpdating = False
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

If you're printing through a macro then you could:
use the one I gave you first, insert your print code, then finish it with the new macro above

Glad I could help...

Cory

Consider this...

Cells A1:A4 contain {"Fruit";"Apples";"Oranges";"Bananas"},
but cells A1:A8 have are formatted with a yellow
pattern; thus, Excel wants to print A1:A8.

However, if you create a defined name for Print_Area
that refers to:

=Sheet1!$A$1:OFFSET(Sheet1!$A$1, MAX((Sheet1!$A$1:$A$8<>"") * ROW(Sheet1!$A$1:$A$8))-1 + RAND()*0,0)

...then only A1:A4 will be printed. Later, if you
enter "Grapes" into cell A5 it will be printed
too!

And, "Look ma' no VBA"!!! : ) Well, if you set the print range to include only cells with data there's nothing to hide. Is there a particular column whose contents can be reliably used to determine the extent of the print range? : i'm hopiing like..it will detecting the empty : rows and hide it..so i can print the sheet without : the border and colour....and then unhide the rows : that i hide after i print the sheet..so i guess it : required two macros.. :

Thanks a zillion cory..it works..regards

That depends... If you're printing by standard means (File --> Print... etc.) then you should use another macro to unhide them. Sub Macro3() ' ' Macro3 Macro ' Macro created 8/3/2001 by Cory ' ' Keyboard Shortcut: Ctrl+w ' Application.ScreenUpdating = False Cells.Select Selection.EntireRow.Hidden = False Range("A1").Select Application.ScreenUpdating = True End Sub If you're printing through a macro then you could: use the one I gave you first, insert your print code, then finish it with the new macro above Glad I could help... Cory

Re: Consider this...

Mark,

How do you create a defined name for Print_Area outside the VBE?
Is the what you meant by "look, Ma, no VB!"?
Cory

Hehe... before you commit consider...

25690.html hehe..thanks cory ..it works.but how can i unhide back the rows..coz maybe other user range is different..i need to unhide back the rows after print the sheet..many thanks cory

: Try this code for your macro

Re: Consider this...

Thanks for the ideas and alternative Mark..
I know you are great..anyway..I guess you are
a chinese(by your "ma") lol thanks..regards

Cells A1:A4 contain {"Fruit";"Apples";"Oranges";"Bananas"}, but cells A1:A8 have are formatted with a yellow pattern; thus, Excel wants to print A1:A8. However, if you create a defined name for Print_Area that refers to: =Sheet1!$A$1:OFFSET(Sheet1!$A$1, MAX((Sheet1!$A$1:$A$8<>"") * ROW(Sheet1!$A$1:$A$8))-1 + RAND()*0,0) ...then only A1:A4 will be printed. Later, if you enter "Grapes" into cell A5 it will be printed too! And, "Look ma' no VBA"!!! : ) : Well, if you set the print range to include only : cells with data there's nothing to hide. Is there : a particular column whose contents can be : reliably used to determine the extent of the : print range?

Re: Consider this...

Use Insert | Name | Define... menu command, and
a dynamic reference. Mark, How do you create a defined name for Print_Area outside the VBE? Is the what you meant by "look, Ma, no VB!"? Cory

Re: Consider this...

Totally, Mark! Thanks for teaching me something new...

Cory

Re: Consider this...

i'm surprise..cory is good with vba..but..hehe.
we all need each other..thank you cory and mark

Totally, Mark! Thanks for teaching me something new... Cory

You could also just use the Workbook_BeforePrint
event.

Ivan Thanks a zillion cory..it works..regards

: That depends... : If you're printing by standard means (File --> Print... etc.) then you should use another macro to unhide them. : Sub Macro3() : ' : ' Macro3 Macro : ' Macro created 8/3/2001 by Cory : ' : ' Keyboard Shortcut: Ctrl+w : ' : Application.ScreenUpdating = False : Cells.Select : Selection.EntireRow.Hidden = False : Range("A1").Select : Application.ScreenUpdating = True : End Sub : : If you're printing through a macro then you could

Ha hahahahahaha!!!

Ha ha ha ha ha ha !!! I knew before I clicked that Ivan would have something to snuff all of that labor above! I laughed when i saw the post. Never knew the command existed! Thanks Ivan!

Cory