VBA Lesson 18: Excel VBA Vocabulary to Filter and Sort Data
When Excel recognises you set of data as a database it offers you very powerful database functionalities like sorting and filtering.
When you work in an Excel database you might want to make sure that all data filters are off. To this end you will start your procedure with two "If"statements. For example with a database starting in cell A1 here are the two sentences:
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
Here is a simplified Excel macro to sort data using a criteria in one field. The following Excel macro will work with any size database starting in cell A1 and it will work in any version of Excel (1997 to 2010).
Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
Try the Excel macro above with a small table like the following (as you have leand how in the basic exercises for beginners):
Here is another simplified Excel macro sorting data using criteria in three different fields.
Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
The code in the two procedures above is much simpler than the following recorded macro in Excel 2007 and 2010. This recorded macro will not work in earlier versions of Excel (1997 to 2006).
In the downloadable course on Excel macros we offer you much more vocabulary to work with Excel databases and also many more simplified macros that can be used in all versions of Excel. You can you can copy/paste any of them into your own workbooks.
We hope you have enjoyed this introduction to lesson 18
All the Lessons
Lesson 1: Visual Basic Editor (VBE) in Excel, Lesson 2: The Project Window in the Visual Basic Editor of Excel, Lesson 3: The Properties Window in the Visual Basic Editor of Excel, Lesson 4: The Code Window in the Visual Basic Editor of Excel, Lesson 5: Developing Macros in the VBE, Lesson 6: Testing Macros in the VBE, Lesson 7: Excel Macro Recorder, Lesson 8: Macros Help and Assistance, Lesson 9: VBA Events, Lesson 10: VBA Security and Protection, Lesson 11: VBA Coding Tips, Lesson 12: Dealing with Errors, Lesson 13: Working with the Application, Lesson 14: Working with the Workbooks, Lesson 15: Working with the Worksheets, Lesson 16: Moving Around the Worksheet, Lesson 17: Working with Message and Input Boxes, Lesson 18: Excel VBA Vocabulary to Filter and Sort Data. Lesson 19: Working with Variables, Lesson 20: Working with Statements, Lesson 21: Working with Functions, Lesson 22: Working with external data and SQL, Lesson 23: Working with Windows and other Microsoft Programs FROM Excel, Lesson 24: Forms (Userforms) in VBA for Excel, Lesson 25: Userforms Properties and VBA Code, Lesson 26: Properties and VBA code for Command Buttons, Lesson 27: Properties and VBA code for Labels, Lesson 28: Properties and VBA code for Text Boxes, Lesson 29: Properties and VBA code for Combo Boxes, Lesson 30: Properties and VBA code for List Boxes, Lesson 31: Properties and VBA code for Check Boxes, Option Buttons and Frames, Lesson 32: Properties and VBA code for Spin Buttons, Lesson 33: Excel Image Controls
This website and the downloadable Tutorial on Excel Macros are divided in three sections:
Section 1: Excel Macros Programming: Lessons 1 to 10
Section 2: Excel VBA Vocabulary: Lessons 11 to 23
Section 3: Forms and Controls in VBA for Excel: Lessons 24 to 33