Need an IF addition to code

[ndendrinos]

Hello to all

Need to add to this routine the following “IF”
And that is if a sheet is empty do not list in the newly formed sheet.
Right now if all number between MaxNum and MinNum for each sheet in the WB are there
what I get on the newly created sheet is the names of the sheets that are completely empty.

Here is the macro:

Public Sub CheckMissingFiles()
Dim Sh As Worksheet
Dim MinNum, MaxNum, Num As Integer

' CREAT A NEW SHEET FOR LIST
Sheets.Add before:=ActiveSheet

'INSPECT EACH SHEET IN BOOK
For Each Sh In Worksheets
If Not Sh.Name = ActiveSheet.Name And Not Sh.Name = "DataSheet" Then


'ESTABLISH MIN AND MAX NUMBERS FOR SHEET
MinNum = WorksheetFunction.Min(Sh.Columns("C:C"))
MaxNum = WorksheetFunction.Max(Sh.Columns("C:C"))

'LOOP THRU ALL VALUES BETWEEN MIN AND MAX , IN CURR SHEET
For Num = MinNum To MaxNum
' IF NOT FOUND RECORD ON NEW SHEET THEN LIST ON SHEET
If WorksheetFunction.CountIf(Sh.Columns("C:C"), Num) = 0 Then
NxRow = Cells(65536, 1).End(xlUp).Row + 1
Cells(NxRow, 1).Value = Sh.Name
Cells(NxRow, 2).Value = Num
End If
Next Num


End If
Next Sh 'LOOP TO NEXT SHEET
End Sub


Thank you

 

[RalphA]

How about changing the last IF, changing the "=" to "<>", thus:
If WorksheetFunction.CountIf(Sh.Columns("C:C"), Num) <> 0 Then

 

[ndendrinos]

Hello ralph and thanks for your help.
Your solution addresses my problem and indeed the empty sheets do not get listed on the newly created sheet... but now the sheets that are populated with sequential data get listed instead and that defeats the purpose of the macro.
If I revert to the = instead of <> I get back to square one .
Looks like something else need be added here in addition to your solution.
Thank you again.

 

[RalphA]

I am not a VBA person, so, I'm not sure just what you are after. I do know that, if the sheet is empty, you do not want it listed. And, what I believe you want is an IF(AND()) statement. We now know that the first part of the the AND() function should be that the sheet is not empty. What is the second part? Once I know this, maybe I can complete the AND() function.

 

[Von Pookie]

Try changing this:

 If Not Sh.Name = ActiveSheet.Name And Not Sh.Name = "DataSheet" Then

to this:

    If Not Sh.Name = ActiveSheet.Name And _
    Not Sh.Name = "DataSheet" And _
    WorksheetFunction.CountA(Sh.Cells) <> 0 Then

The CountA function counts the number of cells that are not blank in the specified range. In this case, I used it to count the number of cells that contain values on each sheet. If sheet is completely blank, this returns 0 and that sheet will not be listed.

 

[ndendrinos]

Thank you gentlemen...
This is what I have now and still have the same problem.

Public Sub CheckMissingFiles()
Dim Sh As Worksheet
Dim MinNum, MaxNum, Num As Integer

' CREAT A NEW SHEET FOR LIST
Sheets.Add before:=ActiveSheet

'INSPECT EACH SHEET IN BOOK
For Each Sh In Worksheets
If Not Sh.Name = ActiveSheet.Name And _
Not Sh.Name = "DataSheet" And _
WorksheetFunction.CountA(Sh.Cells) <> 0 Then

'ESTABLISH MIN AND MAX NUMBERS FOR SHEET
MinNum = WorksheetFunction.Min(Sh.Columns("C:C"))
MaxNum = WorksheetFunction.Max(Sh.Columns("C:C"))

'LOOP THRU ALL VALUES BETWEEN MIN AND MAX , IN CURR SHEET
For Num = MinNum To MaxNum
' IF NOT FOUND RECORD ON NEW SHEET THEN LIST ON SHEET
If WorksheetFunction.CountIf(Sh.Columns("C:C"), Num) = 0 Then
NxRow = Cells(65536, 1).End(xlUp).Row + 1
Cells(NxRow, 1).Value = Sh.Name
Cells(NxRow, 2).Value = Num
End If
Next Num


End If
Next Sh 'LOOP TO NEXT SHEET
End Sub

 

[ndendrinos]

BIG APOLOGIES

Sorry... not my fault... the macro now works great... I had assigned a button to it and just learned that editing the macro and running it from the same button somehow does not switch to the edited version ... either that or I forgot to save when I pasted the new solution... I tried it by going to Tools/Macro and choosing it and it is perfect.
So again my apologies and have a great weekend
Regards,
Nick