WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Examples

VBA Examples

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Examples for Beginners

Macros are your best friend when it comes to increase your productivity or save some time at your workplace. Right from small tasks to big tasks, we can automate by using the VBA coding language. I know oftentimes you might have thought of some of the limitations excel has but with VBA coding, you can eliminate all of those. Ok, if you struggled with VBA and still a beginner in this article, we will give some of the useful examples of VBA Macro code in Excel.

VBA Examples

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

Let’s see each of these examples in detail.

You can download this VBA Examples Excel Template here – VBA Examples Excel Template

#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

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

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

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

Insert Different Color Index

#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

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

Insert Serial Number From Top

#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

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

VBA 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 i

End Sub

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA 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

This 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.

VBA Insert Worksheets as Much 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

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

VBA Delete All Blank Worksheets

#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.

VBA 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.

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.

VBA 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.

Change To Lower Case Characters

#12 – Highlight All the Commented Cells

Code:

Sub HighlightCellsWithCommentsInActiveWorksheet()

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

End Sub

Result: 

Highlight Columns using vba code

#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.

Highlight All 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.

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

This will unhide all the hidden sheets.

VBA Example Code - 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:\Users\Admin_2.Dell-Pc\Desktop\Delete Folder\*.*"
 On Error GoTo 0

End Sub

Change the folder path, which is 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:\Users\Admin_2.Dell-Pc\Desktop\Delete 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:\Users\Admin_2.Dell-Pc\Desktop\Delete Folder\"
 'Note: RmDir delete only a empty folder
 
 On Error GoTo 0

End Sub

Change the folder path, which is 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

VBA Exmple Code - 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

VBA Exmple Code - Find the Last Used Row in the Sheet

Recommended Articles

This has been a guide to VBA Examples. Here we discuss the list of top 19 useful examples of VBA Macro code in excel along with the downloadable template. Below are some useful articles related to Excel VBA –

  • VBA XLUP
  • CDATE VBA Function
  • VBA Tutorial
  • VBA Free Course
  • VBA Randomize
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Examples Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More