VBA example - Excel 2007: filter data in cells


VBA code to filter data in cells

In this VBA example, we are going to build a simple program to filter data in the Excel sheet. In this program, the user can filter text data and numeric data. For example, you have the text data as shown below. How can you do if you want to keep only the rows that contains the word VBA?

VBA programming tutorial
VBA array
Excel VBA
VBA loop
Learn VBA
Learn Excel VBA
VBA filtering data
Microsoft Excel 2007
VBA function
VAB example code
Looping through Excel sheet


To run this example VBA code, you will need one Form, one CommandButton, two Labels, one ComboBox, three Textboxes, and three OptionButtons . You may copy or type the sample text above in to your current worksheet.


VBA MS Excel: VBA program to filter text

VBA code for this example:


Option Explicit Private Sub CommandButton1_Click()
Dim rng As String rng = ComboBox1.Text
Dim b() As Integer   If rng <> "" Then
b = getBound(rng)      
If OptionButton1.Value = True Then 'contain text search
       
Call filterTextContain(rng, TextBox1.Text, b(0), b(1))
   
ElseIf OptionButton2.Value = True Then
       
Call filterLess(rng, TextBox2.Text, b(0), b(1)) 'less than filter
           
ElseIf OptionButton3.Value = True Then
       
Call filterGreater(rng, TextBox3.Text, b(0), b(1)) 'greater than filter
       
End If
Else: MsgBox "Invalid data range or criteria range"    
End If

End Sub

Sub filterGreater(ByVal rng As String, ByVal cri As String, ByVal lb As Integer, ByVal ub As Integer)    
While lb <= ub
               
If Val(cri) > Val(Range(Left(rng, 1) & lb).Value) Then
           
rows(lb).Delete
                   
End If
               
lb = lb + 1
   
Wend

End Sub  

Sub filterLess(ByVal rng As String, ByVal cri As String, ByVal lb As Integer, ByVal ub As Integer)       
While lb <= ub
               
If Val(cri) < Val(Range(Left(rng, 1) & lb).Value) Then
           
 
rows(lb).Delete
                   
End If
                
lb = lb + 1
   
Wend

End Sub  

Sub filterTextContain(ByVal rng As String, ByVal cri As String, ByVal lb As Integer, ByVal ub As Integer)    
Dim r As Object
   
While lb <= ub
       
Set r = Range(Left(rng, 1) & lb).Find(cri)
       
If r Is Nothing Then
           
rows(lb).Delete
                   
End If
         
lb = lb + 1
   
Wend
End Sub
 

Function getBound(ByVal rng As String) As Integer()    
Dim lb, ub As Integer
   
Dim r As Object
   
Dim arr() As String
   
arr = Split(rng, ":")
   
lb = CInt(Right(arr(0), Len(arr(0)) - 1))
   
ub = CInt(Right(arr(1), Len(arr(1)) - 1))
   
Dim b(2) As Integer
    b(0) = lb     b(1) = ub    
getBound = b

End Function

Private Sub UserForm_Initialize()     'add sample ranges to the drop down list    
ComboBox1.AddItem "A1:A30"
   
ComboBox1.AddItem "B1:B30"
   
ComboBox1.AddItem "C1:C30"
   
End Sub


Comments

Comments

Name:
Comment:
Enter the text:
CAPTCHA image
A comment

 A

A


2017-08-28




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.