Trying to clear certain columns if a name is present

[zone709]

Hi trying to clear other columns other then if 523 9th ave is in H,L,P,T,X,AA,AD. As you can see i also need to keep the data from 3 columns before the name 523 9th ave. If anything other then 523 9th ave is present i need to clear it from the range only E to Ad.


Example:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	SNO	Employee Name	Local\Trade	ID	MONDAY				TUESDAY				WEDNESDAY				THURSDAY				FRIDAY				SATURDAY			SUNDAY			TOTAL
2					4/27/2020				4/28/2020				4/29/2020				4/30/2020				5/1/2020				5/2/2020			5/3/2020
3					ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	JOB	ST	OT	JOB	ST	OT	DT
4	1	Joe	Office	1													8.00	1.00	2.00	523 9th ave											8.00	1.00	2.00
5	2	Mike	Laborer BK	2													8.00			523 9th ave											8.00	0.00	0.00
6	3	Ed	Office	3	8.00	2.00	3.00	104 Ralph Ave	8.00	2.00	3.00	104 Ralph Ave					8.00	1.00	1.00	523 9th ave											24.00	5.00	7.00
7	4	Ralph	Office	4													8.00			523 9th ave											8.00	0.00	0.00
8	5	Lee	Office	7					8.00	1.00	1.50	104 Ralph Ave					8.00	1.00	1.00	523 9th ave											16.00	2.00	2.50
9	6	Tommy	Office	11					8.00	1.00	1.00	104 Ralph Ave					8.00			523 9th ave											16.00	1.00	1.00
10																														Totals	80.00	9.00	12.50
11																														Overall Totals	101.50
Data

Named Ranges
Name	Refers To	Cells
Print_Area	=Data!$A$1:$AH$70	AE4:AG10, AE11
Print_Titles	=Data!$1:$3	E2:AD2

Results:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	SNO	Employee Name	Local\Trade	ID	MONDAY				TUESDAY				WEDNESDAY				THURSDAY				FRIDAY				SATURDAY			SUNDAY
2					4/27/2020				4/28/2020				4/29/2020				4/30/2020				5/1/2020				5/2/2020			5/3/2020
3					ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	DT	JOB	ST	OT	JOB	ST	OT	JOB
4	1	Joe	Office	1													8.00	1.00	2.00	523 9th ave
5	2	Mike	Laborer BK	2													8.00			523 9th ave
6	3	Ed	Office	3													8.00	1.00	1.00	523 9th ave
7	4	Ralph	Office	4													8.00			523 9th ave
8	5	Lee	Office	7													8.00	1.00	1.00	523 9th ave
9	6	Tommy	Office	11													8.00			523 9th ave
Data

Named Ranges
Name	Refers To	Cells
Print_Titles	=Data!$1:$3	E2:AD2

 

[zone709]

Hi thanks for the reply. So this works for the address i put above which is great, but i probably would like to change the name in the code sometimes. When i do that it doesn't work. Like say i put in 104 Ralph Ave. It then wont work. Is it because its of the .Value. Do I need to change something if I change the name sometimes?

 

[Fluff]

You need to use lower case in the code.

 

[zone709]

got it works great thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[zone709]

Hi just thought i reach out one more time to see if i can do this since i realize when running this maybe it can be even easier. Sometimes when i use this i have to change the job name in the code to have make this work differently. Anyway the code can pick up the name on the activesheet instead of me changing it everytime. So lets say on the sheet ill type in the name in the cell AJ3 every time then run the code. Meaning use cell AJ3 to identify the name to use in the code.

 

[Fluff]

Just replace the text value, with a cell reference.

 

[zone709]

ok thanks

 

[Fluff]

You're welcome.

 

[zone709]

HI i changed code. It doesn't break which is good ,but doesn't work. I put n5 to test it. I have name in the n5 cell. I think i am missing something in the code though.

Sub zone()
   Dim Cl As Range, Rng1 As Range, Rng2 As Range
   
   With Range("A4", Range("A" & Rows.Count).End(xlUp))
      Set Rng1 = Intersect(.EntireRow, Range("H:H,L:L,P:P,T:T,X:X"))
      Set Rng2 = Intersect(.EntireRow, Range("AA:AA,AD:AD"))
   End With
   For Each Cl In Rng1
      If Cl.Cells <> "" And LCase(Cl.Cells) <> "n5" Then
         Cl.Offset(, -3).Resize(, 4).Cells = ""
      End If
   Next Cl
   For Each Cl In Rng2
      If Cl.Cells <> "" And LCase(Cl.Cells) <> "n5" Then
         Cl.Offset(, -2).Resize(, 3).Cells = ""
      End If
   Next Cl
End Sub

 

[Fluff]

You need to do it like

LCase(Cl.Cells) <> range("n5").value