Remove formula only visible cells

Rpaikh

New Member
Joined
Jul 28, 2019
Messages
27
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.




Code:
Sub Removeformu()
    Dim ws As Worksheet


    Set ws = ThisWorkbook.Sheets("SPI)


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

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
One way (Filtered cells)

Code:
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
Code:
 Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)
use
Code:
 Set Rng = Selection.SpecialCells(xlCellTypeVisible)
 
Last edited:

Rpaikh

New Member
Joined
Jul 28, 2019
Messages
27
Thank very much Yongle.


This perfectly work as expected !


One way (Filtered cells)

Code:
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
Code:
 Set Rng = Sheets("SPI").UsedRange.SpecialCells(xlCellTypeVisible)
use
Code:
 Set Rng = Selection.SpecialCells(xlCellTypeVisible)
 

Rpaikh

New Member
Joined
Jul 28, 2019
Messages
27
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Rpaikh

New Member
Joined
Jul 28, 2019
Messages
27
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,135
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top