|
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 |
printing a range that changes all the time
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 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 |
| All times are GMT +1. The time now is 10:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com