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

 

[mikerickson]

This should get you started.
It would go into some other workbook, not the one being tested.
The Set wb = Workbooks("Book2") needs to be expanded. How do you want to indicate which workbook to check?
As written, if a sheet is an exception (i.e. there is data in D7:D50), that sheet is hidden and listed on the (new) Exceptions sheet and the cell is hyplinked to the offending sheet.
But, hyperlinks to hidden sheets do nothing. That should be adjusted to your taste.
Also the ExceptionSheet is being put into the workbook being tested.
Also, if you run this once and then again, it will fall over trying to create a second Exceptions sheet.
These all can be tweeked when your requirments are clarified.

Sub CheckSheets()
    Dim wb As Workbook, oneSheet As Worksheet
    Dim ExceptionSheet As Worksheet, Flag As Boolean
    Dim arrExceptions() As String, Pointer As Long, i As Long
    
    Set wb = Workbooks("Book2")
    
    ReDim arrExceptions(1 To wb.Sheets.Count, 1 To 1)
    arrExceptions(1, 1) = "Sheets With Data"
    Pointer = 1
    For Each oneSheet In wb.Worksheets
        If Application.CountA(oneSheet.Range("D7:D50")) Then
            oneSheet.Visible = xlSheetHidden
            Flag = True
            Pointer = Pointer + 1
            arrExceptions(Pointer, 1) = oneSheet.Name
        Else
            oneSheet.Visible = xlSheetVisible
        End If
    Next oneSheet
    If Flag Then
        ' at least one sheet with data
        With wb.Worksheets
            Set ExceptionSheet = .Add(before:=.Item(1))
        End With
        With ExceptionSheet
            .Name = "Exceptions"
            .Range("A1").Resize(Pointer, 1).Value = arrExceptions
        
            For i = 2 To Pointer
                With .Cells(i, 1)
                    .Hyperlinks.Add anchor:=.Cells(1, 1), Address:="", SubAddress:= _
                        .Parent.Parent.Sheets(.Value).Range("D7:d50").Address(, , , True), TextToDisplay:=.Value
                End With
            Next i
        End With
    Else
        ' no sheets have data
    End If
End Sub

 

[DavidG007]

Hi Mike,

Thank you for your reply, it is 99% spot on, I tweaked a couple of points, but the only minor issue is the range that is checked in order to hide/visible. The current range, D7:D50, looks like the code actually checks for anything on those line rather than just Column D?

Any ideas?

Thank you again for your very quick response and help

David

 

[DavidG007]

Hi Mike, the code actually does check the range correctly, my issue is that there must be something in those cells hence why those sheets are included. I have taken a sheet as an example of check the range but nothing is contained?

 

[mikerickson]

Might there be formulas that return ""?

If so you might want to use

If 0 < Application.CountIf(oneSheet.Range("D7:D50"), "<>") Then

 

[DavidG007]

Hi Mike,

Thanks again for helping..... I just tried the revised formulas and still no joy.

I have however applied the formula manually to the worksheet and saw that the count was incorrect, but, when I click into the cell then move to the next cell the formula count corrects itself. I can forward you the workbook if that would help?

Thanks, David

 

[DavidG007]

Hi Mike, just a quick followup;

its look like there are hidden carriage returns contained with the fixed range that we are using so maybe needs a Clean String function to run first? but that is above my pay grade and have no idea how to incorporate that into your script?

thanks again, David

 

[mikerickson]

Try this

For Each oneSheet In wb.Worksheets
        oneSheet.Range("D7:D50").Replace What:=vbLf, Replacement:=vbNullString, _
                         LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

        If Application.CountA(oneSheet.Range("D7:D50")) Then
            oneSheet.Visible = xlSheetHidden
            Flag = True
            Pointer = Pointer + 1
            arrExceptions(Pointer, 1) = oneSheet.Name
        Else
            oneSheet.Visible = xlSheetVisible
        End If
    Next oneSheet

 

[DavidG007]

Hi Mike,

Just tried the update you sent over and no joy, the sheets with hidden characters are still having the issue?

Thanks, David

 

[mikerickson]

What is the hidden character? What does =CODE(D5) return when put in a cell (if D5 had the invisible chr)