excel - 是否可以找到每个 excel 行的最后一个元素距离？

Question

考虑以下矩阵:

    Col1    Col2    Col3    Col4    Col5    Col6    Col7
R1   x       x                        x
R2           x                x              x
R3                                                    x
R4           x        x       x
R5                                    x

现在使用 VBScript 或 ADO 是否可以找到每行最后一个元素的距离？ 距离定义距离只不过是最后一个元素放置在给定矩阵中的单元格数。例如说 -

距离(R1)=5

Dist(R4)=4 同理

我尝试了以下方法:

Option Explicit

Dim ArrayListTaskDetails : Set ArrayListTaskDetails = CreateObject("System.Collections.ArrayList")
Dim i,colcount

i=2
Do while i < = objExcel1.Application.WorksheetFunction.CountA(ob.Rows(1))

colcount=objExcel1.Application.WorksheetFunction.CountA(ob.Rows(i))
ArrayListTaskDetails.Add(colcount)

i=i+1
Loop

ArrayListTaskDetails.Sort()
i=ArrayListTaskDetails.Count
MsgBox("HighestColumnNumner:" & ArrayListTaskDetails(i-1))

但它不起作用，因为无法处理空白之间的问题。

纳克斯，

Answer 1

另一种方式...在 VBSCRIPT 中使用公式

Dim oXLApp, oXLWb, oXLWs

Set oXLApp = CreateObject("Excel.Application")

'~~> Show Excel
oXLApp.Visible = True

'~~> Open files (Change as applicable)
Set oXLWb = oXLApp.Workbooks.Open("C:\MyFile.xlsx")
Set oXLWs = oXLWb.Sheets(1)

Dim lRow, i, tmp

With oXLWs
    lRow = .Range("A" & .Rows.Count).End(-4162).Row

    For i = 2 To lRow
        tmp = oXLApp.Evaluate("=ADDRESS(ROW(A" & i & "),MATCH(INDEX($" & i _
        & ":$" & i & ",MAX(IF($A" & i & ":$K" & i & "<>"""",COLUMN($A" & i _
        & ":$K" & i & ")))),A" & i & ":K" & i & "),1)")

        MsgBox .Range("A" & i).Value & " :- " & .Range(tmp).Column - 1
    Next
End With

截图



跟进

根据要求

Dim oXLApp, oXLWb, oXLWs

Set oXLApp = CreateObject("Excel.Application")

'~~> Hide Excel
oXLApp.Visible = True

'~~> Open files
Set oXLWb = oXLApp.Workbooks.Open("C:\MyFile.xlsx")
Set oXLWs = oXLWb.Sheets(1)

Dim lRow, i, tmp, MyArray, ColNo, ReturnName

With oXLWs
    lRow = .Range("A" & .Rows.Count).End(-4162).Row

    ColNo = .Columns.Count

    ReturnName = Split(.Cells(, ColNo).Address, "$", -1,1)(1)

    For i = 2 To lRow
        tmp = oXLApp.Evaluate("=ADDRESS(ROW(A" & i & "),MATCH(INDEX($" & i _
        & ":$" & i & ",MAX(IF($A" & i & ":$" & ReturnName & i & _
        "<>"""",COLUMN($A" & i & ":$" & ReturnName & i & ")))),A" & _
        i & ":" & ReturnName & i & "),1)")

        msgbox .Range("A" & i).Value & " :- " & .Range(tmp).Column - 1
    Next
End With

更多关注

Option Explicit

Dim oXLApp, oXLWb, oXLWs
Dim lRow, i, tmp, MyArray, ColNo, ReturnName

Set oXLApp = CreateObject("Excel.Application")

'~~> Hide Excel
oXLApp.Visible = True

'~~> Open files
Set oXLWb = oXLApp.Workbooks.Open("C:\MyFile.xlsx")

'~~> Set the Sheet 1 as sheet1
Set oXLWs = oXLWb.Sheets(1)

With oXLWs
    '~~> Get the last row in the worksheet
    lRow = .Cells.Find("*", .Range("A1"), -4123, 2, 1, 2).Row

    '~~> Get the total col count
    ColNo = .Columns.Count

    '~~> This will return the column name from column number
    ReturnName = Split(.Cells(, ColNo).Address, "$", -1, 1)(1)

    For i = 1 To lRow
        '~~> We are using the Evaluate to calculate the formula
        '~~> which will find our result
        tmp = oXLApp.Evaluate("=ADDRESS(ROW(A" & i & "),MATCH(INDEX($" & i _
        & ":$" & i & ",MAX(IF($A" & i & ":$" & ReturnName & i & _
        "<>"""",COLUMN($A" & i & ":$" & ReturnName & i & ")))),A" & _
        i & ":" & ReturnName & i & "),1)")

        '~~> This will return the column number
        MsgBox "Last Col in Row " & i & " is " & .Range(tmp).Column
    Next
End With