excel - 特定工作表的列数

Question

Function getMaxColumnCount() As Long

         Dim lColumn As Long, sh As Worksheet
         Set sh = ThisWorkbook.Sheets("Sheet1")
        lColumn = sh.Cells(6, Columns.count).End(xlToLeft).Column
        getMaxColumnCount = lColumn

End Function

在我的 sheet1 中，我的数据从第 6 行和第 2 列开始，到第 25 列结束。
使用上述函数，它返回最大列数为 25

现在我有一个场景，比如有两组数据

在同一张表中只有两组数据
第一组数据 (6,2) 到 (6,25)
第二组数据从 (6,30) 到 (6,50) 我希望输出为 20 使用上面的代码我应该做什么改变

Answer 1

So just to be clear, if your first set of data has 23 columns and 2nd set has 20 columns and lets say there is a 3rd set which has 5 columns then you want 5 as an answer? Also will all set have at least one blank cell in betwen? – Siddharth Rout 5 mins ago Edit

@Siddharth Rout yes it is like that only – uservba12 2 mins ago

这是你正在尝试的吗？
逻辑 :获取最后一列，然后找到之前的空白单元格。减去以获得列数。
代码 :

Option Explicit

Function GetLastSetColumnCount() As Long
    Dim lCol As Long
    Dim BlankCol As Long
    Dim i As Long
    Dim ws As Worksheet
    
    Set ws = Activesheet '~~> OR relevant sheet
    
    With ws
        lCol = .Cells(6, .Columns.Count).End(xlToLeft).Column
        
        For i = lCol To 1 Step -1
            If Len(Trim(.Cells(6, i).Value)) = 0 Then
                BlankCol = i
                Exit For
            End If
        Next i
    End With
    
    GetLastSetColumnCount = lCol - BlankCol
End Function