Trying to clear certain columns if a name is present

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOBSTOTDT
41JoeOffice18.001.002.00523 9th ave8.001.002.00
52MikeLaborer BK28.00523 9th ave8.000.000.00
63EdOffice38.002.003.00104 Ralph Ave8.002.003.00104 Ralph Ave8.001.001.00523 9th ave24.005.007.00
74RalphOffice48.00523 9th ave8.000.000.00
85LeeOffice78.001.001.50104 Ralph Ave8.001.001.00523 9th ave16.002.002.50
96TommyOffice118.001.001.00104 Ralph Ave8.00523 9th ave16.001.001.00
10Totals80.009.0012.50
11Overall Totals101.50
Data
Named Ranges
NameRefers ToCells
Print_Area=Data!$A$1:$AH$70AE4:AG10, AE11
Print_Titles=Data!$1:$3E2:AD2



Results:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOB
41JoeOffice18.001.002.00523 9th ave
52MikeLaborer BK28.00523 9th ave
63EdOffice38.001.001.00523 9th ave
74RalphOffice48.00523 9th ave
85LeeOffice78.001.001.00523 9th ave
96TommyOffice118.00523 9th ave
Data
Named Ranges
NameRefers ToCells
Print_Titles=Data!$1:$3E2:AD2
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
How about
VBA Code: 
Sub zone()
   Dim i As Long
   
   For i = 8 To 30 Step 3
      If Application.CountIf(Columns(i), "523 9th ave") = 0 Then
         Range(Cells(4, i - 2), Cells(Rows.count, i)).ClearContents
      End If
   Next i
End Sub
 
Upvote 0
hi thanks for the reply the only thing i am seeing wrong its taking out the Q column which i need and look at the E column the 8 is there.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOBSTOTDT
41JoeOffice11.002.00523 9th ave0.001.002.00
52MikeLaborer BK2523 9th ave0.000.000.00
63EdOffice38.001.001.00523 9th ave8.001.001.00
74RalphOffice4523 9th ave0.000.000.00
85LeeOffice71.001.00523 9th ave0.001.001.00
96TommyOffice11523 9th ave0.000.000.00
Data
Named Ranges
NameRefers ToCells
Print_Area=Data!$A$1:$AH$70AE4:AG9
Print_Titles=Data!$1:$3E2:AD2
 
Upvote 0
Oops, I can't count. Also missed the fact that the last two sets are smaller.
Try
VBA Code: 
Sub zone()
   Dim i As Long
   Dim Ary As Variant
   Ary = Array(8, 3, 12, 3, 16, 3, 20, 3, 24, 3, 27, 2, 30, 2)
   For i = 0 To UBound(Ary) Step 2
      If Application.CountIf(Columns(Ary(i)), "523 9th ave") = 0 Then
         Range(Cells(4, Ary(i) - Ary(i + 1)), Cells(Rows.Count, Ary(i))).ClearContents
      End If
   Next i
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi i tried using it again and i seen Ralph ave in line 7 and 9 staying while it should be cleared alos

Book1
QRST
24/30/2020
3STOTDTJOB
48.001.002.00523 9th ave
58.00523 9th ave
68.001.001.00523 9th ave
78.002.003.00104 Ralph Ave
88.001.001.00523 9th ave
98.001.001.00104 Ralph Ave
Data
Named Ranges
NameRefers ToCells
Print_Titles=Data!$1:$3Q2:T2
 
Upvote 0
That's because in your op, you gave the impression you wanted to clear columns if they did not contain "523 9th Ave", which is what the code does.
Can you please post some representative data of what you have & what the outcome should be.
 
Upvote 0
Sorry maybe this will help explain

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOBSTOTDT
41JoeOffice18.001.002.00523 9th ave8.001.002.00
52MikeLaborer BK28.00523 9th ave8.00523 9th ave8.00523 9th ave24.000.000.00
63EdOffice38.002.003.00104 Ralph Ave8.002.003.00104 Ralph Ave8.001.001.50104 Ralph Ave24.005.007.50
74RalphOffice48.00108 - 52 ave8.00523 9th ave16.000.000.00
8Mik lOffice68.001.00108 Cramer Street8.001.00108 Cramer Street8.00108 - 52 ave8.001.00108 Cramer Street8.001.00108 Cramer Street40.004.000.00
9John TOffice48.001.00108 Cramer Street8.001.000.00
105LeeOffice78.001.001.50104 Ralph Ave8.001.001.50104 Ralph Ave16.002.003.00
116TommyOffice118.001.001.00104 Ralph Ave8.00523 9th ave16.001.001.00
12Totals152.0014.0013.50
13Overall Totals179.50
Data
Named Ranges
NameRefers ToCells
Print_Area=Data!$A$1:$AH$70AE4:AG12, AE13
Print_Titles=Data!$1:$3E2:AD2


Results:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOBSTOTDT
41JoeOffice18.001.002.00523 9th ave8.001.002.00
52MikeLaborer BK28.00523 9th ave8.00523 9th ave8.00523 9th ave24.000.000.00
63EdOffice30.000.000.00
74RalphOffice48.00523 9th ave8.000.000.00
8Mik lOffice60.00
9John TOffice40.00
105LeeOffice70.000.000.00
116TommyOffice118.00523 9th ave8.000.000.00
12Totals48.001.002.00
13Overall Totals51.00
Data
Named Ranges
NameRefers ToCells
Print_Area=Data!$A$1:$AH$70AF10:AG12, AE4:AG7, AE8:AE13
Print_Titles=Data!$1:$3E2:AD2
 
Upvote 0
How about
VBA Code: 
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.Value <> "" And LCase(Cl.Value) <> "523 9th ave" Then
         Cl.Offset(, -3).Resize(, 4).Value = ""
      End If
   Next Cl
   For Each Cl In Rng2
      If Cl.Value <> "" And LCase(Cl.Value) <> "523 9th ave" Then
         Cl.Offset(, -2).Resize(, 3).Value = ""
      End If
   Next Cl
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Modifying LET formula to include an additional data check
Replies
7
Views
321
Cubist
Cubist
S
  • Question Question
Multiple columns lookup
Replies
0
Views
241
Sipirili2504
S
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
369
ChrisMac1
ChrisMac1
Ramballah
  • Solved
Finding the first certain value with conditions
2
Replies
18
Views
911
mumps
mumps
T
  • Solved
Determining a Range within a Column using VBA
Replies
1
Views
84
jolivanes
jolivanes

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

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