Call Private Function under Private Sub

Hello there,

I want to run these code both at the same time but I don't have much experience with VBA. Please help

(code1)Private Sub Worksheet_Change(ByVal Target As Range)

lr = ActiveSheet.UsedRange.Rows.Count

If (Target.Column = Range("Response").Column) Then

With Application
.EnableEvents = False
.ScreenUpdating = False

End With
Code(2) Private Function IsFiltered(SheetName As Worksheet) As Boolean

Dim lr As Long
Dim n As Integer

Thank you so much! To save some space, I didn't put the completed code, just the first part of it. Thanks!

Hello,

so I have a table with 10 columns, the first code will help me to set value based on the filters. All columns got filtered will return the same values. However, if I only use the first code, even when there's no filter used, all the value will be the same. That's why I created the second code, to say that if there's no filter used, if you pick the value for one row, it will apply to only that row, not for the whole column.

I tried both of them it worked well separately. Could you please tell me how to connect them please? Thanks so much!

I think what you mention about calling the IsFiltered is correct. So where and how you put that "If Is Filtered(ActiveSheet) then:??

1st Code
Private Sub Worksheet_Change(ByVal Target As Range)



lr = ActiveSheet.UsedRange.Rows.Count

If (Target.Column = Range("Response").Column) Then

With Application
.EnableEvents = False
.ScreenUpdating = False

End With

'Declare range you are checking for visible cells - should be dynamic
Dim r As Range

'Declare output range variable
Dim visibleCells As Range

'Set range checking against
Set r = Range(Cells(2, Target.Column), Cells(lr, Target.Column))

'Set visible range
Set visibleCells = r.SpecialCells(xlCellTypeVisible)

'Loop to apply condition to each cell that is visible

For Each cell In visibleCells
cell.Value = Target.Value
Next

With Application
.EnableEvents = True
.ScreenUpdating = True

End With

End If

If IsFiltered(ActiveSheet) Then

End If




End Sub

2nd code

Private Function IsFiltered(SheetName As Worksheet) As Boolean

Dim lr As Long
Dim n As Integer

'Count Columns
lr = SheetName.UsedRange.Columns.Count

'Loop through columns to check if each column is filtered
For n = 1 To lr

If SheetName.AutoFilter.Filters(n).On Then
'Skip to end - your data is filtered if this is True
GoTo FiltersOn
Else
End If

Next n

'Code to Exit Sub - your data is not filtered
IsFiltered = False
Exit Function

'Code for applying IsFiltered = True when data is filtered
FiltersOn:
IsFiltered = True

End Function


Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If IsFiltered(ActiveSheet) Then
    

    lr = ActiveSheet.UsedRange.Rows.Count
    
    If (Target.Column = Range("Response").Column) Then
    
    With Application
         .EnableEvents = False
         .ScreenUpdating = False
    
    End With
    
'Declare range you are checking for visible cells - should be dynamic
    Dim r As Range

'Declare output range variable
    Dim visibleCells As Range

'Set range checking against
    Set r = Range(Cells(2, Target.Column), Cells(lr, Target.Column))

'Set visible range
    Set visibleCells = r.SpecialCells(xlCellTypeVisible)

'Loop to apply condition to each cell that is visible

    For Each cell In visibleCells
        cell.Value = Target.Value
    Next
    
    With Application
         .EnableEvents = True
         .ScreenUpdating = True
         
    End With
    
    End If
    
    If IsFiltered(ActiveSheet) Then
    
    End If
    
    
    

End Sub

Private Function IsFiltered(SheetName As Worksheet) As Boolean

Dim lr As Long
Dim n As Integer

'Count Columns
lr = SheetName.UsedRange.Columns.Count

'Loop through columns to check if each column is filtered
    For n = 1 To lr
    
        If SheetName.AutoFilter.Filters(n).On Then
           'Skip to end - your data is filtered if this is True
           GoTo FiltersOn
        Else
        End If
     
    Next n

 'Code to Exit Sub - your data is not filtered
    IsFiltered = False
Exit Function

'Code for applying IsFiltered = True when data is filtered
FiltersOn:
    IsFiltered = True

I tried to follow your instruction but it didn't work, maybe I put them in the wrong position? Could you please advise and help to combine that for me please. Many thanks!