I have values in column B that start from B18. All the values in column B are text…
- 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.
- 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 SubJust 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.
- 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 - Assistant
To restrict
lastRowto a specific range B18:B118, you can simply assign the value118directly tolastRowinstead of using theEnd(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 SubWith this code, the range will be restricted to B18:B118, and it will sort the range by the date values in ascending order.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.