Combine two Private Sub Worksheet_Change formulas

cevors

New Member
Joined
Aug 1, 2019
Messages
4
Good morning,

I am in need of combining two Private Sub formulas together.

Individually they work, and are as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then
Range("F3").Value = ""
End If
If Target.Address = "$F$3" Then
Range("F4").Value = ""
End If
End Sub


AND...


Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub


I thought combining them would be pretty simple, and I've tried a few different tweaks, but I always get an error.

Any help would be greatly appreciated, so much.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
Welcome to the Board!

Did you try:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Block 1
    If Target.Address = "$F$2" Then
        Range("F3").Value = ""
    End If
    If Target.Address = "$F$3" Then
        Range("F4").Value = ""
    End If
    
'   Block 2
    Sheets("Sheet3").AutoFilter.ApplyFilter

End Sub
Also note, that the way you currently have it written, and change to cell F2 will clear both F3 and F4.
 

cevors

New Member
Joined
Aug 1, 2019
Messages
4
Good morning -- and thank you!

I did try your suggestion, again just now and before, and it doesn't seem to work as intended.
I do need both If statements in the first formula -- as I need a change in F2 to clear out F3 and F4, and any change to F3 (separate and apart from a change to F2, perhaps) to clear out F4.

And then I essentially want any change to the data to be filtered automatically, pursuant to filters that currently exist on the sheet.


I appreciate your help, and I hope we can get this ironed out.

cpe
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
How are cells F2 and F3 being updated? Manually, or in another manner?
Are you only ever updating one cell at a time (i.e. not copying and paste a WHOLE RANGE of cells at once)?
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
Good morning -- and thank you!

I did try your suggestion, again just now and before, and it doesn't seem to work as intended.
I do need both If statements in the first formula -- as I need a change in F2 to clear out F3 and F4, and any change to F3 (separate and apart from a change to F2, perhaps) to clear out F4.

And then I essentially want any change to the data to be filtered automatically, pursuant to filters that currently exist on the sheet.


I appreciate your help, and I hope we can get this ironed out.

cpe

How about this?


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$2" Then
        Range("F3").Value = ""
    End If
    If Target.Address = "$F$3" Then
        Range("F4").Value = ""
    End If
    With Sheets("Sheet1")
        Select Case .AutoFilterMode
            Case False
                .Cells(1, .UsedRange.Columns.Count).Select
                Selection.AutoFilter
        End Select
    End With
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
Also, which Sheet are you on? In the initial post, it had a reference to Sheet3.
Are the filters on the same sheet you are making the updates to?
 

Forum statistics

Threads
1,086,138
Messages
5,388,055
Members
402,099
Latest member
The OB1s and 2s

Some videos you may like

This Week's Hot Topics

Top