How do i find the last 2nd row for every alternating column in VBA?

Question

Is there a way to find the last 2nd row of alternating column in a worksheet in this case the last 2nd row of Time1, Time2 etc and after that copy and paste it on another worksheet as shown in the picture? Often time, when each column has the same number of rows, using LastRow = .Range("E" & .Rows.Count).End(xlUp).Row will do the job but this time round, every alternating columns have different number of rows so i cant used that LastRow definition. Below is my attempted code where i am stuck on how to manipulate to get the last 2nd row for every alternating column.

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

When tried to store the values on another worksheet eg:

     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

If there are empty data and i want to give that columns with empty data a name called "short", how do i go about doing it?

Warcupine Warcupine · Accepted Answer · 2020-05-20T12:18:31

Using the same kind of method you used to get the last column we can get the last row of each column from inside the loop and offset by -1 row.

Then store the values in a collection (or do whatever you want with them).

    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

For your edit, I am unsure what that loop is so I'm going to dump to column A in Sheets("Breakdown").

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

Okay here's the whole thing after the edit:

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

How do i find the last 2nd row for every alternating column in VBA?

3 Answers