将筛选工作表中的公式转换为值

Option Explicit

Sub FilteredToValues()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet1") ' adjust!
    Dim srg As Range: Set srg = sws.UsedRange
    
    If Not sws.FilterMode Then ' the worksheet is not in filter mode
        srg.Value = srg.Value ' or whatever
        Exit Sub
    End If
    
    ' When the worksheet is in filter mode, that means that at least
    ' one row is hidden. It also means that at least one row, the header row,
    ' is visible. Thus no error handling is necessary.
    
    Dim arg As Range
    
    ' Convert the visible range.
    
    ' Reference the visible rows.
    Dim vrg As Range: Set vrg = srg.SpecialCells(xlCellTypeVisible)
    
    ' Convert by looping through the areas of the visible range.
    For Each arg In vrg.Areas
        arg.Value = arg.Value
    Next arg
    
    ' Convert the hidden range.
    
    ' Reference the visible cells in the first column.
    Dim vcrg As Range: Set vcrg = Intersect(srg.Columns(1), vrg)
    
    Dim urg As Range, cel As Range
    
    ' Combine the hidden cells of the first column into a range.
    For Each cel In srg.Columns(1).Cells
        If Intersect(cel, vcrg) Is Nothing Then
            If urg Is Nothing Then Set urg = cel Else Set urg = Union(urg, cel)
        End If
    Next cel
    
    ' Reference the hidden rows.
    Dim hrg As Range: Set hrg = Intersect(urg.EntireRow, srg)
    
    ' Convert by looping through the areas of the hidden range.
    For Each arg In hrg.Areas
        arg.Value = arg.Value
    Next arg
    
    MsgBox "Formulas converted to values.", vbInformation
 
End Sub