VBA to perform check every worksheet for content

[DavidG007]

Hi,

Hopefully this will be an easy question. I have a system based control report that outputs to excel. The number of worksheets will vary each time the report is exported. What I need to do is the following.

Run a VBA script that will check every worksheet (can be in excess of 50 worksheets) and check a fixed data range, 'D7:D50', and if the cells are NOT blank then produce on a New Worksheet that will list (by hyperlink) each worksheet that has data within that set range, or even hide the sheets that return 'false'?

The VBA script would not be embedded as the file is system exported, but, columns and ranges are always fixed.

Any ideas on where to start or any examples available?

A huge thank you for any replies, all would be most welcome

 

[DavidG007]

Hi Mike, it returns 68, thanks

 

[DavidG007]

Hi Mike,

I have found the code below that works for cleansing the data but I don't how to incorporate into your code?

Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub

Function CleanString(StrIn As String) As String

' characters, including carriage returns BUT NOT linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character

   Dim iCh  As Integer
   Dim Ch   As Integer      'a single character to be tested
   CleanString = StrIn
   For iCh = 1 To Len(StrIn)
      Ch = Asc(Mid(StrIn, iCh, 1))
      If Ch < 32 And Ch <> 10 Then
         'remove special character
         CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
      Exit Function
      End If
   Next iCh

End Function

 

[DavidG007]

Hi Mike,

I hope you don't mind but just thought I would see if you had had any time to take a quick look at the last post regarding incorporating the 'Clean' code above that should fix the code that you so kindly wrote for me?

Thanks, David