Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

macro to remove formulas which references a particular sheet

Dear Experts,

I would like a macro that will evaluate all sheets within the workbook except the "Any Term" sheet and replace any formula that references to the "Any Term" sheet with the value (copy-paste special values).

Many thanks for your advice
0
hendrkle
Asked:
hendrkle
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Chris BottomleyCommented:
How about:

Chris
Sub noForm()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) <> "any term" Then
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
Chris BottomleyCommented:
Just a quick thought ... to avoid leaving the used range selected at all the following selects a single cell in the used range, (the first one).

Chris
Sub noForm()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) <> "any term" Then
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            If ws.UsedRange.Cells.Count > 1 Then
                ws.UsedRange.Cells(1).Select
            End If
            Application.CutCopyMode = False
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub
S

Open in new window

0
 
Dave BrettCommented:
I think you will need something like this which uses a Find to determine all formula links to a sheet name contained in the constant
StrText
To then paste as values only these specific cells

The Find approach is written up at
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2919-Using-Find-and-FindNext-to-efficiently-delete-any-rows-that-contain-specific-text.html

I have modified the code below to
1) Look at formulas not values
2) Used a regular expresssion to test whether the sheet name is of the form
     a) "x!"
     b  "'x'!"

Cheers

Dave  

Option Explicit

Const strText = "Any Term"

Sub ColSearch_DelRows()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim strFirstAddress As String
    Dim lAppCalc As Long
    Dim bParseString As Boolean
    Dim objRegex
    Dim strWSname As String

    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = "[^\w]+"
    strWSname = IIf(objRegex.test(strText), "'" & strText & "'!", strText & "!")

    'No further processing of matches
    bParseString = True

    With Application
        lAppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Any Term" Then

            'a) match string to entire cell, case insensitive
            Set cel1 = ws.UsedRange.Find(strWSname, , xlFormulas, xlPart, xlByRows, , False)

            'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
            If Not cel1 Is Nothing Then
                Set rng2 = cel1
                strFirstAddress = cel1.Address
                Do
                    Set cel1 = ws.UsedRange.FindNext(cel1)
                    Set rng2 = Union(rng2.EntireRow, cel1)
                Loop While strFirstAddress <> cel1.Address
            End If

            'Further processing of found range if required
            If bParseString Then
                If Not rng2 Is Nothing Then rng2.Value = rng2.Value
            End If
            End If
        Next ws

        With Application
            .ScreenUpdating = True
            .Calculation = lAppCalc
        End With

    End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Chris BottomleyCommented:
Ah! I missed that distinction - glad someone's synapses are firing correctly.

Chris
0
 
byronwallCommented:
This is a more direct approach.  For what it's worth.

Sub CheckCells()
    Dim cell As Range
    Dim ws As Worksheet

    For Each ws In Sheets
        If LCase(ws.Name) <> "any term" Then
            For Each cell In ws.UsedRange
                If InStr(1, LCase(cell.Formula), "any term") > 0 Then
                    cell.Value = cell.Value
                End If
            Next cell
        End If
    Next ws
End Sub

Open in new window

0
 
Dave BrettCommented:
>This is a more direct approach.  For what it's worth.

While its shorter code it will be much slower. Plus it should be a more specific search to ensure it is a sheet name

For loops can and should be avoided

Dave
0
 
byronwallCommented:
Maybe we will get a sheet to try them out on.  I doubt the Find approach is significantly faster.  Also your notion that For loops should be avoided is just incorrect.  Any type of loop can be used to mimic any other type of loop: the application determines the correctness.

Given the nature of most spreadsheets, I doubt that searching explicitly for just the sheet name in formulas will yield false positives.  If it does, we could improve the search term by adding the !.

And there is something to be said for shorter code if you ever have to come back to it (or worse, someone else has to).  This is especially true if the shorter code does not rely on regular expressions and the unintuitive results of a Do loop iterating a Find and Union.
0
 
Dave BrettCommented:
>  I I doubt the Find approach is significantly faster.  Also your notion that For loops should be avoided is just incorrect

You do well then to up your experience levels before posting in areas you are unfamiliar with. The other prudent approach would be to test your theories before posting

0
 
TommySzalapskiCommented:
byron, you are partially correct in that for loops are often useful tools. However, Excel vba is a scripted language (not a compiled one) so the more you can use the built-in compiled functions (like 'Find'), the faster your code will go. For loops do not get unrolled or optimized in any way at all in VBA.

If you are coding in a compiled language like C, C++, java, even VB.NET, etc. then use for loops all day long. But in VBA, .vbs, MATLAB, etc. use built-in functions whenever possible. They are, in fact, significantly faster.

Dave, come on, he registered, like, maybe four hours ago. Go easy on him. :)
0
 
hendrkleAuthor Commented:
Thank you all for your great input, I found the solution from brettdj best fit for my purpose and am using i now, thank you!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now