Column Names with column Nos in Merged Area Displayed but header Name without and after merge cells did not display

[SamDsouza]

Hello

This is in continuation with following thread

Column names with Column Nos not able to display in Msg Box from different worksheets

Hello I am trying to get values(Names) of a single row and with its respective columns from different worksheets I am not able to get part of msg in Msg box as it only displays that part of Last sheet ie data of first few sheets with names and column numbers and not able to display but...

www.mrexcel.com

.
Somehow i really missed the idea of merged cells in the header row. Approx the code in the above thread helped me to check 35 odd worksheets with what i desired to display header names with column nos. suddenly one Worksheet appeared with header row with merged cells of 4 columns and another sheet with blank cells in header row after each header name.
Case 1 : Found there were merged cells in header Row which gave me its address but next column after the merged cells in header row with header name was not displayed.
Case 2: There were blank cells after each header name in the header row so naturally it did not display the header names of other column because of this syntax
If wks.Cells(RowNumber, x).Value = "" Then Exit For

Can anyone help me to correct the below code with above two issues

Private Sub CommandButton1_Click()
  Dim wks As Worksheet
  Dim cl As Long, RowNumber As Long, x As Long
  Dim ColValStr As String, msg As String
 
  RowNumber = 3
 
  For Each wks In Worksheets
    ColValStr = ""
    msg = "Name of Sheet: " & wks.Name
   
    cl = wks.Rows(RowNumber).Find(What:="*", _
                        After:=wks.Cells(RowNumber, Columns.Count), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False).Column
   
    For x = cl To Columns.Count
      If wks.Cells(RowNumber, x).Value = "" Then Exit For
      ColValStr = ColValStr & vbCrLf & wks.Cells(RowNumber, x).Value & " " & x
    Next x
    MsgBox msg & ColValStr
  Next wks
End Sub

Your guidance will be appreciated
SamD
131

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).