Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

Can you write an excel VBA code that does the following; For the Active sheet check…

  1. User

    Can you write an excel VBA code that does the following;

    For the Active sheet check column J from row 6 downwards to determine the last row with values. Then sort the range B to L by the date value in column I with the oldest date at the top and the earliest date at the bottom.

  2. Assistant

    Sure! Here's the VBA code that fulfills your requirements:

    Sub SortByDate()
        Dim lastRow As Long
        
        ' Determine the last row with values in column J
        lastRow = Range("J" & Rows.Count).End(xlUp).Row
        
        ' Sort the range B to L by the date value in column I
        With Range("B6:L" & lastRow)
            .Sort Key1:=Range("I6:I" & lastRow), Order1:=xlAscending, Header:=xlYes
        End With
    End Sub
    

    To use this code, follow these steps:

    1. Press ALT + F11 to open the VBA Editor in Excel.
    2. Insert a new module by clicking Insert > Module.
    3. Copy and paste the above code into the module.
    4. Close the VBA Editor.
    5. Activate the sheet you want to run the code on.
    6. Press ALT + F8 to open the "Macro" dialog box.
    7. Select the "SortByDate" macro and click "Run".

    This will sort the range B to L in ascending order based on the date values in column I, with the oldest date at the top and the earliest date at the bottom. The range is determined by the values in column J from row 6 downwards (up to the last non-empty cell).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.