How to Auto Update the Sorted Range via VBA in Your Excel Worksheet

Data Recovery, Excel File Recovery, Excel Solutions, File Recovery, Office Solutions, Solutions December 23, 2016

The custom sort in Excel is a very useful feature. In this article, we will talk about how to automatically update custom sort in a range by using the Excel VBA.

When you use the custom sort, you will find that this is an amazing feature in Excel. However, if you use this feature frequently, you may also find a problem. You will sort in a range with certain data and information. When you add additional data and information into the range, the order in the range will not change automatically. The image below shows an example of such a condition.Example

When you add a new data set into the range, it will not change the rank automatically. If you still want to sort this larger range with new data set by the same criteria, you need to perform the process of custom sort again. You can see that this is very troublesome, especially when you need to constantly updating the data and information in the worksheet. Every time you add new information into the range, you need to sort again. In order to solve this problem and finish your task quickly, you can continue reading this article.

Record Macro

When the criteria of custom sort are very complex, you will find it hard to write the VBA codes directly. Thus, now you can first record a macro. And the codes in this macro can be used in other macros. The process of recording codes is very easy.

  1. Before you record a macro, you need to add the tab of VBA in the ribbon. Here right click any of a tab in the ribbon.
  2. And then choose the “Customize the Ribbon” in the menu.Customize the Ribbon
  3. Now in the “Excel Options” window, check the option “Developer” in the list of “Main Tabs”.Developer
  4. After that, click “OK” in the window. Therefore, you have added the tab in the ribbon.
  5. Now you will come back to the worksheet. Click the tab “Developer” that you have added.
  6. And then click the button “Record Macro” in the toolbar. Thus, the “Record Macro” window will pop up.Record Macro

On the other hand, you can also click the small button on the bottom of the worksheet to replace the above 6 steps.Record Macro

  1. Now in the “Record Macro” window, input the name into the first text box. Assign a shortcut key if you need. And then add the description according to your need.Set Macro
  2. Next click “OK”. Thus, the macro begins to record every operation that you make.
  3. Select the range that you need to sort in the worksheet.
  4. Click the tab “Home”.
  5. And then click the button “Sort & Filter” in the ribbon.
  6. In the drop-down list, choose the option “Custom Sort”.Custom Sort
  7. In the “Sort” window, set the criteria according to your need. All the actions will be recorded in the macro.Sort

When you are recording macro, don’t make additional steps. Otherwise those steps will also be recorded. And this will cause trouble in the following part.

  1. After you finish the setting in the “Sort” window, click “OK” to save the settings.
  2. Now click the tab “Developer” in the ribbon again.
  3. And then click the button “Stop Recording”. When the worksheet is in the condition of recording macros, the button will change into “Stop Recording”.Stop Recording

You can also click the button on the bottom of the worksheet to stop recording the macro. Thus, you have finished the recording. All the sort criteria have been saved in the Macro 1.

Use Excel VBA Macros

In this part, we will show you how to use VBA macros to update custom sort in your worksheet. And you will also use the recorded macros in this part.

  1. Click the tab “Developer” in the ribbon.
  2. And then click the button “Visual Basic” in the toolbar. Instead, you can also press the button “Alt +F11” on the keyboard to replace the 2 steps.Visual Basic
  3. In the Visual Basic editor, double click the sheet in the “VBAProject” area. In this sheet, you need to update custom sort. And in your actual file, you need to double click the corresponding sheet.
  4. Now input the following codes into the area.
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
  1. And then input the following codes between the above two VBA sentences.
Application.ScreenUpdating = False
If Not Intersect(Target, Range("A1:C13")) Is Nothing Then

End If

Here the range is estimated. There will be 12 months for the sales volume, and together with the first row of the header, we input the range “A1:C13”. You can also input the range into the codes according to your actual worksheet.

  1. In this step, open the module 1 in the editor. The codes in this module are the process of custom sort that you have earlier made. You can see that using the feature of recording macros can save you a lot of time.
  2. Now copy the main part in this module.Copy
  3. And then double click the target sheet in the “VBAProject” part.
  4. After that, paste the codes into the IF-END IF codes.
  5. And then modify the range in the codes according to your need. The recorded macro is a little complicated and redundant. You can also modify it according to your need. Therefore, the complete VBA codes will be like this:
Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  If Not Intersect(Target, Range("A1:C13")) Is Nothing Then
    With ActiveWorkbook.Worksheets("Sheet1").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("B2:B13"), _
         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
      .SortFields.Add Key:=Range("C2:C13"), _
         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With
 
    With ActiveWorkbook.Worksheets("Sheet1").Sort
      .SetRange Range("A1:C13")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End If
End Sub

We add another WITH-END WITH into the codes. Thus, it will be clearer than the record result. If you have other requirements, you can also modify it according to your actual need. You need to be careful when modifying the codes. Otherwise you will produce some wrong result in the worksheet.

  1. Now you have finished the VBA codes in the editor. You can come back to the worksheet and test the result. When you add the next month and the corresponding numbers into the range, the custom sort will automatically refresh.Test

Therefore, you will never need to manually update the custom sort every time you input new elements into the target range. On the other hand, you need to save this workbook as a macro-enabled Excel file. Otherwise you will lose the codes if you save as an ordinary file.

We will Provide Assistance to Excel Corruption Victims

We all know that Excel is very powerful and it can help you finish your work quickly and easily. But the Excel application is still far from perfect. Sometimes Excel will corrupt due to many different reasons. Once Excel corrupts, you will be unable to finish your tasks by this application. In order to work better, you will need to repair it as soon as possible.

Our company has been working on the recovery area for many years, especially the Excel recovery. Therefore, you can turn to our tech staffs for help. With years of experience, we can easily figure out the reason that causes damage to your files. And to help you better repair Excel xlsx file damage, we have developed a third-party tool. This tool is very easy to manipulate and you don’t need to worry about the privacy problem.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx error and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.