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