Home»Tech Guides»How to program in Microsoft Visual Basic (VBA) To Automate Repetitive Tasks in Excel

How to use VBA (Visual Basic) programming to automate tasks in Excel

One thing that really bothers me is that nobody knows that you can actually automate extremely repetitive tasks using VBA programming.

You can easily make modifications to a code to suit whatever task you need to automate. When you break down the programming line-by-line, it’s actually very simple to understand.

Let’s go through a few examples where you can really make your life easier by using VBA coding.

Make sure you enable the Developer option in Excel to use VBA coding. You need to go to Excel > Preferences > Ribbon & Toolbar, then Customize the Ribbon and check the Developer option. Then click on the Developer tab to access Macros.

You can record a macro, but if you want to “generalize” a macro to do the task a hundred times, for example (without recording yourself doing the task a hundred times of course!), then you need to edit the coding.

When you click Macros > Edit, you will open up Microsoft Visual Basic for Applications, where you can copy and paste our codes and save the modified macro.

How to Delete Every Other Row (or every “n-th” row) using Excel VBA programming

We’ve actually seen people who sit on Excel for hours with the task of deleting every other row.

You can delete one row normally, then highlight the next row you want to delete and press F4. F4 is a shortcut to repeat the last task you did in Excel, but doing this to delete every other row will still take a significant amount of time.

Let’s say we want to delete every other row in Excel 1000 times. We use the code:

Sub Delete_Rows()

Dim i As Integer

For i = 1 To 1000
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
Next i
   
End Sub

Note, when using the code, put your active cell in Excel in the top-left corner or one cell above the cell you want to start deleting. This code will indiscriminately delete every other row for the next 1000 times, make backups before using macros!

Let’s break it down. Delete_Rows is the name of the macro. We make a new variable “i” as an integer, and we put i into a loop from 1 to 1000.

In each of these loops we select the row one row below our current row and delete that row.

The single iteration is then done, and is repeated 999 more times. Each iteration the variable i keeps increasing by 1. By the time the macro is done, you will have deleted every other row for 1000 rows.

What if you only want this macro to delete every other row for a certain number of rows? Replace 1000 with the number you want.

What if you want to delete every other n-th row instead? Replace the “1” in ActiveCell.Offset(1, 0) with the value of the every n-th row you want to delete.

That line of code tells Excel how to move, in this case it’s set to move 1 down and 0 horizontally. Replace 1 with 2 to delete every 3rd row, or 3 to delete every 4th row. Be careful because notice the number is offset by 1 with the actual n-th row you want to delete. Remember that deleting every other row is deleting every 2nd row.

To check it out for yourself, make a quick column of numbers in Excel, and play around with it to see that every 2nd or every 3rd, etc. number will be removed.

How to Delete Every Other Column (or every “n-th” row) using Excel VBA programming

Now use the following code to delete every other column in Excel:

Sub Delete_Columns()

Dim i As Integer

For i = 1 To 20
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
Next i
    
End Sub

The concepts are all the same. This time we made the loop to continue only for 20 times. Now instead of (1,0) we have (0,1), which indicates we are moving one column to the right. As well, the final code in the loop x1ToLeft instead of x1Up.

Remember, you change how many times you want this code to iterate by changing what i goes to. As well, you can delete every 3rd or 4th column, etc, by changing the argument (0,1) to, say, (0,2) or (0,3), etc.

Similarly to deleting every n-th row, the number in the argument is one less than the n-th column you will delete. For example, (1,0) will delete every 2nd column, (2,0) will delete every 3rd column, etc.

How to quickly fill a series of numbers, such as a 100000 values into a column in Excel using VBA coding

Sounds specific, bit have you ever needed to fill out 100000 values into an Excel file?

You can drag down a column to keep counting, but it still takes a lot of time….

You can double click the bottom-right portion of a cell, BUT, that only works if you’re filling with an adjacent column that is already filled!

Let’s make a code that will start counting from 1,2,3,… all the way to 100000. Note, older versions of Excel (such as 2007) will only support up about 64000 rows, but newer versions of Excel will let you make as many columns or rows as you want as long as your computer has the memory for it.

Sub Counting()
    ActiveCell.FormulaR1C1 = "1"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "2"
    ActiveCell.Offset(-1, 0).Range("A1:A2").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A100000"), Type:= _
        xlFillDefault
End Sub

What does this VBA code do? It fills a cell below us with “1”, fills the cell below that one with “2”, and it extends the column all the way to 100000 so you now have a column that counts from 1 to 100000.

Want to change how you count up to 100000, or want to count to a different value? Well change 1000000 to any desired value. To count differently, change “1” and “2” to different values, such as “3” and “6” for example to count in 3’s.

You could also program it easier such as below:

Sub Counting()

For i = 1 To 100000
    Cells(i, 1).Value = i
Next i

End Sub

And it will keep counting by 1 as you keep looping.

But note that if you want to count at a different rate, such as counting by 2’s instead of increasing by 1’s, then you need to modify the code a little, by replacing the “i” with i + i * 1.

Now you change the number i is multiplied by if you want to count in different steps. If you want to count in steps of 4, for instance, then use i + i * 3.

Click here to return to our Tech Guides page.