Help - Xcel Moving Rows by date

Occasional Visitor



i have a spreadsheet I use to manage my projects, but installation dates constantly change. I have the rows organized based on date of installation. Is there a way I can put a code in or feature so that when I change the date on that row it moves it in order?

2 Replies
What about SORT() function?


I wouldn't recommend sorting the data as you enter them - it is very disconcerting if a row moves out of view the moment you enter a date.

Instead, I'd use code to sort the data when you open the workbook and when you activate the sheet (from another sheet).

Let's say you want to sort a sheet named Data Sheet on column D.

1) Right-click the sheet tab of Data Sheet.

Select View Code from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Range("D1").CurrentRegion.Sort Key1:=Range("D1"), Header:=xlYes
    Application.ScreenUpdating = True
End Sub

2) Double-click ThisWorkbook in the project explorer pane on the left (in the Visual Basic Editor).

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    With Worksheets("Data Sheet")
        .Range("D1").CurrentRegion.Sort Key1:=.Range("D1"), Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

3) Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm)

Make sure that you allow macros when you open it.