printing a range that changes all the time

[Pierre via OfficeKB.com]

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

[Ron de Bruin]

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

[Pierre via OfficeKB.com]

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

[Ron de Bruin]

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

[Pierre via OfficeKB.com]

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 de Bruin]

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

[Pierre via OfficeKB.com]

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

[Ron de Bruin]

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

[Pierre via 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

[Pierre via OfficeKB.com]

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

[Ron de Bruin]

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