What is an auto filter? Vba auto filter works hand in hand with excel. It is a feature in excel that allows users to look at specific data without having to search for it for hours on end because the data is scattered through out a worksheet.
There is an audit being done for the IT Department at your company. Imagine you are looking at a excel worksheet and have thousands of rows of data to go through showing numerous server names, IP addresses and people with access to those server names and IP addresses. You need to find specific IP addresses and server names that have been audited for access security breaches due to compliance having to meet audit standards. The specific server names, IP addresses and customer names are scattered through out the worksheet and you don’t have much time to find these because of deadlines that have to be met. It is time that you become familiar and learn about the auto filter feature.
Filtering selects just the data that you want to see. The data that you tell excel to look for through VBA or visual basic application programming. The rest of the data is hidden because excel is filtering the data. Why would you want to spends hours looking through data when excel can find it fast for you?
The data you filter and the data that is hidden is not effected in anyway. Once you turn the filter off, the data will reappear and return back to the way it was before you used the filter.
There are many different purposes for filters. You can use the filter features provided by excel or you can also create your own filters which we will touch on both later. The most important part of using a filter is deciding what specific data you are looking for on the worksheet before applying any filtering to the worksheet using the vba auto filter.
vba autofilters are some of the most often used utilities in Excel. Here are some sample code that you can use for vba autofilter.
To turn on the Excel autofilter if one does not exist.
Sub TurnAutoFilterOn()
‘check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range(“A1″).AutoFilter
End If
End Sub
Show all records
The following code will show all records when a filter has been applied.
Make sure that “Show All” is selected which is located under the check mark box for Autofilter.
Sub ShowAllRecords()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End Sub
To Turn Off the Excel autofilter if one exists.
Sub TurnFilterOff()
‘removes AutoFilter if one exists
Worksheets(“Data”).AutoFilterMode = False
End Sub
{ 0 comments }