Count value when the value is a merged cell

[Macaron]

Hello, I'm a newbie to type VBA code.
Recently, I would like to create a series of VBA code in order to accelerate work.
Now, I have faced the problems below:
1. I would like to count the number of "LTG" which has 3 merged rows only.(shown in the uploaded image)
2. About the Find Function, I discovered that when "Remark" has merged 2 columns,
Find Function didn't work, so I have to find "*" or unmerged "Remark" columns. Is there any ways I can find the range of Remark columns within border area. I just want to the range which is below "Remark"(Range("X11")) to last row with border area(Range("X42")).

Sub Countnumber()
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet
    Dim LastColumn As Long
    Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = objNewWorkbook.Sheets(1)
    
    For i = 1 To ThisWorkbook.Sheets.Count
    objNewWorksheet.Cells(i, 1) = ThisWorkbook.Sheets(i).Name
    LastColumn = ThisWorkbook.Sheets(i).Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    objNewWorksheet.Cells(i, 6).Value = WorksheetFunction.CountIf(ThisWorkbook.Sheets(i).Columns(LastColumn), "*LTG*")
    
    Next i
End Sub

Hope someone can help me..

 

[Macaron]

The same method will work for columns as for rows. The only difference is that you need to include "all" the columns covered by the merged cells.
(The safest would be to use the full range eg Set rng = sht.UsedRange)
If like your check for rows = 3 you wanted to do the same for columns it would become

If foundOtherTPNCell.MergeArea.Columns.Count = 3

Thanks for your suggestion. I used the code below, it is really helpful. It solved what I cannot count before.

Set rng = sht.UsedRange

Now, I can count two items although "Remark" has merged 2 columns. Other 3 items cannot.
How can I restrict the find function range in "Remark" column and use UsedRange at the same time.

See the part of code below, I used "Usedrmkrng" to restrict finding area to "Remark" columns, but it cannot count specific value in "Remark" columns by using UsedRange

    Dim rmkrng As Range
    Dim Usedrng As Range
    Dim Usedrmkrng As Range

    Set rmkrng = Usedrng.Cells.Find(What:="Remark", LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, MatchByte:=False, SearchFormat:=False)
    
LastColumn = rmkrng.Column

    Set Usedrmkrng = sht.UsedRange.Columns(LastColumn)

'--------------------------------------------------------

   
   
    Set foundMCB = Usedrmkrng.Cells.Find(What:=MCBToFind, After:=rmkrng, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
       
    If Not foundMCB Is Nothing Then
        FirstAddr = foundMCB.Address
    End If
  
    Do Until foundMCB Is Nothing
        If foundMCB.MergeArea.Rows.Count = 3 Then
                cntMerged = cntMerged + 1
                foundMCB.Interior.ColorIndex = 8
        Else
                cntNotMerged = cntNotMerged + 1
                foundMCB.Interior.ColorIndex = 15
        End If
       

        Set foundMCB = Usedrmkrng.FindNext(After:=foundMCB)

       
        If foundMCB Is Nothing Then Exit Do
        If foundMCB.Address = FirstAddr Then Exit Do
    Loop
'--------------------------------------------------------

 

[Alex Blakenburg]

Which columns are merged ?

 

[Macaron]

Which columns are merged ?

X and Y column, I am not going to set the fixed range in X and Y because the “Remark” column is different from every sheet. Sometimes it is a single column. Sometimes, “Remark” columns W and X are merged,

 

[Alex Blakenburg]

How can I restrict the find function range in "Remark" column and use UsedRange at the same time.

See the part of code below, I used "Usedrmkrng" to restrict finding area to "Remark" columns, but it cannot count specific value in "Remark" columns by using UsedRange

X and Y column, I am not going to set the fixed range in X and Y because the “Remark” column is different from every sheet. Sometimes it is a single column. Sometimes, “Remark” columns W and X are merged,

I was using UsedRange to make sure I was picking up all the columns of any merged columns by restricting it to 1 column your code defeats the objective.

Change your code to expand the Remark range you are using to 2 columns as follows:-

Set Usedrmkrng = sht.UsedRange.Columns(LastColumn).Resize(, 2)

 

[Macaron]

I was using UsedRange to make sure I was picking up all the columns of any merged columns by restricting it to 1 column your code defeats the objective.

Change your code to expand the Remark range you are using to 2 columns as follows:-

Set Usedrmkrng = sht.UsedRange.Columns(LastColumn).Resize(, 2)

Awesome!!!! It is work!
I tried to write the code below before, but it didn't work

 Set Usedrmkrng = sht.Columns(LastColumn).UsedRange

Besides, I tried to set findformat in order to find the outline border of cell, but it seems didn't work, my code still count the data that out of the format

With Application.FindFormat.Borders
    .LineStyle = True
    End With

 

[Alex Blakenburg]

Glad it worked for you. If you have more questions just create a new thread.

 

[Macaron]

Glad it worked for you. If you have more questions just create a new thread.

Ok.
All in all, thanks for your help, I have leant a lot on this thread