40 Useful Excel Macro [VBA] examples – Part 2 of 2 [ FREE DOWNLOAD ]

.

Dear friends,

As you see this is the second and last part of the tutorial 40 Useful Excel Macro [VBA] examples. In a previous article, I published the first 20 examples.

Download a FREE Excel Workbook with all 40 Examples

At the end of this article, you will have a link to download a FREE copy of all 40 useful excel macros collections. Do not forget to download and play around and do provide your feedback.

Here in this last part remaining 20 examples are specified here.

1. Excel Macro to insert a row in a worksheet

Use the following piece of code to insert a single row or multiple rows in a worksheet.
Note: Do not miss reading the comments specified inside the code. They are important to know more about the code.



    Sub insertRowInWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' insert row at a specific row number
    sh.Rows(4).Insert
    
    ' insert more than 1 row starting from a specific
    ' row. In below example there will be 3 rows
    ' inserted starting from row 3
    ' existing row 3rd will be shifted to 6th position
    sh.Rows("3:5").EntireRow.Insert
    
    ' insert row below the selected cell
    ActiveCell.Rows.Insert
    
    End Sub

2. Excel Macro to insert a column in a worksheet

Following code can be used to insert a single column or multiple columns in a worksheet.


    Sub insertColumnInWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' insert column at a specific column name
    sh.Columns(B).Insert
    
    ' insert more than 1 column starting from a specific
    ' column. In below example there will be 3 columns
    ' inserted starting from column A
    sh.Columns("A:C").Insert
    
    End Sub

 

3. Excel Macro to delete a row in a worksheet

Using this code you can delete a single or multiple rows.


    Sub deleteRowInWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' delete a specific row 
	' row no:2 will be deleted
    sh.Rows(2).Delete
    
    ' delete more than one row
	' below statement will delete
	' all the rows 3, 4 and 5
    sh.Rows("3:5").Delete
        
    End Sub

 

4. Excel Macro to delete a column in a worksheet

Using the below piece of code, you can delete a single or multiple columns.


    Sub deleteColumnInWorksheet()

    End Sub
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' delete a specific column
    sh.Columns(B).Delete
    
    ' delete more than one column
	' All the columns A, B and C will be deleted
	' at once by below statement
    sh.Columns("A:C").Delete
    End Sub
    

 

5. Excel Macro to hide a row in worksheet

Using the below piece of code, you can hide a single or multiple rows.


    Sub hideRowInWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' hide a specific row by providing the row number
    sh.Rows(2).Hidden = True
    
    ' hide more than one row at once
	' following statement will hide all
	' the rows 3, 4 and 5
    sh.Rows("3:5").Hidden = True
        
    End Sub

 

6. Excel Macro to hide a column in worksheet

Using the below piece of code, you can hide a single or multiple columns.


    Sub hideColumnInWorksheet()

    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' hide a specific column
	' column C will be hidden by this statement
    sh.Columns("C").Hidden = True
    
    ' hide multiple columns using the below statement
	' below statement will hide all the columns
	' A, B and C
    sh.Columns("A:C").Hidden = True
    End Sub
    

 

7. Excel Macro to unhide a row in worksheet

Using the below piece of code, you can unhide a single or multiple rows.


    Sub unhideRowInWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' unhide a specific hidden row
    sh.Rows(2).Hidden = False
    
    ' unhide more than 1 hidden rows 
	' following statement will unhide 
	' all the rows from 3 to 5
    sh.Rows("3:5").Hidden = False
        
    End Sub

 

8. how to unhide a column in worksheet

Using the below piece of code, you can unhide a single or multiple columns.


    Sub unhideColumnInWorksheet()

    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' unhide a specific hidden column
    sh.Columns("C").Hidden = False
    
    ' unhide multiple columns at once
	' multiple columns will be made visible 
	' by the below statement - A, B and C
    sh.Columns("A:C").Hidden = False
    End Sub

 

9. Excel Macro to copy and insert copied single or multiple rows before a specific row

Using the below piece of code, you can copy and insert any number of rows.


    Sub CopyAndInsertCopiedRow()

    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' Copy 2nd row and insert this copied row at 10th row
    sh.Rows(2).EntireRow.Copy
    ' below statement by default paste the copied row
    ' exactly at the 10th row and rest of the rows
    ' will be shifted down
    sh.Rows(10).Insert
    
    ' copy more than one row and insert them all
    ' at a specific row
    ' Copy rows from 2 to 5 and paste them
    ' on 10th row. Excel will by default automatically
    ' shift that many rows down
    sh.Rows("2:5").EntireRow.Copy
    sh.Rows(10).Insert
    End Sub

 

