Urgent..TRICKY..How to find last entries of each row "Having different ranges"

[Gauraog]

Hi All, Im a begainer to VBA.

I like to to find a last 3 entries of each row .

eg. I have a Row A2:N2 which contains data.....similarly I have a data in cell A3toG3........A4:Z4...and so on till the last row which has data.

Data will be always start from Column A but not fixed where will it be ending. (Actually it will keep changing)

Basically each row will have data but its not fixed in which column it will be ending.

I want to copy last 3 entries of each row which has data. Can someone please give me a VBA code to perform this activity. Quick response much appreciated.

 

[Rick Rothstein]

Basically each row will have data but its not fixed in which column it will be ending.

What kind of data are we talking about... numbers or text?

If text, will it be one word or could it be multiple words?

 

[Gauraog]

What kind of data are we talking about... numbers or text?

If text, will it be one word or could it be multiple words?

It will contain text or numbers.....
For example Range A2:I2 has below data. Similarly A3:F3....

[TABLE="width: 576"]
<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]twist[/TD]
[TD="class: xl65, width: 64"]TT[/TD]
[TD="class: xl65, width: 64"]twist[/TD]
[TD="class: xl65, width: 64"]36[/TD]
[TD="class: xl65, width: 64"]mo[/TD]
[TD="class: xl65, width: 64"]UU[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]twist[/TD]
[TD="class: xl65"]TT[/TD]
[TD="class: xl65"]access[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]


Sorry as I dont know how to attach excel... so writing in above format.
Thanks.

 

[pleeseemailme]

Is that all you want to do is copy the data? Or do you want to output the data somewhere?

 

[pleeseemailme]

This won't use VBA but will output the data for you. If you can insert 3 columns to the left of column A so that A now becomes D, B becomes E and so forth, you can paste this formula into A2. Fill over to C2 and then fill down as far as you need. It will return the pen-penultimate, penultimate and ultimate data in each row, respectively.

=INDEX(OFFSET($D2,0,COUNTA($E2:$XFD2)-2,1,3),0,(COLUMN(A$1)))

This assumes that all data starts in column E (or B, before inserting 3 over) and that there are no blanks in the data.

 

[Rick Rothstein]

It will contain text or numbers.....
For example Range A2:I2 has below data. Similarly A3:F3....

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl65, width: 64"]twist[/TD]
[TD="class: xl65, width: 64"]TT[/TD]
[TD="class: xl65, width: 64"]twist[/TD]
[TD="class: xl65, width: 64"]36[/TD]
[TD="class: xl65, width: 64"]mo[/TD]
[TD="class: xl65, width: 64"]UU[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]twist[/TD]
[TD="class: xl65"]TT[/TD]
[TD="class: xl65"]access[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


Sorry as I dont know how to attach excel... so writing in above format.
Thanks.

You will run the following code with your data sheet active, since you did not say where you wanted the output to go, I wrote it to Sheet2

Sub GetLastThreeValuesPerRow()
  Dim R As Long, C As Long, LastRow As Long, LastColumn As Long, Counter As Long
  Dim Values As Variant, ArrIn As Variant, ArrOut As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
               SearchDirection:=xlPrevious, LookIn:=xlValues).Column
  ArrIn = Range("A1", Cells(LastRow, LastColumn))
  ReDim ArrOut(1 To UBound(ArrIn), 1 To 3)
  For R = 1 To UBound(ArrIn)
    Counter = 3
    For C = LastColumn To 1 Step -1
      If Counter = 0 Then Exit For
      If Len(ArrIn(R, C)) Then
        ArrOut(R, Counter) = ArrIn(R, C)
        Counter = Counter - 1
      End If
    Next
  Next
  Sheets("Sheet2").Range("A1").Resize(UBound(ArrOut), 3) = ArrOut
End Sub

 

[Gauraog]

Is that all you want to do is copy the data? Or do you want to output the data somewhere?

Basically I want to copy last 3 entries of each row. I'll use this data for my further analysis. I want a macro that will do this task and paste these values to lets say in column X, Y and Z on same sheet.

Thanks

 

[MARK858]

Should be less efficient especially over large ranges than Rick's array code in post #6 but seeing as I had it wrote B4 i saw Rick's post then I might as well post the code below here

Sub MightAsWell()
    Dim c As Range, i As Long
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        i = Cells(c.Row, Columns.Count).End(xlToLeft).Column
        Range(Cells(c.Row, i - 2), Cells(c.Row, i)).Copy Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Offset(1)
    Next
End Sub

 

[Gauraog]

Small extension to above.....

I want that macro to be run for each sheet in workbook and values to be pasted in column lets say column P, Column Q and Column R of same sheet.

Can you please help on this?

 

[MARK858]

Try

Sub TryAgain()
    Dim c As Range, i As Long, WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        With WS
            For Each c In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                i = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
                If i > 2 Then
                    .Range(.Cells(c.Row, i - 2), .Cells(c.Row, i)).Copy .Cells(c.Row, "P")
                End If
            Next
        End With
    Next
End Sub