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