Clear Contents of Excel Sheet Except First Row Using VBA

In this post you will learn how to clear the contents of Excel sheet except the first row using VBA. This is a very common requirement for Excel VBA applications. Because VBA applications often need to clear existing reports and re-generate them. When doing this, the program doesn’t need to delete the header row. Because it is the same for the new reports. Also there may be some other situations where you want to develop a VBA code to delete content from the Excel sheet except the first row. For example, sometimes users may need to clear the existing result sheets except the header using buttons.

So now let’s look at how to develop a code to delete the worksheet contents without header row.

Let’s consider this sample worksheet. Name of the worksheet is “Order Information”.

There are few different ways to accomplish this. I will explain two methods in this post. You can use the first method if you know the last column you have data in. In the above sample sheet we have data upto column E. If you don’t know what the last column is or if the last column changes from time to time then you should use the second method shown at the end.

This is the complete code of the first method.

Sub DeleteContentsExceptHeader()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Range("A2:E" & LastRow).ClearContents
     End If

End Sub

This is the result you will get when run the subroutine.

Here is the explanation for the first subroutine.

First we need to define two variables

Dim WS As Worksheet
Dim LastRow As Long

Next, assign the worksheet to the WS variable.

Set WS = Worksheets("Order Information")

Find the last row of the worksheet

LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Next we need to check whether the last row is greater than 1. If it is greater than 1, then we can clear the contents using the Range.ClearContents method.

If LastRow > 1 Then
     WS.Range("A2:E" & LastRow).ClearContents
End If

It is important to check whether the last row is greater than 1, because otherwise the “WS.Range("A2:E" & LastRow).ClearContents” statement will delete the header row if there is no data after row 1.

Now let’s move to the second method. As mentioned earlier, you can use this method if you don’t know the last column of the data or if the last column changes from time to time. Below is the complete code of the second method.

Sub DeleteContentsExceptHeader_Method2()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Rows("2:" & LastRow).ClearContents
     End If

End Sub

In this method we are deleting entire rows from row number two to the last row. It is important to use If Statement (If LastRow > 1 Then) to avoid deletion of the first row in case there is no data after row 1.

How to Search For Multiple Strings Using VBA InStr Function

In the previous post we learnt how to use the InStr function to check whether a string contains a substring. Today I’m going to show you how to search for multiple strings using the VBA InStr function. For an example consider this sample string.

“Learn to develop Excel VBA applications”

Assume you want to check whether this string contains either Excel or VBA. We can’t use the InStr function as it is, to search for multiple values. But we can create our own function using the InStr function to do the job. I’m going to develop two types of solutions to search for multiple values using the VBA InStr function. One function will return true or false depending on whether it can find at least one of the multiple strings or not. And the other function will return the positions of each and every substring. So stay tuned.

Function 1 - Return true or false depending on whether at least one of the multiple values are available or not.

This function needs two input parameters. The string being searched and the multiple strings. Below is the complete code of the first function.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

    For i = LBound(MultipleStrings) To UBound(MultipleStrings)
        If Len(MultipleStrings(i)) > 0 Then
            If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then
                MultipleStringSearch = True
                Exit Function
            End If
        End If
    Next i

End Function

Below is the explanation of the above code.

Here we pass the multiple values to the function as an array. Also this function returns either true or false. So the data type of the returned value is boolean.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

Then a For Next statement is used to iterate through each element of the MultipleStrings array.

For i = LBound(MultipleStrings) To UBound(MultipleStrings)

Next i

We should check whether the length of each string is higher than 0. Because the InStr function returns the start value when the length of the search string is 0. Click the link below to see more details.

InStr function return Start value when length of second string is 0

If Len(MultipleStrings(i)) > 0 Then

Then the InStr function is used to check whether the SText string contains each substring of the MultipleStrings array.

If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then

Now let’s see how to call this function in subroutines.

Let’s use a somewhat long string as the first string. This will be the string being searched.

MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

And the multiple strings we are searching for are “we”, “Excel” and “applications”.

Sub Test_1()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     StringsArr(0) = "we"
     StringsArr(1) = "Excel"
     StringsArr(2) = "applications"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

In this subroutine, a fixed size array is declared to contain the multiple strings.

Dim StringsArr(2) As String

And then added the multiple strings to that array as follows.

StringsArr(0) = "we"
StringsArr(1) = "Excel"
StringsArr(2) = "applications"

Want to learn more about fixed size arrays? Check this post.

Fixed Size Arrays in VBA

This is the output of the Test_1 subroutine.

Here is the second example.

Sub Test_2()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "I went to home"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

As you can see the multiple strings are not found inside the MyString. So the output of the Test_2 subroutine is false.

In the above two examples, multiple strings were added to the StringsArr array one by one. However if you have the multiple values in a one string separated by commas, then you can use Split function to convert it to an array easily. See the below example.

