如何为VBA中的每一个交替列找到最后的第二行？

Question

是否有一种方法可以找到工作表中交替列的最后第二行在这种情况下是Time1、Time2等的最后第二行，然后将其复制并粘贴到另一个工作表上，如图所示？通常，当每个列具有相同的行数时，使用LastRow=.range（“e”&.rows.count).end（xlUp）.row就可以完成这项工作，但这一次，每个交替列具有不同的行数，所以我不能使用LastRow定义。下面是我尝试的代码，我被如何操作以获得每个交替列的最后第二行所困扰。

Sub main()

Dim i As Long

Dim j As Long

Dim lastrowv1 As Long

Dim LastColumn As Long


i = 1 'i for column
j = 2  'j for row



Set sht = ActiveWorkbook.Sheets("current")
LastColumn = sht.Cells(1, sht.Columns.count).End(xlToLeft).Column

For i = 1 To LastColumn Step 2
    With sht

'How do i find the last 2nd row for every alternate column(in this case time1,time2,time3...timex) in VBA?

'
'
'

End Sub

当试图将这些值存储在另一个工作表上时（例如：

     For m = 2 To i - 2 Step 2
                Sheets("Breakdown").Cells(m / 2 + 1, 3) = timecoll.Add.Cells(.Rows.count, i).End(xlUp).Offset(-1, 0).Value

'^argument not optional error above
              Next m

如果有空数据，我想给那些有空数据的列命名为“short”，我该怎么做呢？

Answer 1

使用与获取最后一列相同的方法，我们可以从循环中获取每列的最后一行，并偏移-1行。

然后将值存储在集合中（或对它们执行任何您想要的操作）。

    Dim i As Long
    Dim lastcolumn As Long

    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets("current")

    Dim timecoll As Collection
    Set timecoll = New Collection
    With sht
        lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = 1 To lastcolumn Step 2
            timecoll.Add .Cells(.Rows.Count, i).End(xlUp).Offset(-1, 0).Value
        Next i
    End With

对于您的编辑，我不确定该循环是什么，所以我将转储到工作表（“breakdrow”）中的A列。

dim k as variant

set sht = thisworkbook.sheets("Breakdown")
i = 1
with sht
    for each k in timecoll
        .cells(i, 1).value = k
        i = i + 1
    next k
end with

好吧，这是编辑后的全部内容：

Option Explicit

Sub test()
    Dim i As Long
    Dim lastcolumn As Long

    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets("current")

    Dim timecoll As Collection
    Set timecoll = New Collection
    With sht
        lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        On Error GoTo short
        For i = 1 To lastcolumn Step 2
            timecoll.Add .Cells(.Rows.Count, i).End(xlUp).Offset(-1, 0).Value
        Next i
        On Error GoTo 0
    End With

    Dim k As Variant

    Set sht = ThisWorkbook.Sheets("Breakdown")
    i = 1
    With sht
        For Each k In timecoll
            .Cells(i, 1).Value = k
            i = i + 1
        Next k
    End With
    Exit Sub
short:
    timecoll.Add "Short"
    Resume Next
End Sub