VBA Excel - If word in this column delete contents from another column

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
I'm trying to say; if the word payout is in column c, delete contents from column b. I"m getting an error message @ If (Not Intersect(Target, Rows(3)) Is Payout) And (Target.Count = 3) Then

Sub Filter1()
Dim lRow As Long
Dim ts As Date
If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A30000").End(xlUp).Row



With Windows("Rental Rec.xlsm")
Sheets("Owned").Select
With Range("a2:bh" & lRow)
.AutoFilter
.AutoFilter field:=2, Criteria1:="<>#N/A"
.AutoFilter field:=3, Criteria1:="Payout"

End With
End With

'trying to say; if payout is in column c, delete contents from column b
' .Range("b3:b" & lrow).Value = "delete"


If (Not Intersect(Target, Rows(3)) Is Payout) And (Target.Count = 3) Then
Range("b3:b" & lRow).ClearContents




End If
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
How about
Code:
Sub Filter1()
Dim lRow As Long
Dim ts As Date

With Workbooks("Rental Rec.xlsm")
   With .Sheets("Owned")
      If .AutoFilterMode And .FilterMode Then .ShowAllData
      lRow = .Range("A30000").End(xlUp).Row
      With .Range("a1:bh" & lRow)
         .AutoFilter Field:=2, Criteria1:="<>#N/A"
         .AutoFilter Field:=3, Criteria1:="Payout"
      End With
      .AutoFilter.Range.Offset(1).Columns(2).ClearContents
   End With
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,134
Messages
5,466,864
Members
406,506
Latest member
Patb3

This Week's Hot Topics

Top