Vba Examples List Of Top 19 Excel Vba Examples For Beginners

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Examples (wallstreetmojo.com)

List of Top 19 Examples

Print All Sheet NamesInsert Different Color Index in VBAInsert Serial Number From TopInsert Serial Number From BottomInsert Serial Number From 10 to 1Insert Worksheets as Much as You wantDelete All Blank Worksheets From the WorkbookInsert Blank Row After Every Other RowHighlight Spelling MistakeChange All To Upper Case CharactersChange All To Lower Case CharactersHighlight All the Commented CellsHighlight All the Blank CellsHide All Sheets Except One SheetUnhide All SheetsDelete All Files in the FolderDelete Entire FolderFind the Last Used Row in the SheetFind the Last Used Column in the Sheet

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

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

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.

#4 – Insert Serial Number From Bottom

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

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

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

#6 – Insert Worksheets as Much as You want

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

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

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

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. It will highlight the cells which have spelling mistakes.

#10 – Change All To Upper Case Characters

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

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 excelLower Case Characters In ExcelThere are six methods to change lowercase in excel - Using the lower function to change case in excel, Using the VBA command button, VBA shortcut key, Using Flash Fill, Enter text in lower case only, Using Microsoft word. read more.

#12 – Highlight All the Commented Cells

Sub HighlightCellsWithCommentsInActiveWorksheet()

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

End Sub

Result:

#13 – Highlight All the Blank Cells

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

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

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

Sub Delete_All_Files()

‘You can use this to delete all the files in the folder Test ’’ On Error Resume NextError Resume NextVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more 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

Sub Delete_Whole_Folder()

‘You can use this to delete entire folder

On Error Resume NextError Resume NextVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more

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

#18 – Find the Last Used Row in the Sheet

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

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.

Excel VBA Examples for Beginners#

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#

Recommended Articles#

Excel VBA Examples for Beginners

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

Recommended Articles