Sub Test_3()

     Dim MyString As String
     Dim MultipleStrings As String
     Dim StringsArr() As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     MultipleStrings = "Excel,vba,application"

     StringsArr = Split(MultipleStrings, ",")

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

This is the output of the Test_3 subroutine.

Function 2 - Return the positions of each multiple string

Previous function returned true or false depending on whether the multiple values are available inside the string being searched or not. But this new function returns the position of each multiple string inside the main string. For example if the multiple strings are found inside the main string at the positions x, y and z then the function will return x,y,z in an array.

Function InStrResultForMultipleStrings(SText As String, MultipleStrings() As String) As Variant

     Dim i As Integer
     Dim ResultArr() As Variant
     Dim iStart As Integer
     Dim iEnd As Integer

     iStart = LBound(MultipleStrings)
     iEnd = UBound(MultipleStrings)
     ReDim ResultArr(iStart To iEnd)

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         ResultArr(i) = InStr(1, SText, MultipleStrings(i), vbTextCompare)
     Next i

     InStrResultForMultipleStrings = ResultArr()

End Function

Input parameters for this second function are the same as the previous function. However this function returns a value of the variant data type instead of the boolean. Following example shows how to call this function within a subroutine.

Sub Test_4()

     Dim MyString As String
     Dim StringsArr(2) As String
     Dim Result() As Variant

     MyString = "Excel formulas and vba macros"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Result = InStrResultForMultipleStrings(MyString, StringsArr)

End Sub

Then set a breakpoint at the end of the subroutine and run the macro to see the result in the Locals window.

Word “Excel” is at the position 1 within the MyString and “vba” is at position 20. Also the substring "application" is not available inside the MyString. So the function returns 0 for that substring.

Also note that above two functions carry out a case insensitive search. If you want to do case sensitive search for multiple strings then change the compare type from “vbTextCompare” to “vbBinaryCompare”. So the first function should be changed like this.

