Select All Cells Using VBA

In this post you will learn about different ways to select all cells of a worksheet using VBA. When developing VBA macros you may need the VBA program to select all cells of a worksheet. For an example you might need to select all cells before printing the Excel sheet. There are many ways to select all cells of an Excel worksheet using VBA. In this lesson you will learn four different ways to select all cells of an Excel sheet.

Table of contents
Select all cells using Cells.Select Method
Select all cells using UsedRange property
Select all cells using last row and column numbers
Select all cells using CurrentRegion property

Cells.Select Method

This is the simplest way to select all the cells of an Excel worksheet using VBA. If you want to select all the cells of the activesheet then you can use the below code to do that.

Sub SelectAllCells_Method1_A()

     ActiveSheet.Cells.Select

End Sub

However keep in mind that this method will select all cells of the worksheet including the empty cells.

Sometimes you may need to select all cells of a specific sheet of a workbook which contains multiple worksheets. Then you can refer to that specific sheet by its name. If the name of the sheet is “Data” then you can modify the above VBA macro as follows.

Sub SelectAllCells_Method1_B()

     Dim WS As Worksheet

     Set WS = Worksheets("Data")

     WS.Activate
     WS.Cells.Select

End Sub

Note that it is important to use the Worksheet.Activate method before selecting all cells. Because the VBA program can’t select cells of a worksheet which is not active. Program will throw an error if you try to select cells of a worksheet which is not active.

UsedRange Method

Above macros select all the cells of the worksheet. So VBA programs select cells even beyond the last row and column having data. But what if you want to select all the cells only inside the range you have used? For that you can use Worksheet.UsedRange property.

Sub SelectAllCells_Method2()

     Dim WS As Worksheet

     Set WS = Worksheets("Data")

     WS.Activate
     WS.UsedRange.Select

End Sub

Above VBA code will select all the cells inside the range you have used.

Select all cells using last row and column numbers

Also there is an alternative way to do this using VBA. First we can find the row number of the bottom most cell having data. Next we can find the column number of the rightmost cell having data. Then we can select the complete range from cell A1 to cell with those last row and column numbers using VBA. Here is how we can do it.

Sub SelectAllCells_Method3()

     Dim WS As Worksheet
     Dim WS_LastRow As Long
     Dim WS_LastColumn As Long
     Dim MyRange As Range

     Set WS = Worksheets("Data")

     WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
     WS_LastColumn = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

     Set MyRange = WS.Range(Cells(1, 1), Cells(WS_LastRow, WS_LastColumn))

     WS.Activate
     MyRange.Select

End Sub

There is one difference between those last two methods. If you use the last row and column numbers method, the range will be selected from cell A1. But if you use the UsedRange property then the range will be started from the first cell with the data. However you can also modify the last row and column numbers macro to get the same result as the Worksheet.UsedRange property method. But it will be a little more complicated.

Using CurrentRegion Property

We can also use the Range.CurrentRegion property to select all cells of an Excel worksheet. We can easily do that by making a slight change to the UsedRange method. Here is how we can select all cells using the CurrentRegion property.

Sub SelectAllCells_Method4()

     Dim WS As Worksheet

     Set WS = Worksheets("Data")

     WS.Activate
     WS.Range("A1").CurrentRegion.Select

End Sub

However there is one limitation in this method. If you have empty rows in your worksheet, then the VBA program will select cells only upto that row.

How to Add or Edit Chart Title Using VBA

Typically, If you have titles in your data, Excel will automatically add a relevant title to the chart. But if no titles present in the data or if titles are not selected when inserting the chart, then the created chart will not have a title. And sometimes you might need to add a different title to the chart than present in the data. So in this lesson you will learn how to add or edit a chart title using VBA. Let’s consider this sample chart.

This chart was created from the data below.

If you look at the above Excel line chart, you will notice that the chart was created with the default title text called “Chart Title”. So in this lesson first let’s look at how to edit an existing title like that.

Assume you want to edit a title of an active chart. You can do it very easily as shown below.

Sub EditActiveChartTitle()

     With ActiveChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

However, for this macro to be worked, your chart should be in an active state(Selected). Otherwise the VBA program will throw an error like this.

But if you know the name of the chart then you can develop a VBA code to edit the title of the chart independent of whether it is active or not. For example, the subroutine below will change the chart title to “Monthly Sales” of a chart called “Chart 2”.

Sub EditTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Don’t know how to find the name of a chart? Check this post to learn how.
Find the name of a chart in Excel

