List all non-formula cells

[DaveWagner_]

Need to generate a list of cell references (including worksheet name and cell location) across an entire workbook for all cells that are not blank and are not formulas.
Essentially, trying to find all "input" cells (it's an inherited workbook). Understand that the solution will likely also return header cells, descriptors, etc.

 

[StephenCrump]

How about conditionally formatting instead?

=NOT(OR(ISFORMULA(A1),ISBLANK(A1)))

Easier to work through the cells directly rather than flipping back and forwards between a list and the cells.

 

[Kaper]

Either in this file or in a separate one) open VBA Editor (Ctrl+F11),
From menu Insert->Module
In opened window paste code:

Sub ListAllNonFormulaCells()
Dim dict As Object, sht As Worksheet, cst As Range, rng As Range, arr() As Variant, i As Long
Set dict = CreateObject("Scripting.Dictionary")
With ActiveWorkbook
  For Each sht In .Worksheets
    Set cst = Nothing
    On Error Resume Next
      Set cst = sht.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If Not cst Is Nothing Then
      For Each rng In cst
        dict.Add "'" & sht.Name & "'!" & rng.Address(0, 0), rng.Value
      Next rng
    Else
      dict.Add "Sheet: " & sht.Name & "!", "has no non-empty, non-formula cells"
    End If
  Next sht
  With .Sheets.Add
    ReDim arr(1 To dict.Count, 1 To 2)
      For i = 0 To dict.Count - 1
         arr(i + 1, 1) = dict.Keys()(i)
         arr(i + 1, 2) = dict.Items()(i)
      Next i
    .Range("A1:B1") = Split("Address;Value", ";")
    .Range("A2").Resize(dict.Count, 2).Value = arr
    .Columns("A:B").AutoFit
  End With
End With
End Sub

Close VBA Editor (Alt+F4)
make sure the file you want to examine is an active one.
Run the macro: Alt+F8 select name ListAllNonFormulaCells and Run

Edit: A solution with conditional formatting is fine, but I know that there are sometimes hidden rows/columns or (rarely) some data is written in such strange locations as XFD1048576

 

[DaveWagner_]

How about conditionally formatting instead?

=NOT(OR(ISFORMULA(A1),ISBLANK(A1)))

Easier to work through the cells directly rather than flipping back and forwards between a list and the cells.

Thanks much! Thought about it, but need a comprehensive list for someone else so they can determine where to source the inputs. Appreciate your response!

 

[DaveWagner_]

Either in this file or in a separate one) open VBA Editor (Ctrl+F11),
From menu Insert->Module
In opened window paste code:

Sub ListAllNonFormulaCells()
Dim dict As Object, sht As Worksheet, cst As Range, rng As Range, arr() As Variant, i As Long
Set dict = CreateObject("Scripting.Dictionary")
With ActiveWorkbook
  For Each sht In .Worksheets
    Set cst = Nothing
    On Error Resume Next
      Set cst = sht.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If Not cst Is Nothing Then
      For Each rng In cst
        dict.Add "'" & sht.Name & "'!" & rng.Address(0, 0), rng.Value
      Next rng
    Else
      dict.Add "Sheet: " & sht.Name & "!", "has no non-empty, non-formula cells"
    End If
  Next sht
  With .Sheets.Add
    ReDim arr(1 To dict.Count, 1 To 2)
      For i = 0 To dict.Count - 1
         arr(i + 1, 1) = dict.Keys()(i)
         arr(i + 1, 2) = dict.Items()(i)
      Next i
    .Range("A1:B1") = Split("Address;Value", ";")
    .Range("A2").Resize(dict.Count, 2).Value = arr
    .Columns("A:B").AutoFit
  End With
End With
End Sub

Close VBA Editor (Alt+F4)
make sure the file you want to examine is an active one.
Run the macro: Alt+F8 select name ListAllNonFormulaCells and Run

Edit: A solution with conditional formatting is fine, but I know that there are sometimes hidden rows/columns or (rarely) some data is written in such strange locations as XFD1048576

Worked very well - thanks much!

 

[Kaper]

Gald to hear that and thanks for marking my post as a solution.
A minor correction:

        dict.Add "''" & sht.Name & "'!" & rng.Address(0, 0), rng.Value

In the previous version excel treats single apostrophe as "just marker that there is a text in a cell".
Like the apostrophe added while entering from keyboard '1/2 to keep it visible as half not Feb 1st (or Jan 2nd - depending on system local settings)

 

[DaveWagner_]

Gald to hear that and thanks for marking my post as a solution.
A minor correction:

        dict.Add "''" & sht.Name & "'!" & rng.Address(0, 0), rng.Value

In the previous version excel treats single apostrophe as "just marker that there is a text in a cell".
Like the apostrophe added while entering from keyboard '1/2 to keep it visible as half not Feb 1st (or Jan 2nd - depending on system local settings)

Is it possible to separate the worksheet name from the cell reference, in two columns?

 

[Kaper]

Sure, Let's use Text-to-Columns (but not by button in Data, but from VBA):

Sub ListAllNonFormulaCells()
Dim dict As Object, sht As Worksheet, cst As Range, rng As Range, arr() As Variant, i As Long
Set dict = CreateObject("Scripting.Dictionary")
With ActiveWorkbook
  For Each sht In .Worksheets
    Set cst = Nothing
    On Error Resume Next
      Set cst = sht.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If Not cst Is Nothing Then
      For Each rng In cst
        dict.Add "''" & sht.Name & "'!" & rng.Address(0, 0), rng.Value
      Next rng
    Else
      dict.Add "''" & sht.Name & "'", "has no non-empty, non-formula cells"
    End If
  Next sht
  With ThisWorkbook.Sheets.Add
    ReDim arr(1 To dict.Count, 1 To 2)
      For i = 0 To dict.Count - 1
         arr(i + 1, 1) = dict.Keys()(i)
         arr(i + 1, 2) = dict.Items()(i)
      Next i
    .Range("A2").Resize(dict.Count, 2).Value = arr
    .Columns("B:B").Insert Shift:=xlToRight
    .Range("A2").Resize(dict.Count, 1).TextToColumns Destination:=.Range("A2"), DataType:=xlDelimited, OtherChar:="!", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    .Range("A1:C1") = Split("Sheet;Cell;Value", ";")
    .Columns("A:C").AutoFit
  End With
End With
End Sub