Hi all,

I wrote two VBA codes:

1st: generate all sames values for all filtered columns
2nd: when there's no filter applied, the selected value applies to only one row, not for the whole column as 1st code

I tried both code and they work perfectly but have two run twice SEPARATELY.

Is there a way we can combine this so it runs by itself all together

Please find code below.

Thanks so much!!!


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

     

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