10. Excel macro to copy and insert copied column before a specific column


    Sub CopyAndInsertCopiedColumn()

    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' Copy Column A and insert this copied column at Column D
    sh.Columns("A").EntireColumn.Copy
    ' below statement by default paste the copied column
    ' exactly at the Column - D and rest of the columns
    ' will be shifted right
    sh.Columns("D").Insert
    
    ' copy more than one column and insert them all
    ' at a specific column
    ' Example: Copy columns from A to D and paste them
    ' on column F. Excel will by default automatically
    ' shift that many columns right
    sh.Columns("A:D").EntireColumn.Copy
    sh.Columns("F").Insert
    End Sub
    

 

11. Excel Macro to protect a worksheet without any password


    Sub protectSheetWithoutPassword()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' protect this one sheet sh without any password
    sh.Protect
    End Sub
    

 

12. Excel Macro to protect a worksheet with a password


    Sub protectSheetWithPassword()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' protect this one sheet sh without a very strong password
    ' like i have given below ;)
    sh.Protect Password:="password123"
    End Sub
    

 

13. Excel Macro to unprotect a protected worksheet


    Sub unprotectSheetWithoutPassword()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' unprotect a protected sheet which is not
    ' protected without giving any password
    sh.Unprotect
    End Sub
    

 

14. Excel Macro to unprotect a password protected worksheet


    Sub unprotectSheetWithPassword()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' unprotect a protected sheet which is not
    ' protected without giving any password
    sh.Unprotect Password:="password123"
    End Sub
    

 

15. Excel Macro to protect a workbook with password


    Sub protectWorkbookWithPassword()
    Dim wb As Workbook
    Dim fPath As String
    Dim newFileName As String
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    newFileName = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    
    ' .saveAs provides a feature in excel vba
    ' to provide a password which will be asked
    ' when you try to open it again
    wb.SaveAs Filename:=newFileName, Password:="password123"
        
    End Sub

 

16. Excel Macro to open a password protected workbook


    Sub OpenProtectedWorkbookWithPassword()
    Dim wb As Workbook
    Dim fPath As String
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath, Password:="password123")
    ' now you can use this workbook as normal
    End Sub

 

17. Excel Macro to clear contents of a Range without clearing formatting


    Sub ClearContentOfRangeWithoutClearingFormatting()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' following statement will clear all the contents
    ' of 1st Range A1 to X5. This will keep the
    ' formatting as it is
    sh.Range("A1:X5").ClearContents
    End Sub
    

 

18. Excel Macro to clear content of a range with formatting


    Sub ClearContentAndFormatting()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' following statement will clear all the contents
    ' as well as any formatting done on these cells
    sh.Range("A1:P27").Clear
    End Sub
    

 

19. Excel Macro to clear contents of a worksheet


    Sub ClearContentOfWorksheet()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' To clear all contents + formatting together
    sh.UsedRange.Clear
    
    ' To clear all contents ONLY
    sh.UsedRange.ClearContents
    End Sub

 

20. Excel Macro to clear all the comments



    Sub ClearAllComments()
    Dim wb As Workbook
    Dim fPath As String
    Dim sh As Worksheet
    
    fPath = "C:\Users\vmishra\Desktop\myfile.xlsx"
    Set wb = Workbooks.Open(Filename:=fPath)
    Set sh = wb.Worksheets(1)
    ' following statement will clear all the contents
    ' as well as any formatting done on these cells
    sh.Range("A1:P27").ClearComments
    End Sub

How did you find this collection of 40 Excel Macro examples?

Did you find them useful to you? Provide your feedback about this. I will write more of such articles with more and more useful and simple Excel VBA Macro examples.

Download your Excel File with all 40 Useful Macro Collection

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Pramod Garg

    Thanks

    Reply
  2. Anil kumar

    thanku very much sir

    Reply

Trackbacks/Pingbacks

  1. 40 Useful Excel Macro [VBA] examples - Part 1 of 2 - Let's excel in Excel - […] Here in this article, I have tried to consolidate some most useful and more frequently used excel macro with…
  2. Excel Macro : Excel VBA code to Protect OR UnProtect Sheet - Let's excel in Excel - […] 40 Useful Excel Macro [VBA] examples – Part 2 of 2 […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest