Working with Autofilters in Excel VBA

Use the below VBA code, to show all the records in the sheet, without removing the already applied autofilter.

Sub ViewAllData()
  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If
End Sub

This code can be used, where we have applied multiple filters and to remove them at once, without removing the autofilter.


Use below code to view all the records in the protected sheet.

If the worksheet is protected, with no password, we can use below code to unprotect it, show all data and then turn the protection back on.

Sub ViewAllProtected()
With ActiveSheet
    .Unprotect
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True
End With
End Sub


Similarly we can use the below code to view all records in the password protected sheet

Sub ViewAllProtectedPwd()
Dim p As String
p = "password"
With ActiveSheet
    .Unprotect Password:=p
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:=p
End With
End Sub


Check and apply the Auto Filter

Below code will check if the auto filter is already applied in the sheet. If already applied then the code will exit, else it will apply the filter to the data.

Sub CheckAutoFilter()
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Sub


Removing the Autofilter

We can use the below code to remove excel AutoFilter using VBA code, if the autofilter already exists in the sheet.

Sub RemoveAutofilter()
  Worksheets("Sheet1").AutoFilterMode = False
End Sub

If there is no Autofilter already applied in the sheet, then the code will exit without executing anything.


Below code will help you to copy filtered rows

This code will copy the filtered rows to the sheet2 of the worksheet.

Sub CopyfilteredRows()

Dim r1 As Range, r2 As Range

With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set r2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With

If r2 Is Nothing Then
   MsgBox "No data available to copy"
Else
   Worksheets("Sheet2").Cells.Clear
   Set r1 = ActiveSheet.AutoFilter.Range
   r1.Offset(1, 0).Resize(r1.Rows.Count - 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
End If

   ActiveSheet.ShowAllData

End Sub

Counting visible rows in a filter

The below code will help us to get the number of total rows and out of which how many are visible.

Sub CountVisibleRows()

Dim r As Range

Set r = ActiveSheet.AutoFilter.Range

MsgBox r.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & r.Rows.Count - 1 & " Records"

End Sub

This code can be used where for example, we are counting the number of students in Group A. With the group as filtered using VBA code and the above message box code will provide us with the total and filtered row numbers.

10 tips when starting to code in Excel VBA

1. Make Use of the Macro Recorder
Microsoft Excel has a built in Macro recording feature, which give us a great way to learn about new objects and how to use them. I use the macro recorder whenever required to get the objects code in the part of my codes. The macro recorder does not always create the complete code which is required to get the best output. But is gives you the idea on how to write the code to perform specific set of actions. You have an option to record the code and then edit to get it work for the best output.

2. Make use of the immediate window
Excel VBA editor has a feature of the immediate window, which can be of great help while coding. You can write any VBA statements and get the resluts immediately on the immediate window. For example, say cell A1 = How ? and Cell A2 = by pdaphal.blogspot.com way

Select cell A1 and type activecell.value and press enter in the immediate window it will return you the value in cell A1. see the sample code and output below:

?activecell.Value
How ?
?activecell.Offset(1).Value
by pdphal.blogspot.com way

3. Make use of Debug.Print
Debug.print is the best way to find out the bugs (errors) in your code. Our code sometimes does not work the way we want to, due to variable situations we receive in the data processing. By using the debug.print code, you can see the output in the immediate window, as your code / program is running.

See example below for making use of the debug.Print code.
Say cells A1:A10 have the below list of name :
Kiran
Suresh
Arun
Vijay
Vikram

use code to test debug.print in immediate window:

Sub testdebug()
Dim name As Range, namelist As Range
Set namelist = Range("A1:A5")

For Each name In namelist
Debug.Print name.Value
Next name
End Sub

4. Think from all possible angles before starting the coding
Before starting the coding, planning or execution of any complex task, thinking thouroughly is the best way to begin. Start with wrtiting the logic on the paper, about how your code is going to work and execute the different steps. Check and understand every aspects of the problem, working this way will help to understand the problem and while understanding the problem, the solution find its way to you.

5. Make use of inbuilt Methods in excel VBA
It happens to everyone of us, we write the similar codes which we have used in past again and again for executing the different tasks. for e.g., finding the text, converting the text, splitting text. Before staring to write a code, please check the already defined functions / Methods in the Excel VBA editor and make the best use of them.

Few exmamples of methods are - Sendkeys, Second, Strreverse and more. To view methods Got to VBA editor window. Click list Properties / Methods. or you can use the shortcut Ctrl + J.

6. Program your code in Blocks
While wrtiting a bigger program, find the smallest working step which can be built easily. Build this first set of code and then start coding the rest bigger steps around it. By using this we you can test your code step by step, while for a bigger tasks you have something in hand everyday which is getting tested and completed day by day. This improves your turn around time and avoids the complexities in the code.

7. Always keep the references of the important codes / formulas you dont want to search again
If you are a regular VBA user, you should keep the VBA books references handy. Good reference books provide lots of information and tips for daily VBA usage. Also, whenever you have free time, you can always refer these books to learn something new. While, learning you can also create your personal reference bank which you require the most.

8. Break your code / project into smaller pieces
The project / problem you get might be very complex, but it always is a combined problem of different small problems, We need to identify these small problems and start finding the solution for them. So, break the problem into several small pieces. This coding method is called as modularization. Below are few advantages of using the modularization method:
You can resuse the code: once we break the big program in smalled parts, we can resue this smaller part in several places or even in different projects.
Easy to test: the smaller codes are easy to test and debug. this helps us to avoid to go through the bigger program for just testing a one line code.
Easy to maintain: as the program is broken into smaller parts, it is easy to upgrade them as required.

9. Make use of VBA only when you need it
Please make the use of VBA only when you actually use it. Once you start learning VBA code, you will feel excited about using VBA for everything, but there are tasks which can be acheived by using the excel formulas. Avoid using the VBA codes to make your situation more complex. Make the best use of the excel built in features which are readily available. e.g. data validation, conditional formatting, pivot tables, formulas and more.

10. Take Challenges to learn more
If you are into everyday VBA coding user, then taking challenges is the best way to keep your VBA techniques updated and sharpened. You need to keep sharpening your VBA saw, like the same way of the woodcutter story of sharpening the saw. Try to automate the report, that your team prepares manually, try to simplify the formlas, try enhancing the already written VBA code to improve performance. This will keep your VBA skills refreshed, updated and improve your confidence of problem solving capability