VBA Examples

Publication Date :

Blog Author :

Download FREE VBA Examples Excel Template and Follow Along!
VBA Examples Excel Template.xlsm

Table Of Contents

arrow

Excel VBA Examples for Beginners

Macros are your best friend when it comes to increasing productivity or saving time at your workplace. From small to big tasks, we can automate by using the VBA coding language. We know often you might have thought of some of the limitations Excel has but with VBA coding, you can eliminate all of those. If you struggled with VBA and are still a beginner in this article, we will give some useful examples of VBA Macro code in Excel.

Excel VBA Examples
You are free to use this image on your website, templates, etc.. Please provide us with an attribution link.

Besides checking the above examples in detail below, you are also recommended to have a look at this Excel Events Automation Using Advanced VBA Functions Course, which will tell you how event automation is done and how it works. 

Let's see each of these examples in detail.

List of Top 19 Examples

  1. Print All Sheet Names
  2. Insert Different Color Index in VBA
  3. Insert Serial Number From Top
  4. Insert Serial Number From Bottom
  5. Insert Serial Number From 10 to 1
  6. Insert Worksheets as Much as You want
  7. Delete All Blank Worksheets From the Workbook
  8. Insert Blank Row After Every Other Row
  9. Highlight Spelling Mistake
  10. Change All To Upper Case Characters
  11. Change All To Lower Case Characters
  12. Highlight All the Commented Cells
  13. Highlight All the Blank Cells
  14. Hide All Sheets Except One Sheet
  15. Unhide All Sheets
  16. Delete All Files in the Folder
  17. Delete Entire Folder
  18. Find the Last Used Row in the Sheet
  19. Find the Last Used Column in the Sheet

Besides checking the above examples in detail below, you are also recommended to have a look at this Excel Events Automation Using Advanced VBA Functions Course, which will tell you how event automation is done and how it works. 

Let's see each of these examples in detail.

#1 - Print All Sheet Names

Code:

Sub Print_Sheet_Names()  Dim i As Integer  For i = 1 To Sheets.Count   Cells(i, 1).Value = Sheets(i).Name  Next i End Sub

It will extract all the sheet names to the active sheet.

#1 - Print All Sheet Names

#2 - Insert Different Color Index in VBA

Code:

Sub Insert_Different_Colours()

Dim i As Integer

For i = 1 To 56

      Cells(i, 1).Value = i

      Cells(i, 2).Interior.ColorIndex = i  

Next 

End Sub

It will insert numbers from 1 to 56 and their color index in the next column.

#2 - Insert Different Color Index in VBA

#3 - Insert Serial Number From Top

Code:

Sub Insert_Numbers_From_Top()

Dim i As Integer

For i = 1 To 10

      Cells(i, 1).Value = i

Next i

End Sub

It will insert serial numbers from 1 to 10 from the top.

#3 - Insert Serial Number From Top

This topic opens up many avenues for further exploration. If youโ€™re intrigued by these concepts, you might find this Excel VBA Macro Crash Course for Absolute Beginners provide additional insights and hands-on experience.

#4 - Insert Serial Number From Bottom

Code:

Sub Insert_Numbers_From_Bottom()  Dim i As Integer  For i = 20 To 1 Step -1   Cells(i, 7).Value = i  Next i End Sub

It will insert serial numbers from 1 to 20 from the bottom.

#4 - Insert Serial Number From Bottom

#5 - Insert Serial Number From 10 to 1

Code:

