CODEWITHSUNDEEP
vba
pdx
pdx

Reputation: 301

VBA: split cells after ":" for the last 6 rows

I have managed so far to 6 split cells (wrt the colon sign) in the same column. Now I am trying to standardise it for whenever I'll have additional data above (i.e. if last row = 50, then my first row is 44).

My code is as follows:

        Dim fullstring As String, colonposition As Integer, j As Integer
        For i=1 to 6
        fullstring = Cells(j, 1).Value
        colonposition = InStr(fullstring, ":")
        Cells(j, 2).Value = Mid(fullstring, colonposition + 2)
        Cells(j, 1).Value = Left(fullstring, colonposition - 1)
        Next j

I have also tried this (but unsuccessfully)

            Dim fullstring As String, colonposition As Integer, j As Integer, LastRow as Long, FirstRow as Long
            LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            FirstRow = LastRow - 6
            For j = FirstRow To EndRow
            fullstring = Cells(j, 1).Value
            colonposition = InStr(fullstring, ":")
            Cells(j, 2).Value = Mid(fullstring, colonposition + 2)
            Cells(j, 1).Value = Left(fullstring, colonposition - 1)
            Next

Any suggestion as to how to proceed to make VBA select the last row and first row = last row - 6?

Upvotes: 0

Views: 135

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Sub test()

    Dim rng         As Range
    Dim rngCell     As Range
    Dim lCtr        As Long
    Dim vArr

    Set rng = Sheet1.UsedRange.Columns(1)

    '/ UnComment the comments if you have to set Row limits e.g 6 from last.
    '/ Otherwise this code will work on any range.
'
'    If rng.Rows.Count > 7 Then
'        Set rng = rng.Offset(rng.Rows.Count - 7).Resize(7)

        For Each rngCell In rng.Cells
            vArr = Split(rngCell.Value2, ":")

            For lCtr = LBound(vArr) To UBound(vArr)
                rngCell.Offset(0, lCtr) = vArr(lCtr)
            Next
        Next
'    End If

End Sub

Upvotes: 1

Related Questions