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

I have values in column B that start from B18. All the values in column B are text…

  1. User

    I have values in column B that start from B18. All the values in column B are text values. All the values start with the text value of a date in the format dd/mm/yyyy I would like to sort the range by the text value of the represented date with the oldest date at the top. Can you please write a VBA code to do this.

  2. Assistant

    Sure, here's a VBA code that will sort the range in column B based on the date values in ascending order (oldest date at the top):

    Sub SortByDate()
        Dim ws As Worksheet
        Dim lastRow As Long
        
        ' Set the worksheet object
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
        
        ' Find the last row with data in column B
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        
        ' Sort the range in column B by the date values
        ws.Range("B18:B" & lastRow).Sort Key1:=ws.Range("B18"), Order1:=xlAscending, Header:=xlNo
        
        ' Optional: Autofit the column width after sorting
        ws.Range("B:B").EntireColumn.AutoFit
    End Sub
    

    Just replace "Sheet1" with the actual name of your sheet, and then run this code. It will sort the range in column B starting from B18 based on the date values in ascending order.

  3. User

    How can I restrict lastRow to a range B18:B118 in this code; Dim wss As Worksheet Dim lastRow As Long

    ' Set the worksheet object
    Set wss = ThisWorkbook.Worksheets("Start Page")
    lastRow = wss.Cells(wss.Rows.count, "B").End(xlUp).Row
    wss.Range("B18:B118" & lastRow).Sort Key1:=wss.Range("B18"), Order1:=xlAscending, Header:=xlNo
    
  4. Assistant

    To restrict lastRow to a specific range B18:B118, you can simply assign the value 118 directly to lastRow instead of using the End(xlUp) method. Here's the updated code:

    Sub SortByDate()
        Dim wss As Worksheet
        Dim lastRow As Long
        
        ' Set the worksheet object
        Set wss = ThisWorkbook.Worksheets("Start Page")
        
        ' Set the lastRow value to 118 since you want to restrict the range B18:B118
        lastRow = 118
        
        ' Sort the range B18:B118 by the date values
        wss.Range("B18:B" & lastRow).Sort Key1:=wss.Range("B18"), Order1:=xlAscending, Header:=xlNo
        
        ' Optional: Autofit the column width after sorting
        wss.Range("B:B").EntireColumn.AutoFit
    End Sub
    

    With this code, the range will be restricted to B18:B118, and it will sort the range by the date values in ascending order.

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