Hi experts,

I have data starting from A1 to M...
I would like to count the rows to print based on the input in column B !!!

I have the following code for printing a range.

Dim rngprint As Range
Sheets("Outputdlnrs").Select
Set rngprint = Range("A1", Range("A1").End(xlDown).Address)
ActiveSheet.PageSetup.PrintArea = rngprint.Address
Selection.PrintPreview

This does not work because only A1 is selected each time.

Does anybody have the right formula ?

Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Try this Pierre

Sub test()
Dim rngprint As Range
Dim lr As Long
Sheets("Outputdlnrs").Select
lr = Range("B" & Rows.Count).End(xlUp).Row
Set rngprint = Range("A1:M" & lr)
rngprint.PrintPreview
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pierre via OfficeKB.com" <u13950@uwe wrote in message news:566bc8945ed14@uwe...
Hi experts,

I have data starting from A1 to M...
I would like to count the rows to print based on the input in column B !!!

I have the following code for printing a range.

Dim rngprint As Range
Sheets("Outputdlnrs").Select
Set rngprint = Range("A1", Range("A1").End(xlDown).Address)
ActiveSheet.PageSetup.PrintArea = rngprint.Address
Selection.PrintPreview

This does not work because only A1 is selected each time.

Does anybody have the right formula ?

Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Ron,

Thanks you for your input. I checked your site and several questions asked
in this forum about printing in which your name came up each time !

Your code works fine but...

in column B there are some 1000 lines with a formula in them.
only a certain number of cells have actual data in them.
So it would be nice if your formula would only count the rows where the cell
< ""

Any ideas?
thanks,
Pierre

Ron de Bruin wrote:
Try this Pierre

Sub test()
Dim rngprint As Range
Dim lr As Long
Sheets("Outputdlnrs").Select
lr = Range("B" & Rows.Count).End(xlUp).Row
Set rngprint = Range("A1:M" & lr)
rngprint.PrintPreview
End Sub

Hi experts,

[quoted text clipped - 15 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Pierre

Why don't you filter with autofilter on nonblanks in column B and print

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pierre via OfficeKB.com" <u13950@uwe wrote in message news:566c60320fbbe@uwe...
Hi Ron,

Thanks you for your input. I checked your site and several questions asked
in this forum about printing in which your name came up each time !

Your code works fine but...

in column B there are some 1000 lines with a formula in them.
only a certain number of cells have actual data in them.
So it would be nice if your formula would only count the rows where the cell
< ""

Any ideas?
thanks,
Pierre

Ron de Bruin wrote:
Try this Pierre

Sub test()
Dim rngprint As Range
Dim lr As Long
Sheets("Outputdlnrs").Select
lr = Range("B" & Rows.Count).End(xlUp).Row
Set rngprint = Range("A1:M" & lr)
rngprint.PrintPreview
End Sub

Hi experts,

[quoted text clipped - 15 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Ron,

Wonderfull suggestion. but how do i go about this in VBA ?

- autofilter on row 7
- in column B choose the non blanks
- make printpreview
- when the user presses print or close the autofilter should be turnd off
again

Thanks for your big help Ron
Pierre

Ron de Bruin wrote:
Hi Pierre

Why don't you filter with autofilter on nonblanks in column B and print

Hi Ron,

[quoted text clipped - 28 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

With the header of column B in B7 try this

Sub Print_with_Autofilter()
With ActiveSheet
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintPreview
.AutoFilterMode = False
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pierre via OfficeKB.com" <u13950@uwe wrote in message news:566ce8bfc4910@uwe...
Hi Ron,

Wonderfull suggestion. but how do i go about this in VBA ?

- autofilter on row 7
- in column B choose the non blanks
- make printpreview
- when the user presses print or close the autofilter should be turnd off
again

Thanks for your big help Ron
Pierre

Ron de Bruin wrote:
Hi Pierre

Why don't you filter with autofilter on nonblanks in column B and print

Hi Ron,

[quoted text clipped - 28 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Ron,

Thanks man, you're the best !
This works great form me.

P.S. i found out that when the sheet is hidden, this code does not work.
Is there a way to keep the sheet hidden and still be able to present the
printpreview so users can pritn this out ?

Ron de Bruin wrote:
With the header of column B in B7 try this

Sub Print_with_Autofilter()
With ActiveSheet
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintPreview
.AutoFilterMode = False
End With
End Sub

Hi Ron,

[quoted text clipped - 18 lines]
Thanks,
Pierre


--
Message posted via http://www.officekb.com

Hi Pierre

youy can unhide and hide in the code

Sub Print_with_Autofilter()
With Sheets("Sheet1")
.Visible = True
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
.PrintPreview
.AutoFilterMode = False
.Visible = False
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pierre via OfficeKB.com" <u13950@uwe wrote in message news:5679d61d4826d@uwe...
Ron,

Thanks man, you're the best !
This works great form me.

P.S. i found out that when the sheet is hidden, this code does not work.
Is there a way to keep the sheet hidden and still be able to present the
printpreview so users can pritn this out ?

Ron de Bruin wrote:
With the header of column B in B7 try this

Sub Print_with_Autofilter()
With ActiveSheet
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintPreview
.AutoFilterMode = False
End With
End Sub

Hi Ron,

[quoted text clipped - 18 lines]
Thanks,
Pierre


--
Message posted via http://www.officekb.com

Thanks Ron,

This works fine for me !
Thanks,
Pierre

Ron de Bruin wrote:
Hi Pierre

youy can unhide and hide in the code

Sub Print_with_Autofilter()
With Sheets("Sheet1")
.Visible = True
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
.PrintPreview
.AutoFilterMode = False
.Visible = False
End With
End Sub

Ron,

[quoted text clipped - 20 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Ron,

I still have a little problem.
If i use your code the sheet(1) has to be unprotected.
Is there a way to have the sheet temporarily unprotect and after the code
protect again.
or
is there a way to really hide the sheet and only have it appear when this
code is executed ?
otherwise users might use ctrl-pagedown

Ron de Bruin wrote:
Hi Pierre

youy can unhide and hide in the code

Sub Print_with_Autofilter()
With Sheets("Sheet1")
.Visible = True
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
.PrintPreview
.AutoFilterMode = False
.Visible = False
End With
End Sub

Ron,

[quoted text clipped - 20 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Pierre

is there a way to really hide the sheet and only have it appear when this
code is executed ?

That is the code doing now with
..Visible = True
and
..Visible = False

It unhide the sheet
Apply the filter
Print
Hide the sheet

But you can also unprotect/protect the sheet in the macro if you want and not hide the sheet

Sub Print_with_Autofilter()
With Sheets("Sheet1")
'.Visible = True
.Unprotect Password:="password"
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
.PrintPreview
.AutoFilterMode = False
.Protect Password:="password"
'.Visible = False
End With
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pierre via OfficeKB.com" <u13950@uwe wrote in message news:569d7a8ed4914@uwe...
Hi Ron,

I still have a little problem.
If i use your code the sheet(1) has to be unprotected.
Is there a way to have the sheet temporarily unprotect and after the code
protect again.
or
is there a way to really hide the sheet and only have it appear when this
code is executed ?
otherwise users might use ctrl-pagedown

Ron de Bruin wrote:
Hi Pierre

youy can unhide and hide in the code

Sub Print_with_Autofilter()
With Sheets("Sheet1")
.Visible = True
.Range("B7:B100").AutoFilter Field:=1, Criteria1:="<"
.PrintPreview
.AutoFilterMode = False
.Visible = False
End With
End Sub

Ron,

[quoted text clipped - 20 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1