Remove formula only visible cells

[Rpaikh]

Hello,

I basically use below code to remove formula for the entire sheets.

But is anyone happen to know if we could have a VBA code to remove formula only the selected visible cells ?
Sometimes I need to keep some formula by hiding or filter it out.

Sub Removeformu()
    Dim ws As Worksheet


    Set ws = ThisWorkbook.Sheets("SPI)


    ws.UsedRange.Value = ws.UsedRange.Value
End Sub

 

[Yongle]

One way (Filtered cells)

Sub Rpaikh()
    Dim Cel As Range, Rng As Range
    Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
            For Each Cel In Rng
                Cel.Value = Cel.Value
            Next
        .Calculation = xlCalculationAutomatic
    End With
End Sub

If you want to filter AND then apply to manually selected range of visible filtered cells
instead of

 Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)

use

 Set Rng = Selection.SpecialCells(xlCellTypeVisible)

 

[Rpaikh]

Thank very much Yongle.


This perfectly work as expected !

One way (Filtered cells)

Sub Rpaikh()
    Dim Cel As Range, Rng As Range
    Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
            For Each Cel In Rng
                Cel.Value = Cel.Value
            Next
        .Calculation = xlCalculationAutomatic
    End With
End Sub

If you want to filter AND then apply to manually selected range of visible filtered cells
instead of

 Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)

use

 Set Rng = Selection.SpecialCells(xlCellTypeVisible)

 

[Rpaikh]

Hello Yongle - Just one quick question


For .Calculation = xlCalculationManual and .Calculation = xlCalculationAutomatic


Is this just to speed up loops of Cel.Value = Cel.Value for the Rng range am I correct ?

 

[Yongle]

For .Calculation = xlCalculationManual and .Calculation = xlCalculationAutomatic
Is this just to speed up loops of Cel.Value = Cel.Value for the Rng range am I correct ?

The loop in the code means that values are being amended ONE cell at a time
(By default) a workbook auto-recalculates formulas EVERY time a value in any cell changes
Setting calculation mode to manual avoids those muliple recalculations
Setting calculation mode back to automatic (after ALL values have been amended) puts calculation mode back to normal and results in only ONE recalculation event
So, yes it speeds up the loop

Setting ScreenUpdating to False prevents the screen from updating until the sub finishes running. ONE update instead of EVERY time any cell changes. Again speeding up the loop. Screen updating does not require resetting in the code - that happens automatically.

 

[Rpaikh]

Thank you once again for a clear explanation.

The loop in the code means that values are being amended ONE cell at a time
(By default) a workbook auto-recalculates formulas EVERY time a value in any cell changes
Setting calculation mode to manual avoids those muliple recalculations
Setting calculation mode back to automatic (after ALL values have been amended) puts calculation mode back to normal and results in only ONE recalculation event
So, yes it speeds up the loop

Setting ScreenUpdating to False prevents the screen from updating until the sub finishes running. ONE update instead of EVERY time any cell changes. Again speeding up the loop. Screen updating does not require resetting in the code - that happens automatically.