Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

My Smaple data in column J starts at J5 and Stops at J48. This rows increase daily. When…

  1. User

    My Smaple data in column J starts at J5 and Stops at J48. This rows increase daily. When I run this vba code it presents 20 values to me in this descending order, J1, J2, J3, J4, J5, J6, J7, J8, J9, J10 until the it gets J20. What I actually want is for the code to look for the last row containg data and present the values in an ascending order. So if for example my last row of data as my example is in J48, I want the code to present the data to me in this order, J48, J47, J46, J45, J44, J43, J42, J41, J40, J39 until 20 values are reached which in this case will be J29.

    The code must never present more than 20 values in an asceding order. If for instance from another set values in column J there are only 11 rows of data, it must present from J11 and stop at J5.

    Can the code below be modified or changed to do this?

    If ActiveSheet.Range("G3") = "" Or 0 Then Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End If

    Set wscf = Sheets(Range("G3").Value) Set wsjr = Sheets("Start Page")

    lastRow = wscf.Cells(Rows.count, "J").End(xlUp).Row 'Set the range to be copied If lastRow >= 20 Then Set copyRange = wscf.Range("J" & lastRow & ":J5") Else Set copyRange = wscf.Range("J" & lastRow & ":J5")

    End If 'Copy the range to the destination sheet column E wsjr.Range("F5").Resize(20, 1).Value = copyRange.Value

    Application.ScreenUpdating = True Application.EnableEvents = True

  2. Assistant

    Yes, the code can be modified to do this. Here's a modified version:

    If ActiveSheet.Range("G3") = "" Or 0 Then Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End If

    Set wscf = Sheets(Range("G3").Value) Set wsjr = Sheets("Start Page")

    lastRow = wscf.Cells(Rows.Count, "J").End(xlUp).Row 'Get the number of rows to copy, up to a maximum of 20 numRowsToCopy = WorksheetFunction.Min(lastRow - 4, 20) 'Set the range to be copied, starting from the last row and going up Set copyRange = wscf.Range("J" & lastRow - numRowsToCopy + 1 & ":J" & lastRow) 'Copy the range to the destination sheet column E wsjr.Range("F5").Resize(numRowsToCopy, 1).Value = copyRange.Value

    Application.ScreenUpdating = True Application.EnableEvents = True

    This code first calculates the number of rows to copy, up to a maximum of 20. It then sets the range to be copied based on the last row and the number of rows to copy. Finally, it copies the range to the destination sheet column E.