• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Exhaustive search of a spreadsheet for either formulas or data...

Eloise T

Eloise T

Active Member
I know that [ Ctrl + ` ] gives you a "broad view" of the underlying formulas, etc. of a spreadsheet, but is there a way to use a function like ISBLANK and have it check every cell in a spreadsheet to find any data that is not in the "Main Street" area of the spreadsheet and have it make a list of cell addresses when it finds data...or is that a job for VGA, and if so, what would it look like?

I ask because I have inherited some spreadsheets from the former person that did my job who is no longer around to ask. He was known among his peers for being secretive and hiding formulas. It would be nice to make sure these spreadsheets don't contain important data/information before deleting them without spending hours and hours searching every spreadsheet from A1 to XFD1048576 and beyond.
 
With a SINGLE cell selected in the spreadsheet (makes sure there are no hidden columns/rows first) then press F5 on the keyboard, click Special…, choose what you want to select, click OK. Then you can press th TAB key on the keyboard to visit each cell that's been found.
Last Cell might be a good one to start with as I doubt you can hide anything below/to the right of, that cell.
If you only want to search outside the 'main street' select those areas before pressing F5; you can select both whole columns and whole rows at the same time while holding down the Ctrl key and selecting column headers (the letters A,B C etc, and rows by selecting the row headers (the numbers at the left).

I doubt VGA would be very good but a VBA might be if you if you have an awful lot. Perhaps record a macro of you doing it.
 
This snippet will cycle through all the areas on the active sheet which have formulae in:
Code: 
Sub blah()
Dim myRng As Range
With Range(Range("a1"), Range("A1").SpecialCells(xlCellTypeLastCell))
  On Error Resume Next
  Set myRng = .SpecialCells(xlCellTypeFormulas, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Select
      MsgBox "Formulae here" & vbLf & are.Address(0, 0)
    Next are
  End If
End With
End Sub
You can do something similar with the other options of SpecialCells.
 
I put 10 cells of data in A1:A10 and XFD1000000:XFD1000010.
I copied and pasted your VGA by going to Alt + F11, then F7, then F5 to run.
Nothing happened. What did I do wrong?
 
Yes, just data. Once I changed it to formulas, it worked like a charm. Thanks.

P.S. I can get VBA to start from Alt + F11, F5. Once I'm in the worksheet, how do I initiate F5 (Run) without going to Alt + F11 first?

Also in the VGA, one of the lines of code is: Set myRng = .SpecialCells(xlCellTypeFormulas, 23)

What does the 23 do?
 
Last edited:
From the spreadsheet, Alt+F8 then choose and Run; if the Options… button is active you could assign a keyboard shortcut.

To add constants too:
Code: 
Sub blah()
Dim myRng As Range
With Range(Range("a1"), Range("A1").SpecialCells(xlCellTypeLastCell))
  On Error Resume Next
  Set myRng = .SpecialCells(xlCellTypeFormulas, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Select
      MsgBox "Formulae here" & vbLf & are.Address(0, 0)
    Next are
  End If
  Set myRng = Nothing
  On Error Resume Next
  Set myRng = .SpecialCells(xlCellTypeConstants, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Select
      MsgBox "Constants here" & vbLf & are.Address(0, 0)
    Next are
  End If
End With
End Sub
 
Last edited:
Thank you!

Also in the VGA, one of the lines of code is: Set myRng = .SpecialCells(xlCellTypeFormulas, 23)

What does the 23 do?

Also, if there is a lot of "stuff" (formulas and constants), how do you abort in the midst of the VBA? The ESC key doesn't work.
 
Last edited:
Whatever said:
Also in the VGA, one of the lines of code is: Set myRng = .SpecialCells(xlCellTypeFormulas, 23)

What does the 23 do?
Click to expand...
Ticks all the boxes in the Go To Special dialogue box.
See help where:
XlSpecialCellsValue constants Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2

If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type.


(Seems you can miss out the 23 as it's the default)


Whatever said:
Also, if there is a lot of "stuff" (formulas and constants), how do you abort in the midst of the VBA? The ESC key doesn't work.
Click to expand...
You can Ctrl+Break on the keyboard at the msgbox.

Alternatively, this code will allow you to cancel out at any stage. See comments in the code about changing Sub to For.
Code: 
Sub blah()
Dim myRng As Range
With Range(Range("a1"), Range("A1").SpecialCells(xlCellTypeLastCell))
  On Error Resume Next
  Set myRng = .SpecialCells(xlCellTypeFormulas, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Select
      ans = MsgBox("Formulae here" & vbLf & are.Address(0, 0), 1)
      If ans <> vbOK Then Exit Sub ' change SUB to FOR if instead of exiting altogether you want to just move on to Constants
    Next are
  End If
  Set myRng = Nothing
  On Error Resume Next
  Set myRng = .SpecialCells(xlCellTypeConstants, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Select
      ans = MsgBox("Constants here" & vbLf & are.Address(0, 0), 1)
      If ans <> vbOK Then Exit Sub
    Next are
  End If
End With
End Sub

and another bit of code to look for objects on the sheet:
Code: 
Sub blah2()
For Each obj In ActiveSheet.DrawingObjects
  visibility = obj.Visible  'record whether it's visible.
  obj.Visible = msoTrue  'make it visible anyway.
  Application.Goto obj.TopLeftCell, True  'scroll to where the object is.
  obj.Select
  ans = MsgBox("Object called '" & obj.Name & " at " & obj.TopLeftCell.Address(0, 0), 1)
  If ans <> vbOK Then
    If Not visibility Then
      ans = MsgBox("This object was not vsible." & vbLf & "Leave it visible?", vbYesNo)
      If ans <> vbYes Then obj.Visible = visibility
      Exit Sub  'this will leave the object visible even if it was not visible before.
    End If
  End If
  obj.Visible = visibility  'restore original visibilty
Next obj
End Sub
It doesn't have to be a separate sub, you can tack the innards at the end of the previous sub.
 
You must log in or register to reply here.
Back
Top