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")).
![WhatsApp Image 2021-11-14 at 12.43.54 PM.jpeg WhatsApp Image 2021-11-14 at 12.43.54 PM.jpeg](https://www.mrexcel.com/board/data/attachments/51/51184-765d0da13d81e1b74c162e5c8c63dbac.jpg?hash=dl0NoT2B4b)
Hope someone can help me..
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")).
![WhatsApp Image 2021-11-14 at 12.43.54 PM.jpeg WhatsApp Image 2021-11-14 at 12.43.54 PM.jpeg](https://www.mrexcel.com/board/data/attachments/51/51184-765d0da13d81e1b74c162e5c8c63dbac.jpg?hash=dl0NoT2B4b)
VBA Code:
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..