Procedure before my code if some sheets doesn't contain data

[Mussa]

Hi
I search for procedure to put before my code .
if there are some sheets don't contain data especially in row1 even if the sheet contains data in any cell exept the row1 then populate message box " sorry you can't implement the code and some sheets don't contain headers in row1"
and exit sub
thanks

 

[Cubist]

Something like this perhaps.

Sub CheckSheetsForHeaders()
    Dim ws As Worksheet
    Dim hasDataInRow1 As Boolean
    Dim hasHeadersInRow1 As Boolean
    
    hasDataInRow1 = False
    hasHeadersInRow1 = False
    
    For Each ws In ThisWorkbook.Worksheets
        If Application.WorksheetFunction.CountA(ws.Rows(1)) > 0 Then
            hasDataInRow1 = True
            If Application.WorksheetFunction.CountA(ws.Rows(1)) = ws.UsedRange.Columns.Count Then
                hasHeadersInRow1 = True
            Else
                MsgBox "Sheet '" & ws.Name & "' doesn't contain headers in row 1."
                Exit Sub
            End If
        End If
    Next ws
    
    If Not hasDataInRow1 Then
        MsgBox "No sheet contains data in row 1."
        Exit Sub
    End If
    
    ' If code execution reaches here, it means all sheets contain data in row 1 with headers
    ' Put your main code here
    
End Sub

 

[Mussa]

Thanks
unfortunately doesn't show message as you did in the code!
it runs my code even if some sheets are empty in row1.

 

[Akuini]

Try this:

Sub Check_Blank_Sheets()
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim c As Range
    Dim tx As String
    For Each ws In ThisWorkbook.Worksheets
        Set c = ws.Rows(1).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If c Is Nothing Then
            tx = tx & vbLf & ws.Name
            Flag = True
        End If
    Next ws
    
    If Flag Then
        MsgBox "First row of this sheet(s) is blank: " & tx
        Exit Sub
    End If
   
    'Put your code here
    
End Sub

 

[Mussa]

@Akuini
really great !
sorry I'm not clear from th bginning !
I would if there is no headers A:F or just header in any column(A1:B1) or( A1,C1) or( A1,C1,D1 )then should show the message .
if the whole range A1:F1 contains header then run my code .

 

[Akuini]

if the whole range A1:F1 contains header then run my code .

Try:

Sub Check_Blank_Sheets_2()
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim tx As String
    For Each ws In ThisWorkbook.Worksheets
        If WorksheetFunction.CountA(ws.Range("A1:F1")) < 6 Then
            tx = tx & vbLf & ws.Name
            Flag = True
        End If
    Next ws
    
    If Flag Then
        MsgBox "Some headers of this sheet(s) is blank: " & tx
        Exit Sub
    End If
   
    'Put your code here
    
End Sub

 

[Mussa]

thank you so much , Akuini.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.