Sometimes you might need to add a chart title to a chart which doesn’t have any title. Here is an example. This chart doesn’t have any title. As mentioned above this can happen if no titles present in the data or if the user doesn’t select the titles when inserting the chart. So let’s see how we can add a title to it.

To add a chart title, we need to do a very simple change to the above VBA code we used to edit the title. The only change needed is we should make the HasTitle property from False to True. So this is how you can add a chart title to an active chart by changing the HasTitle property.

Sub AddChartTitle()

     With ActiveChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Also If you know the name of the chart you want to add the title then you can change the above VBA code like this.

Sub AddTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Here is the result of the above macros.

Also Read
How to create a line chart automatically - Excel VBA
Swap Axis of an Excel Chart Without Changing Excel Sheet Data
Formatting Excel Line Charts Using VBA

How to Insert a Pie Chart Using VBA

In this lesson you will learn how to insert a pie chart automatically using VBA. Pie charts are very useful in data visualization. Because from a pie chart, users can get a clear picture about the data at a glance. So let's use the below sample data to see how to create a pie chart automatically using VBA. This table shows the profits of different branches of a company.

Assume name of the worksheet is "Sheet1". As we have data in the range A1:B6, we can insert the pie chart automatically using VBA as follows.

Sub Create3DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

Here is the result of the above macro.

This is the syntax of the AddChart2 method.

Note that all the parameters are optional for the AddChart2 method. In the above VBA code we have set “-1” as the style and the “xl3DPie” as the xlChartType. Since we have used “-1” Excel will create the default style of the xl3DPie chart type.

Check this post if you want to learn more about the AddChart2 method.

AddChart2 Method

Next let’s see how we can insert a 2D Pie chart by modifying the above VBA code. To do that we need to only change the second parameter of the AddChart2 method.

Sub Create2DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xlPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

If you run the above macro, a 2D Pie chart will be created like this.

Sometimes you may want to insert a pie chart with a different style. For an example, suppose you want to create a pie chart with a dark background. This is how you can do it in Excel 2013.

