Need an IF addition to code

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How about changing the last IF, changing the "=" to "<>", thus:
If WorksheetFunction.CountIf(Sh.Columns("C:C"), Num) <> 0 Then
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try changing this:
Code:
 If Not Sh.Name = ActiveSheet.Name And Not Sh.Name = "DataSheet" Then

to this:
Code:
    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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,347
Messages
6,136,045
Members
449,982
Latest member
josephinelinnea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top