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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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