Sub Create3DPieChart_DarkBackground()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(262, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

Note that we only changed the style number to create this new chart.

However not all the chart styles are available for every Excel version. Because newer Excel versions have more features than previous versions. So how do we find the exact style number of our prefered chart? You can find that by using the Record Macro function.

Follow these simple steps to find the style number of your preferred chart.

Go to the developer tab and click on Record Macro.

Give the macro a suitable name and click OK. Also you can decide where you want to store the VBA macro by selecting an option from the “Store macro in” dropdown.

Then select the data.

Go to the Insert tab and select the chart you want to create.

Now select a chart design from the list. I will select the 3rd design from the top row for this example.

Next, go to the “Developer” tab again and click “Stop Recording”.

Click on the “Visual Basic” to view the code.

Now you can incorporate these circled values to your code to create a chart automatically with similar style.

Check this post if you want to learn more about recording a macro in Excel.

Record a Macro in Excel

VBA - Get Unique Values From Column Into Array

When developing Excel VBA programs, sometimes we need to get unique values from a column into an array. Particularly when creating dynamic lists for dropdowns or generating reports. For an example assume you have a dropdown in your VBA program. Suppose that it needs to be updated with the data entered by the user. If the related data is stored in a column, then we can use that column to populate the dropdown. But what, if values are repeated in the column? We don’t show duplicate values in a dropdown list. So then you need to get only unique values to the dropdown. To do that first we can add unique values from column to an array. Then we can easily create the list of the dropdown using that array. So in this lesson you will learn how to populate an array from unique values of a given column. Also I’m going to develop a VBA function for this. So you can readily use it in your VBA programs.

Below is the list I’m going to use for this lesson. It is a list of asset ids. And they are listed in column A.

This is not a list of unique values. Because some asset ids are repeated in the column. So now let’s see how we can add only unique values from this column to a VBA array. I’m going to create a custom VBA function for this.

Function GetUniqueValuesFromColumnIntoArray(WS As Worksheet, ColumnName As String) As String()

End Function

This VBA function has two parameters. WS and ColumnName. I added these two parameters to input the worksheet name and the column. So you can reuse this for your worksheets easily. Also note that the return type of the VBA function is String(). This is because the function needs to return an array. Want to learn more about returning an array from a VBA function? Check this post.

How to Return an Array From VBA Function

Now we have declared the function name with parameters. Next we need to declare a few variables.

Dim WS_ColumnName_LastRow As Long
Dim i As Long
Dim j As Long
Dim Counter As Long
Dim AllValues() As String
Dim UniqueValues() As String
Dim ValueFound As Boolean

AllValues() array will hold all the values from the column. The UniqueValues() variable will hold only the unique values from the column.

Next, find the last row of the list.

With WS
     WS_ColumnName_LastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row
End With

Size the “AllValues” dynamic array that has already been formally declared.

ReDim AllValues(1 To WS_ColumnName_LastRow)

Populate the AllValues array using a For Next statement.

For i = 1 To WS_ColumnName_LastRow
     AllValues(i) = WS.Range(ColumnName & i).Value
Next i

Then we need to size the UniqueValues array. Here we size the UniqueValues array to the same size of the AllValues array. Because at this moment we don’t know how many unique values we will have in the array. Once all the unique values are populated then we can resize the array to appropriate size.

ReDim UniqueValues(1 To WS_ColumnName_LastRow)

Add the first element from AllValues to the UniqueValues array.

UniqueValues(1) = AllValues(1)

Next we use a nested For Next statement to find unique values.

Counter = 1
For i = 1 To WS_ColumnName_LastRow
     ValueFound = False
     For j = 1 To Counter
          If StrComp(AllValues(i), UniqueValues(j), vbTextCompare) = 0 Then
              ValueFound = True
              Exit For
          End If
     Next j
     If ValueFound = False Then
          Counter = Counter + 1
          UniqueValues(Counter) = AllValues(i)
     End If
Next i

In the above code, the outer For Next statement is used to iterate through the elements of the AllValues array.

For i = 1 To WS_ColumnName_LastRow

Next i

Then this inner For Next statement is used to iterate through existing(Newly adding) elements of the UniqueValues array.

For j = 1 To Counter

Next j

StrComp function returns 0 if the elements of the two arrays are matching.

StrComp(AllValues(i), UniqueValues(j), vbTextCompare)

Here the variable ValueFound is used as a flag.

If the value is not found among the elements of the UniqueValues array, then this new value is added as the next element.

If ValueFound = False Then
     Counter = Counter + 1
     UniqueValues(Counter) = AllValues(i)
End If

Once all the unique values are collected to the UniqueValues array, we can resize the UniqueValues array as follows. Use the Preserve keyword to keep the existing values while resizing the array. If not, all the values will be erased.

ReDim Preserve UniqueValues(1 To Counter)

Finally, the function returns the UniqueValues array as the output.

GetUniqueValuesFromColumnIntoArray = UniqueValues

And Below is the full code of the function.

Function GetUniqueValuesFromColumnIntoArray(WS As Worksheet, ColumnName As String) As String()

     Dim WS_ColumnName_LastRow As Long
     Dim i As Long
     Dim j As Long
     Dim Counter As Long
     Dim AllValues() As String
     Dim UniqueValues() As String
     Dim ValueFound As Boolean

     With WS
          WS_ColumnName_LastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row
     End With

     ReDim AllValues(1 To WS_ColumnName_LastRow)

     For i = 1 To WS_ColumnName_LastRow
          AllValues(i) = WS.Range(ColumnName & i).Value
     Next i

     ReDim UniqueValues(1 To WS_ColumnName_LastRow)
     UniqueValues(1) = AllValues(1)
     Counter = 1
     For i = 1 To WS_ColumnName_LastRow
          ValueFound = False
          For j = 1 To Counter
               If StrComp(AllValues(i), UniqueValues(j), vbTextCompare) = 0 Then
                    ValueFound = True
                    Exit For
               End If
          Next j
          If ValueFound = False Then
               Counter = Counter + 1
               UniqueValues(Counter) = AllValues(i)
          End If
     Next i

     ReDim Preserve UniqueValues(1 To Counter)
     GetUniqueValuesFromColumnIntoArray = UniqueValues

End Function

You can use this function inside a subroutine like this. Assume the name of the worksheet is “Data”.

Sub Test()

     Dim WS As Worksheet
     Dim UniqueIds() As String

     Set WS = Worksheets("Data")

     UniqueIds = GetUniqueValuesFromColumnIntoArray(WS, "A")

End Sub

Add a breakpoint at “End Sub” and run the program. Then view the UniqueIds array in the “Locals” window.

The example worksheet we considered above has a header in row 1. Therefore we have the header “Asset ID” also in the result array. But sometimes you might need to populate unique values into an array without the header. There are few different ways to achieve this. Also you can do it by changing the function or changing the subroutine. In here I will show you how to modify the subroutine to get unique values without the header.

Here is how we can modify the subroutine. Once we get the unique values to the array, we can iterate through all the elements using a For Next statement. While loop through the elements we can decrement the index of each and every element by 1. Then the first element which is the header will be removed. Finally we can resize the array to one less than the original size. To keep the current values use the Preserve keyword when resizing.

Sub Test_2()

     Dim WS As Worksheet
     Dim UniqueIds() As String
     Dim i As Long

     Set WS = Worksheets("Data")

     UniqueIds = GetUniqueValuesFromColumnIntoArray(WS, "A")

     For i = 1 To UBound(UniqueIds) - 1
          UniqueIds(i) = UniqueIds(i + 1)
     Next i

     ReDim Preserve UniqueIds(1 To UBound(UniqueIds) - 1)

End Sub

Below is the outcome of the above subroutine.

Also Read
Fill a Listbox From an Array
Transposing an Array in VBA
Re-size Dynamic Arrays
Quickly Write Multidimensional Array to Excel Range

Different Ways to Delete Rows in Excel Sheets

When you use Excel for your personal, office or business work, you will often need to delete entire rows from the worksheets. In this lesson you will learn how to delete rows in Excel. There are few different ways to delete rows manually. I will explain them one by one. Let’s consider this sample Excel sheet.

Here is the first method to delete rows manually.

Method 1

Right click on a cell of the row you wish to delete. Then select “Delete” from the drop down menu.

Then the “Delete” window will appear like this. Select the “Entire row” option button and click OK.

You can also use this method to delete multiple rows as well. First select atleast one cell from each row you want to delete. In this example Excel sheet, I have selected rows 6, 8, 10 and 12. You can use the Ctrl key on your keyboard to select multiple cells. (Press Ctrl key while selecting cells.)

Now you can follow the same steps you followed to delete one row.

Method 2

In this method, first you should take the mouse to the left most side of the Excel sheet. When you take the mouse on top of the row numbers, the mouse pointer will be changed to an arrow like this.

Now click the right mouse button. Select “Delete” from the menu. Row will be deleted instantly.

Likewise you can use this method to delete multiple rows in Excel sheets. First select the rows you want to delete. You can use the Ctrl key on your keyboard to select multiple rows.

Once you select the rows, you can follow the same steps followed to delete a single row.

Sometimes we may need to delete a set of adjacent rows in Excel. This video shows how to select a set of contiguous cells easily and then delete the entire rows.

Here is also an alternative way to delete a set of adjoining rows.

Also read
How to Read, Write, Delete and Move excel comments
Record a Macro in Excel
Clear Contents of Excel Sheet Except First Row Using VBA

How to Pass an Array to a VBA Function

In this lesson you will learn how to pass an array to a VBA function. You can’t pass an array to any type of function. Function should be developed in such a way that it can accept arrays as variables. Syntax is similar to functions which accept other types of variables. But array arguments should have parenthesis at the end of the name. Here is the syntax of a VBA function which accepts an array as an argument. Data type of the argument is string. So you can only pass an array of a type string to this function.

Function FunctionName(ArrayParameterName() As String)

     '-----------------------
     'Codes of the function
     '-----------------------

     FunctionName = ResultString

End Function

Above example shows the syntax of a VBA function which has one argument. It accepts only arrays as the argument. Now let’s look at how to create a function using that syntax. Let’s develop a VBA function to calculate the average marks of students. It will accept an array of type double as the argument. This is how we can do it.

Function AverageMarks(Marks() As Integer) As Double

     Dim TotalMarks As Integer
     Dim StudentCount As Integer
     Dim i As Integer

     For i = LBound(Marks) To UBound(Marks)
         TotalMarks = TotalMarks + Marks(i)
         StudentCount = StudentCount + 1
     Next i

     AverageMarks = TotalMarks / StudentCount

End Function

Function iterates through elements of the “Marks” array while calculating the total marks and the student count. Finally calculate the average marks by dividing total marks by the student count. We can call this within a subroutine as follows.

Sub FindAverageMarksOfClass()

     Dim StudentMarks(9) As Integer
     Dim ClassAvg As Double

     StudentMarks(0) = 52
     StudentMarks(1) = 92
     StudentMarks(2) = 85
     StudentMarks(3) = 45
     StudentMarks(4) = 65
     StudentMarks(5) = 32
     StudentMarks(6) = 28
     StudentMarks(7) = 62
     StudentMarks(8) = 70
     StudentMarks(9) = 68

     ClassAvg = AverageMarks(StudentMarks())

     MsgBox ClassAvg

End Sub

This is the result of the subroutine.

Note that we have parentheses for the argument of the function.

Function AverageMarks(Marks() As Integer) As Double

However we can omit the parentheses when calling the function. So both the below methods will work.

ClassAvg = AverageMarks(StudentMarks())

ClassAvg = AverageMarks(StudentMarks)

What will happen if we pass non-array to a function which accepts array as the argument. Let’s check this by passing a non-array to the above function. Note that our VBA function has an argument of data type “Integer”. So we are going to pass a value of the same data type.

Sub FindAverageMarksOfOneStudent()

     Dim Student1_Marks As Integer
     Dim ClassAvg As Double

     Student1_Marks = 85

     ClassAvg = AverageMarks(Student1_Marks)

     MsgBox ClassAvg

End Sub

Above subroutine has a variable called “Student1_Marks”. It is a non-array variable of type integer. We assigned the marks of one student to that variable. Then called the function “AverageMarks” using the “Student1_Marks” as the parameter. Here is the outcome.

So you will get a compile error if you pass a non-array to a function which accepts an array as the argument.

Also you can pass an array to function only as ByRef (by reference). Some functions may analyze the array and return a result without doing any change to the array. But some VBA functions make changes to the array as well. So if the called function does any changes to the array, changes affect the original array. I will explain this using an example.

Assume we want a function which returns the third power of given numbers.

Function ThirdPower(Arr() As Long) As Long()

     Dim i As Integer

     For i = LBound(Arr) To UBound(Arr)
         Arr(i) = Arr(i) ^ 3
     Next i

     ThirdPower = Arr

End Function

We can pass numbers as an array. Then the function will return the third power of those numbers. Here is how we can call this function from a subroutine.

Sub TestMacro1()

     Dim MyArr(1 To 5) As Long
     Dim ResultArr() As Long

     MyArr(1) = 2
     MyArr(2) = 3
     MyArr(3) = 7
     MyArr(4) = 8
     MyArr(5) = 10

     ResultArr = ThirdPower(MyArr())

End Sub

Now let’s set a breakpoint at “End Sub” and run the code.

If you view the arrays in the locals window, you will notice that the “MyArr” is also updated with the third power values. This is because the array is passed to the function by reference. You might think that you can pass the array by value using the ByVal statement. But it is impossible. You will get a syntax error if you try to pass the array by value.

Also read
How to Return an Array From VBA Function
Fixed Size Arrays in VBA
Multidimensional Arrays in VBA
Dynamic arrays in VBA
Quickly Write Multidimensional Array to Excel Range

Add Horizontal Scrollbar to a Listbox

Today I’m going to discuss about adding a horizontal scrollbar to a listbox. You might think that you can add this from the Properties window of the VBA editor. But it is not. You can’t change a property and add a horizontal scrollbar for a VBA listbox. Here is a sample listbox and available properties in the properties window.

As you can see there is no property associated with the horizontal scrollbars. So how do we add one to the listbox? Actually you don’t need to do anything to add a horizontal scrollbar. Scrollbar will be automatically added to the VBA listbox if the space is not enough to show all the columns. But the automatic appearance of the horizontal scrollbar is not smooth. I will show an example of malfunction and explain how to solve it at the end of this post.

Here is an example where scrollbars were added automatically to the listbox.

Private Sub UserForm_Initialize()

     Dim WS_OrderDetails As Worksheet

     Set WS_OrderDetails = Worksheets("Order Details")

     lstOrderDetails.ColumnCount = 7

     lstOrderDetails.List() = WS_OrderDetails.Range("A1:G100").Value

End Sub

But in this next example the horizontal scrollbar doesn’t appear even though it is needed. So the user can’t see some of the information.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub

Only the vertical scrollbar is available for the above listbox. So the user can’t see the email addresses listed in the VBA listbox. If you get into this kind of problem, you can fix it in two ways. One method is to increase the width of the listbox. For that sometimes you may need to increase the width of your VBA form as well. However if you are unable to increase width due to design aspect or any other reason then you can follow the second method. Second method is assigning column widths for listbox columns using VBA. You can do it using the ColumnWidths property as follows.

lstNameEmailList.ColumnWidths = "125;125;250"

Then here is the full VBA code of the UserForm_Initialize event.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "125;125;250"

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub

This is the result after the adding the ColumnWidths property.

Also Read
Important Things You Should Know When Using Scrollbars in VBA Userforms
Fill a Listbox From an Array