Function MultipleStringsSearchCaseSensitive(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         If Len(MultipleStrings(i)) > 0 Then
             If InStr(1, SText, MultipleStrings(i), vbBinaryCompare) > 0 Then
                 MultipleStringsSearch = True
                 Exit Function
             End If
         End If
     Next i

End Function

You can do the same for the second function as well.

How to Check If String Contains an Another String - VBA

When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings. So in this lesson you will learn how to check if a string contains a substring using the VBA InStr function.

Table of contents
InStr function
Return values of VBA InStr function
VBA InStr case insensitive search
VBA InStr case sensitive search
If the second string occurs multiple times.
How to use VBA InStr Function for list of strings

InStr function

The InStr function has four parameters. Two optional and two required parameters. This is the syntax of the InStr function.

InStr([start], FirstString, SecondString, [CompareMethod])

Start - This is the starting point of the first string where you want to begin searching for the second string. If omitted, the function will search from first position.

FirstString - Function will search through this string to find the second string.

SecondString - This is the string the InStr function will search for.

CompareMethod - There are three options for this parameter. vbBinaryCompare, vbDatabaseCompare and vbTextCompare. But vbDatabaseCompare is only used for Microsoft Access. So you can use either vbTextCompare or vbBinaryCompare for Excel VBA Macros. If you select vbBinaryCompare then the VBA InStr function will carry out a binary comparison. So the function will see “A” and “a” as different. But if you choose vbTextCompare then the InStr function will carry out a textual comparison and it will see “A” and “a” as the same. You will get a clear understanding about these different types of comparisons from the examples below.

Return values of VBA InStr function

There are four types of return values for this function. Click on the links to see related examples.

0 - Second string is not found or Length of first string is 0 or Starting point is higher than length of first string or Starting point is higher than the occurrence position of the second string.

Null - First string is null or Second string is Null or both are Null

Start - Length of second string is 0

Position at first string where match is found - When second string is found within first string

Now let’s look at examples where we will get those return types.

Return 0

Second sting is not found

Sub Example_1()

     Debug.Print InStr(1, "Excel VBA Solutions", "PHP", vbTextCompare)

End Sub

Length of first string is 0

In this example Len(String1) equals 0. So the function returns 0.

Sub Example_2()

     Dim String1 As String
     Dim String2 As String

     String1 = ""
     String2 = "PHP"

     Debug.Print InStr(1, String1, "PHP", vbTextCompare)

End Sub

Starting point is higher than length of first string

Sub Example_3()

     Debug.Print InStr(20, "run macro", "macro", vbTextCompare)

End Sub

Here the length of the first string is 9. But the start is set to 20. So the function will return 0.

Start is higher than occurrence position of the second string inside first string

Sub Example_4()

     Debug.Print InStr(8, "Check this Excel tutorial", "this", vbTextCompare)

End Sub

In this example, the InStr function is searching for the string “this” inside the first string. And string “this” appears at the position 7 of the first string. But as the start is set to 8 the function returns 0. Because the InStr function can’t find the substring “this” after position 8.

Return Null

The InStr function returns Null on three occasions.

Return Null when first string is Null

Sub Example_5()

     Dim String1 As Variant
     Dim String2 As String

     String1 = Null
     String2 = "word"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String1 has been declared as a variant because only the variant data type can hold Null values.

Return Null when second string is Null

Sub Example_6()

     Dim String1 As String
     Dim String2 As Variant

     String1 = "Excel VBA Solutions"
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String2 is declared as a variant because only the variant data type can hold the Null values.

Return Null when both first and second strings are Null

Sub Example_7()

     Dim String1 As Variant
     Dim String2 As Variant

     String1 = Null
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Return Start

The InStr function will return the start value on one occasion.

Return Start when length of second string is 0

Sub Example_8()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = ""

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the Len(String2) is equal to 0. And the start is 4. So the function will return 4.

Return the position where the match is found

When the second string is found within the first string, the function will return the position of the first string where the second string is found.

Sub Example_9()

     Dim String1 As String
     Dim String2 As String

     String1 = "How to check if string contains another string"
     String2 = "to"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “to” can be found at the fifth position of the String1. So the function will return 5. Note that the function also considers spaces when determining the position.

VBA InStr Case Sensitivity

When you use the InStr function sometimes you may want to do case insensitive searches and sometimes case sensitive searches. So how do we control the case sensitivity? We can use the fourth parameter of the function to control the case sensitivity of the searches.

VBA InStr case insensitive search

In Excel VBA we can use one of the two values for the fourth parameter of the VBA InStr function. vbTextCompare or vbBinaryCompare. Because vbDatabaseCompare is only related to Microsoft Access. So far in our examples we used the vbTextCompare as the fourth parameter. If we use vbTextCompare as the fourth parameter, then the function will do case insensitive search.

Sub Example_10()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “VBA” in uppercase in the String1 and “vba” in lowercase in the String2. As we have used vbTextCompare as the fourth parameter, the InStr function will do case insensitive search and will return 7.

VBA InStr case sensitive search

So we learnt how to do case insensitive search using the VBA InStr function from the above example. We can use vbBinaryCompare as the fourth parameter to do case sensitive searches.

Sub Example_11()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

Here the word “VBA” is in uppercase in the String1 and “vba” is in lowercase in the String2. As we have used vbBinaryCompare as the fourth parameter, search will be case sensitive and function will return 0.

Now let’s use the word “VBA” in uppercase in both strings and check how it works with the vbBinaryCompare option.

Sub Example_12()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "VBA"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

If the second string occurs multiple times.

Sometimes the second string can occur multiple times inside the first string. If this happens the InStr function will return the position of the first occurrence of the second string starting from the start point.

Sub Example_13()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

In this example word Excel occurs 3 times inside the String1. As the start is 1, the InStr function will return the position of first occurrence which is 1.

Here are the same example strings with a different start.

Sub Example_14()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(5, String1, String2, vbTextCompare)

End Sub

In this example the start is set to 5. So now the function will search for the word “Excel” inside the String1 from the fifth position onward. Therefore in this example, the function will return the position of the second occurrence of the word “Excel”.

How to use VBA InStr Function for list of strings

However in practical situations you may not need to check if one string contains a substring. Instead you may need to check if a list of strings contains a particular substring and output the results. So now let’s look at how to accomplish such a task with the help of the For Next statement.

Let’s consider this sample Excel sheet.

This sample Excel sheet contains a list of post titles of this blog in column A. I’m going to find which titles have the word “Excel” and write “Found” Or “Not Found” in column B. Let’s name the subroutine as CheckForWordExcel

Sub CheckForWordExcel()

End Sub

First we need to declare a few variables.

Dim WS As Worksheet
Dim PostTitle As String
Dim i As Integer

If the name of the worksheet is “Sheet1”, we can assign the sheet to the WS variable as follows.

Set WS = Worksheets("Sheet1")

Assume there are titles up to the 100th row. So we can use a For Next statement like this.

For i = 2 To 100

Next i

In each iteration we can assign the post titles to the PostTitle variable like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value
Next i

Now we can use the InStr function to check whether the word “Excel” is available in each title.

InStr(1, PostTitle, "Excel", vbTextCompare)

Here the second string is “Excel” and the length of it is higher than 0. Therefore InStr function should return positive value only when substring “Excel” found inside the PostTitle. So we can use an If statement inside the For Next Loop like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value

     If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
         WS.Range("B" & i).Value = "Found"
     Else
         WS.Range("B" & i).Value = "Not Found"
     End If
Next i

So here is the full code of the subroutine.

Sub CheckForWordExcel()

     Dim WS As Worksheet
     Dim PostTitle As String
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 100
         PostTitle = WS.Range("A" & i).Value

         If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
             WS.Range("B" & i).Value = "Found"
         Else
             WS.Range("B" & i).Value = "Not Found"
         End If
     Next i

End Sub