Print only Cells with Data

T

The Animal

Active Member
Joined
May 26, 2011
Messages
449
Hi.
I have a worksheet A2 to K83 and I want to create a button to print only down to the line that has data from B2 to K83 but the "A" cells is to be included in the print if there is data in the "B" Column.
e.g if there is data in cells B2 to say B20 then I only want to print A2 to K20 and not the remaining blank cells A21 to A83

Thanks Stephen
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
What do you want to print if there's no data in B2:B83?
 
Upvote 0
Hi Joe
Nothing. I only want to print row A2 to A whatever depending on where data is found in the "B" Column. so if there is data down to say B20 then print only A2 to K20

Thanks
 
Upvote 0
The Animal said:
Hi Joe
Nothing. I only want to print row A2 to A whatever depending on where data is found in the "B" Column. so if there is data down to say B20 then print only A2 to K20

Thanks
Click to expand...
And ......... if there is no data in B2:B83 then you don't want to print or ....?
 
Upvote 0
The Animal said:
I dont want to print anything if there is no data in B2 to B83
Thanks
Click to expand...
Try this (untested):
Code: 
Sub PrintIt()
Dim R As Range
Set R = ActiveSheet.Range("A2:K83")
If WorksheetFunction.CountA(R.Columns(2)) = 0 Then Exit Sub
ActiveSheet.PageSetup.PrintArea = R.Resize(R.Columns(2).Find("*", _
    searchdirection:=xlPrevious).Row - 1, Columns("A:K").Count).Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
End Sub
May need some tweaking if your columns are wide enough to create page breaks.
 
Upvote 0
Sorry Joe.
How would it look if I wanted to open my print window to select a printer rather than using the default. I need to swap printer from my default to print document
Thanks again
 
Upvote 0
The Animal said:
Sorry Joe.
How would it look if I wanted to open my print window to select a printer rather than using the default. I need to swap printer from my default to print document
Thanks again
Click to expand...

Try this:
Code: 
Sub PrintIt()
Dim R As Range
Set R = ActiveSheet.Range("A2:K83")
If WorksheetFunction.CountA(R.Columns(2)) = 0 Then Exit Sub
ActiveSheet.PageSetup.PrintArea = R.Resize(R.Columns(2).Find("*", _
    searchdirection:=xlPrevious).Row - 1, Columns("A:K").Count).Address
Application.Dialogs(xlDialogPrint).Show
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Data printing by following specific formula in specific cell
Replies
0
Views
108
Rezaulkarimacce
R
A
  • Question
VBA code to print lottery tickets base on the cell amount.
Replies
0
Views
166
asyamonique
A
I
  • Solved
Print sheets based on checkbox
Replies
3
Views
172
John_w
J
E
  • Question
Print routines
2
Replies
13
Views
682
MARK858
MARK858
B
  • Solved
macro to print selection when there is data in rows
2 3
Replies
24
Views
1K
bh24524
B

Forum statistics

Threads
1,214,925
Messages
6,122,301
Members
449,078
Latest member
nonnakkong

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top