VBA Modules for Excel Automation (Advance Excel)

 

Essential VBA Modules for Excel Automation

1. Auto-Adjust Column Width

Automatically adjust all columns to fit content.

Sub AutoFitColumns()
    Cells.EntireColumn.AutoFit
End Sub

---

2. Delete Blank Rows

Removes all empty rows in the active sheet.

Sub DeleteBlankRows()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    Dim i As Integer
    For i = rng.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
            rng.Rows(i).Delete
        End If
    Next i
End Sub

---

3. Highlight Duplicates

Highlights duplicate values in a selected range.

Sub HighlightDuplicates()
    Dim rng As Range
    Set rng = Selection
    rng.FormatConditions.AddUniqueValues
    rng.FormatConditions(1).DupeUnique = xlDuplicate
    rng.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

---

4. Merge Multiple Sheets into One

Merges all sheets into a single sheet.

Sub MergeSheets()
    Dim ws As Worksheet, destSheet As Worksheet
    Set destSheet = Sheets.Add
    destSheet.Name = "MergedData"
    Dim rowCount As Integer
    rowCount = 1
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> destSheet.Name Then
            ws.UsedRange.Copy destSheet.Cells(rowCount, 1)
            rowCount = destSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        End If
    Next ws
End Sub

---

5. Save Workbook Automatically Every 5 Minutes

Auto-saves every 5 minutes to prevent data loss.

Sub AutoSave()
    Application.OnTime Now + TimeValue("00:05:00"), "AutoSave"
    ThisWorkbook.Save
End Sub

---

6. Export Excel Data to a CSV File

Saves the active sheet as a CSV file.

Sub ExportToCSV()
    ActiveWorkbook.SaveAs Filename:="C:\Users\Public\ExportedFile.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
End Sub

---

7. Send Email from Excel Using Outlook

Automatically send an email with Excel data.

Sub SendEmail()
    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "recipient@example.com"
        .Subject = "Automated Email from Excel"
        .Body = "This is a test email sent via VBA."
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

---

8. Protect and Unprotect Worksheets

Prevents unwanted changes to a sheet.

Sub ProtectSheet()
    ActiveSheet.Protect Password:="1234"
End Sub

Sub UnprotectSheet()
    ActiveSheet.Unprotect Password:="1234"
End Sub

---

9. Find and Replace Text in Excel

Searches and replaces text within a selected range.

Sub FindReplace()
    Cells.Replace What:="OldText", Replacement:="NewText", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
End Sub

---

10. Create a Simple UserForm for Data Entry

Displays a basic input box to collect user input.

Sub GetUserInput()
    Dim userName As String
    userName = InputBox("Enter your name:", "User Input")
    If userName <> "" Then
        MsgBox "Welcome, " & userName & "!", vbInformation, "Greeting"
    End If
End Sub

---

11. Copy Data from One Sheet to Another

Copies data from "Sheet1" to "Sheet2".

Sub CopyData()
    Sheets("Sheet1").UsedRange.Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

---

12. Insert Multiple Rows at Once

Inserts 5 blank rows at the selected row.

Sub InsertRows()
    Dim i As Integer
    For i = 1 To 5
        ActiveCell.EntireRow.Insert Shift:=xlDown
    Next i
End Sub

---

13. Automatically Create a Backup of Excel File

Saves a backup copy of the workbook.

Sub BackupWorkbook()
    ThisWorkbook.SaveCopyAs "C:\Backup_" & Format(Now, "yyyy-mm-dd_hh-mm") & ".xlsm"
End Sub

---

14. Convert All Formulas to Values

Replaces formulas with their calculated values.

Sub ConvertToValues()
    Dim rng As Range
    Set rng = Selection
    rng.Value = rng.Value
End Sub

---

15. Auto-Generate a Table of Contents

Creates a summary sheet with hyperlinks to all sheets.

Sub CreateTOC()
    Dim ws As Worksheet, toc As Worksheet
    Set toc = ThisWorkbook.Sheets.Add
    toc.Name = "Table of Contents"
    Dim i As Integer: i = 1
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> toc.Name Then
            toc.Cells(i, 1).Value = ws.Name
            toc.Hyperlinks.Add toc.Cells(i, 1), "", "'" & ws.Name & "'!A1"
            i = i + 1
        End If
    Next ws
End Sub

---

16. Delete Hidden Rows and Columns

Removes all hidden rows and columns in the active sheet.

Sub DeleteHiddenRowsColumns()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange.Rows
        If rng.Hidden Then rng.Delete
    Next rng
    For Each rng In ActiveSheet.UsedRange.Columns
        If rng.Hidden Then rng.Delete
    Next rng
End Sub

---

17. Sort Data in Ascending Order

Sorts selected data in column A.

Sub SortData()
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

---

18. Highlight Cells Based on Condition

Highlights cells in column A with values greater than 50.

Sub HighlightCells()
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A100")
    For Each cell In rng
        If cell.Value > 50 Then
            cell.Interior.Color = RGB(255, 255, 0) ' Yellow
        End If
    Next cell
End Sub

---

19. Auto-Refresh Pivot Tables

Refreshes all Pivot Tables in the workbook.

Sub RefreshPivotTables()
    Dim pt As PivotTable, ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

---

20. Delete All Comments in a Worksheet

Removes all comments from the active sheet.

Sub RemoveComments()
    ActiveSheet.Cells.ClearComments
End Sub

---

Bonus: Run All VBA Macros at Once

Execute all macros in the workbook.

Sub RunAllMacros()
    Dim macro As String
    For Each macro In Array("AutoFitColumns", "DeleteBlankRows", "BackupWorkbook")
        Application.Run macro
    Next macro
End Sub

---

click here

Assessment: Regular Tests, Assignments, and Final Project Evaluation

Certification: Certificate of Completion from Disha Institute

Number of Days Depends on your practice and feedback. The more you practice and review your work, the faster you will complete the course.

For Batch time and Fess contact to Below Address and Number.

Zamanat Sir       (MCA / Bsc. I.T / ‘A’ / ‘O’ / CCC / Govt. Certified  Domain Skill Trainer Disha Institute 153 Vijay Nagar Opp. Rg Pg College W.K Road Meerut     (9411617329 , 9458516690) 

Courses                Reviews &Location                  Download

For More Details such as Courses , Job updation , Notes , Videos ,Links  Click Here