Listing in chronological order | Visual Basic

Asked

Viewed 66 times

0

Good afternoon, how do I order the times chronologically without affecting the dates in excel, using Visual Basic?

I have a spreadsheet where are saved the entries I do by VBA. In column G are the 'Payment Dates'. What I would like is for these dates to be ordered chronologically. As exemplified below:

A ideia é deixar ele assim

I tried to create a macro and reuse the base code to make my listing appear in chronological order but it didn’t work.

My code is as follows:

    With ActiveWorkbook.Worksheets("Planilha2").Sort
        .SetRange Range("G2:G")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I started playing with Visual Basic just a few days ago, I’m still a beginner. Can someone help me?

excel vba excel-vba excel-userform userform

1 answer

0


Hello.

The problem is in the range. You need to define which area you want to sort.

In your example, the data goes to line 8, so the range is G2:G8:

With ActiveWorkbook.Worksheets("Sheet3").Sort
    .SetRange Range("G2:G8")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

  • Opa Erick, thanks for the answer. I understood, but I put G2:G to get the next items, the ones I haven’t inserted yet. Has a correct way of choosing a preventive range?

  • Hi Gabriel. Yes, you can use this counter: Cells(Rows.Count, 7). End(xlUp). Row (Row 7 is the G column, counting from left to right). Implementing looks like this: . Setrange Range("G1:G" & Cells(Rows.Count, 7). End(xlUp). Row)

  • The nice thing about VBA is that you can do the same thing in many ways, if your case is just sort the data, you can simplify: Range("G1:G" & Cells(Rows.Count, 7).End(xlUp).Row). Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes

  • Opa Erick, with your help I managed here. Thank you for your patience.

Browser other questions tagged excel vba excel-vba excel-userform userform

You are not signed in. Login or sign up in order to post.