Table Of Contents
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.
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
- Print All Sheet Names
- Insert Different Color Index in VBA
- Insert Serial Number From Top
- Insert Serial Number From Bottom
- Insert Serial Number From 10 to 1
- Insert Worksheets as Much as You want
- Delete All Blank Worksheets From the Workbook
- Insert Blank Row After Every Other Row
- Highlight Spelling Mistake
- Change All To Upper Case Characters
- Change All To Lower Case Characters
- Highlight All the Commented Cells
- Highlight All the Blank Cells
- Hide All Sheets Except One Sheet
- Unhide All Sheets
- Delete All Files in the Folder
- Delete Entire Folder
- Find the Last Used Row in the Sheet
- 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.
#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.
#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.
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.
#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.
#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.
#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.
#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.
#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.
#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.
#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.
#12 - Highlight All the Commented Cells
Code:
Sub HighlightCellsWithCommentsInActiveWorksheet()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4
End Sub
Result:
#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.
#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.
#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.
#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 ''
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.
#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.