Printing an area decided by criteria

[Ibbo1978]

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

 

[Yongle]

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

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

 

[Ibbo1978]

Thankyou

How do I incorporate the print if no Oops?

 

[Yongle]

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

 

[Ibbo1978]

There seems to be an error, will it error if there is a blank cell before finding "END" ?

 

[Yongle]

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 ?

 

[Ibbo1978]

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

 

[Yongle]

or ideally row previous to row containing "END"

- adjusted like this:

    .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

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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
1​	a1	b1	c1	d1	e1	f1	g1	h1	i1	j1	k1	l1	m1	n1
2​	a2	b2	c2	d2	e2	f2	g2	h2	i2	j2	k2	l2	m2	n2
3​	a3	b3	c3	d3	e3	f3	g3	h3	i3	j3	k3	l3	m3	n3
4​	a4	b4	c4	d4	e4	f4	g4	h4	i4	j4	k4	l4	m4	n4
5​	a5	b5	c5	d5	e5	f5	g5	h5	i5	j5	k5	l5	m5	n5
6​	a6	b6		d6	e6	f6	g6	h6	i6	j6	k6	l6	m6	n6
7​	a7	b7	c7	d7	e7	f7	g7	h7	i7	j7	k7	l7	m7	n7
8​	a8	b8	c8	d8	e8	f8	g8	h8	i8	j8	k8	l8	m8	n8
9​	a9	b9	c9	d9	e9	f9	g9	h9	i9	j9	k9	l9	m9	n9
10​	a10	b10	c10	d10	e10	f10	g10	h10	i10	j10	k10	l10	m10	n10
11​	a11	b11	c11	d11	e11	f11	g11	h11	i11	j11	k11	l11	m11	n11
12​	a12	b12	c12	d12	e12	f12	g12	h12	i12	j12	k12	l12	m12	n12
13​	a13	b13	c13	d13	e13	f13	g13	h13	i13	j13	k13	l13	m13	n13
14​	a14	b14	c14	d14	e14	f14	g14	h14	i14	j14	k14	l14	m14	n14
15​	a15	b15	c15	d15	e15	f15	g15	h15	i15	j15	k15	l15	m15	n15
16​	a16	b16	c16	d16	e16	f16	g16	h16	i16	j16	k16	l16	m16	n16
17​	a17	b17	c17	d17	e17	f17	g17	h17	i17	j17	k17	l17	m17	n17
18​	a18	b18		d18	e18	f18	g18	h18	i18	j18	k18	l18	m18	n18
19​	a19	b19	c19	d19	e19	f19	g19	h19	i19	j19	k19	l19	m19	n19
20​	a20	b20	c20	d20	e20	f20	g20	h20	i20	j20	k20	l20	m20	n20
21​	a21	b21	c21	d21	e21	f21	g21	h21	i21	j21	k21	l21	m21	n21
22​	a22	b22	c22	d22	e22	f22	g22	h22	i22	j22	k22	l22	m22	n22
23​	a23	b23	c23	d23	e23	f23	g23	h23	i23	j23	k23	l23	m23	n23
24​	a24	b24	c24	d24	e24	f24	g24	h24	i24	j24	k24	l24	m24	n24
25​	a25	b25	c25	d25	e25	f25	g25	h25	i25	j25	k25	l25	m25	n25
26​	a26	b26	c26	d26	e26	f26	g26	h26	i26	j26	k26	l26	m26	n26
27​	a27	b27	end	d27	e27	f27	g27	h27	i27	j27	k27	l27	m27	n27
28​	a28	b28	c28	d28	e28	f28	g28	h28	i28	j28	k28	l28	m28	n28
29​	a29	b29	c29	d29	e29	f29	g29	h29	i29	j29	k29	l29	m29	n29
30​	a30	b30	c30	d30	e30	f30	g30	h30	i30	j30	k30	l30	m30	n30
31​	a31	b31	c31	d31	e31	f31	g31	h31	i31	j31	k31	l31	m31	n31
32​	a32	b32	c32	d32	e32	f32	g32	h32	i32	j32	k32	l32	m32	n32
33​	a33	b33	c33	d33	e33	f33	g33	h33	i33	j33	k33	l33	m33	n33
34​	a34	b34	c34	d34	e34	f34	g34	h34	i34	j34	k34	l34	m34	n34
35​	a35	b35	c35	d35	e35	f35	g35	h35	i35	j35	k35	l35	m35	n35

Sheet: Sheet2

 

[Ibbo1978]

it was my mistake due to "END" being the result of a formula, I have now fixed it.

Thanks foal your assistance

 

[Yongle]

it was my mistake due to "END" being the result of a formula

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

.PageSetup.PrintArea = "$A$1" & ":" & Cells(.Columns("C").Find("END", lookat:=xlWhole[COLOR=#ff0000], LookIn:=xlValues)[/COLOR].Row - 1, "N").Address