Find last values in multiple rows.

    Hello everyone,

    I have multiple rows with data. Aim is to return value of the last cell of each row (data is not contiguous) to corresponding cell in the column BB. I found the way how to get it done with one row, but I do not know how to modify this macros for multiple rows in a range. I would be grateful for any help.

    Thanks in advance.

    Dilshod


    Code
    Sub LastCellInRow()

Range("BB3").Value = Range("AY3").End(xlToLeft).Value

End Sub

    Supposing that the other data is in the rows under AY3 and column BB and after are clear, try with these changes to your macro:

    Code
    Option Explicit
Sub LastCellInRow()
Dim lr     As Long                            'last row
Dim x      As Long                            'counter
lr = Range("AY" & .Rows.Count).End(xlUp).Row
For x = 3 To lr
Range("BB" & x) = Cells(x, Cells.Columns.Count).End(xlToLeft).Column
Next x
End Sub
    Quote from rollis13

    Supposing that the other data is in the rows under AY3 and column BB and after are clear, try with these changes to your macro:

    Hi rollis13,


    Thanks for your help. Most of the rows contain data starting from columns A to AX. Contiguous data in every other column starting from column E. The code above unfortunately gives error message. Thanks again.

    Dilshod

    Hello,


    You could test following UDF to get the Last Column Number for any given row ...


    Code
    Function MyLastCol(rCel As Range)
    MyLastCol = ActiveSheet.Cells(rCel.Row, Columns.Count).End(xlToLeft).Column
End Function


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello Carim,


    Thanks for your response. What I wanted is to find the value of the last cell of each row starting from Row3 down the sheet and paste it to corresponding cell in the column BB. All the rows have different length. I found how to do that for a single row, but I don't know how to do that for multiple rows.

    Thanks again.

    Dilshod

    May be there is something about your context that I am missing ...


    If you store on each single row the last column used in column BB ... you will get the column BB for each row ...


    No ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Just read your comment to rollis13 ...


    Have a go with following


    Code
    Sub LastColumnNumberInEachRow()
Dim lr As Long  'last row
Dim x As Long   'counter
lr = Range("AX" & Rows.Count).End(xlUp).Row
    For x = 3 To lr
        Range("BB" & x) = Cells(x, Cells.Columns.Count).End(xlToLeft).Column
    Next x
End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 2

    Carim,

    Sorry for delay with answer. I'm on call and had to respond to call.

    I tried to run your code and it seems that it does not make any changes to the table. I tried to add .Value to the code line#6 with no success. The aim is to add VALUE OF THE LAST CELL of the each row (ideally every other row, but every row will do as well) starting from Row3 to the corresponding cell of the column BB.

    Thanks in advance.

    Dilshod

    Sorry ...


    But just tested rollis13' s macro ... and it is working fine :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Obviously ... you are facing a problem of how to adapt to your specific file ...


    Why don't you attach a sample file (without sensitive data ....) BUT with the actual structure ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from Carim

    Sorry ...


    But just tested rollis13' s macro ... and it is working fine :thumbup:

    In my table column AY is blanc, and the line lr = Range("AY" & Rows.Count).End(xlUp).Row counts rows (if I understand right) based on a blanc column. When I tried to change AY to E (the column with contiguous data), it copies values to the column BB but wrong values, they are not the last ones in the rows and I do not know from where they are. In the previous post I've sent screenshot of the table with results in BB.

    Thanks.

    Dilshod

    Code
    Option Explicit
Sub LastCellInRow()
    Dim lr     As Long                            'last row
    Dim x      As Long                            'counter
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For x = 3 To lr
        Range("BB" & x) = Cells(x, 51).End(xlToLeft).Column
    Next x
End Sub
    Quote from rollis13
    Code
    Option Explicit
Sub LastCellInRow()
    Dim lr     As Long                            'last row
    Dim x      As Long                            'counter
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For x = 3 To lr
        Range("BB" & x) = Cells(x, 51).End(xlToLeft).Column
    Next x
End Sub

    This one looks like it should work right, but if you'll try to run it in the workbook attached it gives wrong values. For example, when I run it in my file AO21 value is 13.88% but in BB21 value is 41. etc.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login