Printing an area decided by criteria

I

Ibbo1978

New Member
Joined
Mar 6, 2017
Messages
20
Hi Guys,

I hope you can help as always.

I have a file that I want to print up to a certain row which contains the word "END". If there is no "END" in that column, I want a message state that.

I have set up a Formula to automate the name range and added a button to simply print the print area, although feel I can do all the above within VBA with a little assistance.

Please advise,

Ibbo
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Try this

Replace following values with correct values for your worksheet
- Top Left cell of print area $B$2
- Column where "END" is to be found A
- Last column to print F


Code: 
Sub Set_Print_Area()
    
With ActiveSheet
    On Error GoTo Oops
    .PageSetup.PrintArea = "[COLOR=#ff0000]$B$2[/COLOR]" & ":" & Cells(.Columns("[COLOR=#0000cd]A[/COLOR]").Find("END", lookat:=xlWhole).Row, "[COLOR=#800080]F[/COLOR]").Address
End With

Exit Sub
Oops: MsgBox "Where has the END gone?", vbCritical, "oops!"
End Sub
 
Last edited:
Upvote 0
Code: 
Sub Set_Print_Area()
    
With ActiveSheet
    On Error GoTo Oops
    .PageSetup.PrintArea = "$B$2" & ":" & Cells(.Columns("A").Find("END", lookat:=xlWhole).Row, "F").Address
 [COLOR=#ff0000]   .PrintOut[/COLOR]
End With

Exit Sub
Oops: MsgBox "Where has the END gone?", vbCritical, "oops!"
End Sub
 
Upvote 0
1. Post ENTIRE code that you are using
2. Which columns are you trying to print ?
3. Which column contains "END" ?
4. Which row should be the first row to print ?
 
Upvote 0
Sub Print_ETG()
With ActiveSheet
On Error GoTo Oops
.PageSetup.PrintArea = "$A$1" & ":" & Cells(.Columns("C").Find("END", lookat:=xlWhole).Row, "C").Address
.PrintOut
End With

Exit Sub
Oops: MsgBox "Where has the END gone?", vbCritical, "oops!"
End Sub


I need to:
Print A:N
Column containing "END" is C
Print Row 1 until row containing "END" or more ideally row previous to row containing "END"

Thanks again
 
Upvote 0
or ideally row previous to row containing "END"
Click to expand...
- adjusted like this:
Code: 
    .PageSetup.PrintArea = "$A$1" & ":" & Cells(.Columns("C").Find("End", lookat:=xlWhole).[COLOR=#006400]Row - 1[/COLOR], "N").Address


With code as amended below, and worksheet as copied below ...the print range that is printed for me is A1:N26


Code: 
Sub Set_Print_Area()
    
With ActiveSheet
    On Error GoTo Oops
    .PageSetup.PrintArea = "[COLOR=#ff0000]$A$1[/COLOR]" & ":" & Cells(.Columns("[COLOR=#ff0000]C[/COLOR]").Find("END", lookat:=xlWhole).[COLOR=#006400]Row - 1[/COLOR], "[COLOR=#ff0000]N[/COLOR]").Address
    .PrintOut
End With
    
Exit Sub
Oops: MsgBox "Where has the END gone?", vbCritical, "oops!"
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][th]
P​
[/th][th]
Q​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]a1[/td][td]b1[/td][td]c1[/td][td]d1[/td][td]e1[/td][td]f1[/td][td]g1[/td][td]h1[/td][td]i1[/td][td]j1[/td][td]k1[/td][td]l1[/td][td]m1[/td][td]n1[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]a2[/td][td]b2[/td][td]c2[/td][td]d2[/td][td]e2[/td][td]f2[/td][td]g2[/td][td]h2[/td][td]i2[/td][td]j2[/td][td]k2[/td][td]l2[/td][td]m2[/td][td]n2[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]a3[/td][td]b3[/td][td]c3[/td][td]d3[/td][td]e3[/td][td]f3[/td][td]g3[/td][td]h3[/td][td]i3[/td][td]j3[/td][td]k3[/td][td]l3[/td][td]m3[/td][td]n3[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]a4[/td][td]b4[/td][td]c4[/td][td]d4[/td][td]e4[/td][td]f4[/td][td]g4[/td][td]h4[/td][td]i4[/td][td]j4[/td][td]k4[/td][td]l4[/td][td]m4[/td][td]n4[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]a5[/td][td]b5[/td][td]c5[/td][td]d5[/td][td]e5[/td][td]f5[/td][td]g5[/td][td]h5[/td][td]i5[/td][td]j5[/td][td]k5[/td][td]l5[/td][td]m5[/td][td]n5[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]a6[/td][td]b6[/td][td][/td][td]d6[/td][td]e6[/td][td]f6[/td][td]g6[/td][td]h6[/td][td]i6[/td][td]j6[/td][td]k6[/td][td]l6[/td][td]m6[/td][td]n6[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]a7[/td][td]b7[/td][td]c7[/td][td]d7[/td][td]e7[/td][td]f7[/td][td]g7[/td][td]h7[/td][td]i7[/td][td]j7[/td][td]k7[/td][td]l7[/td][td]m7[/td][td]n7[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]a8[/td][td]b8[/td][td]c8[/td][td]d8[/td][td]e8[/td][td]f8[/td][td]g8[/td][td]h8[/td][td]i8[/td][td]j8[/td][td]k8[/td][td]l8[/td][td]m8[/td][td]n8[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]a9[/td][td]b9[/td][td]c9[/td][td]d9[/td][td]e9[/td][td]f9[/td][td]g9[/td][td]h9[/td][td]i9[/td][td]j9[/td][td]k9[/td][td]l9[/td][td]m9[/td][td]n9[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]a10[/td][td]b10[/td][td]c10[/td][td]d10[/td][td]e10[/td][td]f10[/td][td]g10[/td][td]h10[/td][td]i10[/td][td]j10[/td][td]k10[/td][td]l10[/td][td]m10[/td][td]n10[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]a11[/td][td]b11[/td][td]c11[/td][td]d11[/td][td]e11[/td][td]f11[/td][td]g11[/td][td]h11[/td][td]i11[/td][td]j11[/td][td]k11[/td][td]l11[/td][td]m11[/td][td]n11[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]a12[/td][td]b12[/td][td]c12[/td][td]d12[/td][td]e12[/td][td]f12[/td][td]g12[/td][td]h12[/td][td]i12[/td][td]j12[/td][td]k12[/td][td]l12[/td][td]m12[/td][td]n12[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]a13[/td][td]b13[/td][td]c13[/td][td]d13[/td][td]e13[/td][td]f13[/td][td]g13[/td][td]h13[/td][td]i13[/td][td]j13[/td][td]k13[/td][td]l13[/td][td]m13[/td][td]n13[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]a14[/td][td]b14[/td][td]c14[/td][td]d14[/td][td]e14[/td][td]f14[/td][td]g14[/td][td]h14[/td][td]i14[/td][td]j14[/td][td]k14[/td][td]l14[/td][td]m14[/td][td]n14[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]a15[/td][td]b15[/td][td]c15[/td][td]d15[/td][td]e15[/td][td]f15[/td][td]g15[/td][td]h15[/td][td]i15[/td][td]j15[/td][td]k15[/td][td]l15[/td][td]m15[/td][td]n15[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]a16[/td][td]b16[/td][td]c16[/td][td]d16[/td][td]e16[/td][td]f16[/td][td]g16[/td][td]h16[/td][td]i16[/td][td]j16[/td][td]k16[/td][td]l16[/td][td]m16[/td][td]n16[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]a17[/td][td]b17[/td][td]c17[/td][td]d17[/td][td]e17[/td][td]f17[/td][td]g17[/td][td]h17[/td][td]i17[/td][td]j17[/td][td]k17[/td][td]l17[/td][td]m17[/td][td]n17[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]a18[/td][td]b18[/td][td][/td][td]d18[/td][td]e18[/td][td]f18[/td][td]g18[/td][td]h18[/td][td]i18[/td][td]j18[/td][td]k18[/td][td]l18[/td][td]m18[/td][td]n18[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]a19[/td][td]b19[/td][td]c19[/td][td]d19[/td][td]e19[/td][td]f19[/td][td]g19[/td][td]h19[/td][td]i19[/td][td]j19[/td][td]k19[/td][td]l19[/td][td]m19[/td][td]n19[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]a20[/td][td]b20[/td][td]c20[/td][td]d20[/td][td]e20[/td][td]f20[/td][td]g20[/td][td]h20[/td][td]i20[/td][td]j20[/td][td]k20[/td][td]l20[/td][td]m20[/td][td]n20[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]a21[/td][td]b21[/td][td]c21[/td][td]d21[/td][td]e21[/td][td]f21[/td][td]g21[/td][td]h21[/td][td]i21[/td][td]j21[/td][td]k21[/td][td]l21[/td][td]m21[/td][td]n21[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]a22[/td][td]b22[/td][td]c22[/td][td]d22[/td][td]e22[/td][td]f22[/td][td]g22[/td][td]h22[/td][td]i22[/td][td]j22[/td][td]k22[/td][td]l22[/td][td]m22[/td][td]n22[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]a23[/td][td]b23[/td][td]c23[/td][td]d23[/td][td]e23[/td][td]f23[/td][td]g23[/td][td]h23[/td][td]i23[/td][td]j23[/td][td]k23[/td][td]l23[/td][td]m23[/td][td]n23[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]a24[/td][td]b24[/td][td]c24[/td][td]d24[/td][td]e24[/td][td]f24[/td][td]g24[/td][td]h24[/td][td]i24[/td][td]j24[/td][td]k24[/td][td]l24[/td][td]m24[/td][td]n24[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]a25[/td][td]b25[/td][td]c25[/td][td]d25[/td][td]e25[/td][td]f25[/td][td]g25[/td][td]h25[/td][td]i25[/td][td]j25[/td][td]k25[/td][td]l25[/td][td]m25[/td][td]n25[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td]a26[/td][td]b26[/td][td]c26[/td][td]d26[/td][td]e26[/td][td]f26[/td][td]g26[/td][td]h26[/td][td]i26[/td][td]j26[/td][td]k26[/td][td]l26[/td][td]m26[/td][td]n26[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td]a27[/td][td]b27[/td][td]end[/td][td]d27[/td][td]e27[/td][td]f27[/td][td]g27[/td][td]h27[/td][td]i27[/td][td]j27[/td][td]k27[/td][td]l27[/td][td]m27[/td][td]n27[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td]a28[/td][td]b28[/td][td]c28[/td][td]d28[/td][td]e28[/td][td]f28[/td][td]g28[/td][td]h28[/td][td]i28[/td][td]j28[/td][td]k28[/td][td]l28[/td][td]m28[/td][td]n28[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td]a29[/td][td]b29[/td][td]c29[/td][td]d29[/td][td]e29[/td][td]f29[/td][td]g29[/td][td]h29[/td][td]i29[/td][td]j29[/td][td]k29[/td][td]l29[/td][td]m29[/td][td]n29[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td]a30[/td][td]b30[/td][td]c30[/td][td]d30[/td][td]e30[/td][td]f30[/td][td]g30[/td][td]h30[/td][td]i30[/td][td]j30[/td][td]k30[/td][td]l30[/td][td]m30[/td][td]n30[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td]a31[/td][td]b31[/td][td]c31[/td][td]d31[/td][td]e31[/td][td]f31[/td][td]g31[/td][td]h31[/td][td]i31[/td][td]j31[/td][td]k31[/td][td]l31[/td][td]m31[/td][td]n31[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td]a32[/td][td]b32[/td][td]c32[/td][td]d32[/td][td]e32[/td][td]f32[/td][td]g32[/td][td]h32[/td][td]i32[/td][td]j32[/td][td]k32[/td][td]l32[/td][td]m32[/td][td]n32[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td]a33[/td][td]b33[/td][td]c33[/td][td]d33[/td][td]e33[/td][td]f33[/td][td]g33[/td][td]h33[/td][td]i33[/td][td]j33[/td][td]k33[/td][td]l33[/td][td]m33[/td][td]n33[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td]a34[/td][td]b34[/td][td]c34[/td][td]d34[/td][td]e34[/td][td]f34[/td][td]g34[/td][td]h34[/td][td]i34[/td][td]j34[/td][td]k34[/td][td]l34[/td][td]m34[/td][td]n34[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
35​
[/td][td]a35[/td][td]b35[/td][td]c35[/td][td]d35[/td][td]e35[/td][td]f35[/td][td]g35[/td][td]h35[/td][td]i35[/td][td]j35[/td][td]k35[/td][td]l35[/td][td]m35[/td][td]n35[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
it was my mistake due to "END" being the result of a formula, I have now fixed it.

Thanks foal your assistance
 
Upvote 0
it was my mistake due to "END" being the result of a formula
Click to expand...

add an extra attribute (as below) and it will also work if cell contains a formula which returns "end" ...

Code: 
.PageSetup.PrintArea = "$A$1" & ":" & Cells(.Columns("C").Find("END", lookat:=xlWhole[COLOR=#ff0000], LookIn:=xlValues)[/COLOR].Row - 1, "N").Address
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Complex Print Button
Replies
2
Views
192
kevin9999
K
J
  • Question Question
Macro to assign to print button with specific criteria
2
Replies
11
Views
773
Micron
M
S
  • Question Question
Macro to change one cell on a sheet, save sheet as PDF, then loop back and change that one cell on that sheet and repeat until the end
Replies
3
Views
680
IslandofBDA
I
T
  • Question Question
VBA Stuck Sanitising Several Sheets
Replies
2
Views
218
Tarkemelion
T
T
  • Question Question
Adding icons to ribbon
Replies
4
Views
796
tiredofit
T

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

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