Forum Discussion

Ggilbertson1987's avatar
Ggilbertson1987
Copper Contributor
Sep 06, 2022

Help - Xcel Moving Rows by date

Hello-

 

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?

excel
Excel on Mac
Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 06, 2022

    Ggilbertson1987 

    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.

Resources