Sub Ten_To_One(

Dim i As Integer

Dim j As Integer

j = 10

For i = 1 To 10

Range("A" & i).Value = j

j = j - 1

Next iEnd Sub

It will insert serial numbers from 10 to 1 from the top.

#5 - Insert Serial Number From 10 to 1

#6 - Insert Worksheets as Much as You want

Code:

Sub AddSheets()

Dim ShtCount As Integer, i As Integer

ShtCount = Application.InputBox("How Many Sheets you would like to insert?", "Add Sheets", , , , , , 1)

If ShtCount = False Then

Exit Sub

Else

For i = 1 To ShtCount

Worksheets.Add

Next i

End If

End Sub

It will ask you to enter the number of worksheets you would like to insert. Just specify the number in the input box and click on "OK." It will insert those many sheets immediately.

#6 - Insert Worksheets as Much as You want

#7 - Delete All Blank Worksheets From the Workbook

Code:

Sub Delete_Blank_Sheets()

Dim ws As Worksheet

Application.DisplayAlerts = False

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

If WorksheetFunction.CountA(ws.UsedRange) = 0 Then ws.Delete

End If Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True

End Sub

It will delete all the blank worksheets from the workbook we are working on.

#7 - Delete All Blank Worksheets From the Workbook

#8 - Insert Blank Row After Every Other Row

Code:

Sub Insert_Row_After_Every_Other_Row()

Dim rng As Range

Dim CountRow As Integer

Dim i As Integer

Set rng = Selection
CountRow = rng.EntireRow.Count

For i = 1 To CountRow

ActiveCell.EntireRow.Insert

ActiveCell.Offset(2, 0).Select

Next i

End Sub

For this, first, you need to select the range where you would like to insert alternative blank rows.

#8 - Insert Blank Row After Every Other Row

#9 - Highlight Spelling Mistake

Code:

Sub Chech_Spelling_Mistake()

Dim MySelection As Range

For Each MySelection In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Word:=MySelection.Text) Then    MySelection.Interior.Color = vbRed

End If

Next MySelection

End Sub

First, select the data and run the VBA code. It will highlight the cells which have spelling mistakes.

#9 - Highlight Spelling Mistake

#10 - Change All To Upper Case Characters

Code:

Sub Change_All_To_UPPER_Case()

Dim Rng As Range

For Each Rng In Selection.Cells

If Rng.HasFormula = False Then

Rng.Value = UCase(Rng.Value)

End If

Next Rng

End Sub

First, select the data and run the code. It will convert all the text values to upper case characters.

#10 - Change All To Upper Case Characters

#11 - Change All To Lower Case Characters

Code:

Sub Change_All_To_LOWER_Case()  Dim Rng As Range  For Each Rng In Selection.Cells   If Rng.HasFormula = False Then     Rng.Value = LCase(Rng.Value)   End If  Next Rng End Sub

First, select the data and run the code. It will convert all the text values to lower case characters in excel.

#11 - Change All To Lower Case Characters

#12 - Highlight All the Commented Cells

Code:

Sub HighlightCellsWithCommentsInActiveWorksheet()

ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4

End Sub

Result:

#12 - Highlight All the Commented Cells

#13 - Highlight All the Blank Cells

Code:

Sub Highlight_Blank_Cells()

Dim DataSet As Range

Set DataSet = Selection

DataSet.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen

End Sub

First, select the data range and run the code. It will highlight all the blank cells with green color.

#13 - Highlight All the Blank Cells

#14 - Hide All Sheets Except One Sheet

Code:

Sub Hide_All_Except_One()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If Ws.Name <> "Main Sheet" Then Ws.Visible = xlSheetVeryHidden

Next Ws

End Sub

The above code hides all the sheets except the sheet named "Main Sheet." You can change the worksheet name as per your wish.

#14 - Hide All Sheets Except One Sheet

#15 - Unhide All Sheets

Code:

Sub UnHide_All()  Dim Ws As Worksheet  For Each Ws In ActiveWorkbook.Worksheets    Ws.Visible = xlSheetVisible  Next Ws End Sub

It will unhide all the hidden sheets.

#15 - Unhide All Sheets

#16 - Delete All Files in the Folder

Code:

Sub Delete_All_Files()

'You can use this to delete all the files in the folder Test ''

On Error Resume Next

Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*"

On Error GoTo 0

End Sub

Change the folder path marked in red as per your folder deletion.

 

#17 - Delete Entire Folder

Code:

Sub Delete_Whole_Folder()

'You can use this to delete entire folder On Error Resume Next

Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*"

'Firstly it will delete all the files in the folder

'Then below code will delete the entire folder if it is empty

RmDir "C:UsersAdmin_2.Dell-PcDesktopDelete Folder"

'Note: RmDir delete only a empty folder

On Error

GoTo 0

End Sub

Change the folder path marked in red as per your folder deletion.

#18 - Find the Last Used Row in the Sheet

Code:

Sub Last_Row()  Dim LR As Long  LR = Cells(Rows.Count, 1).End(xlUp).Row  MsgBox LR End Sub

Here, we find the last used row in the sheet.

#18 - Find the Last Used Row in the Sheet

#19 - Find the Last Used Column in the Sheet

Code:

Sub Last_Column()

Dim LC As Long

LC = Cells(1, Columns.Count).End(xlToLeft).Column

MsgBox LC

End Sub

Here, we find the last used column in the sheet.

#19 - Find the Last Used Column in the Sheet