Solved

macro to remove formulas which  references  a particular sheet

Posted on 2010-11-25
10
365 Views
Last Modified: 2012-05-10
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
Comment
Question by:hendrkle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34215817
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
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 100 total points
ID: 34215821
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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 300 total points
ID: 34216470
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34216528
Ah! I missed that distinction - glad someone's synapses are firing correctly.

Chris
0
 
LVL 3

Assisted Solution

by:byronwall
byronwall earned 100 total points
ID: 34220830
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34220858
>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
 
LVL 3

Expert Comment

by:byronwall
ID: 34220888
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34220978
>  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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34221056
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
 

Author Closing Comment

by:hendrkle
ID: 34221448
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question