Excel Macro Examples
Below you will find a list of basic macro examples for common Excel automation tasks.
Copy and Paste a Row from One Sheet to Another
This super simple macro will copy a row from one sheet to another.
Sub Paste_OneRow()
'Copy and Paste Row
Sheets("sheet1").Range("1:1").Copy Sheets("sheet2").Range("1:1")
[Link] = False
End Sub
Send Email
This useful macro will launch Outlook, draft an email, and attach the ActiveWorkbook.
Sub Send_Mail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("[Link]")
Set OutMail = [Link](0)
With OutMail
.to = "test@[Link]"
.Subject = "Test Email"
.Body = "Message Body"
.[Link] [Link]
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
List All Sheets in Workbook
This macro will list all sheets in a workbook.
Sub ListSheets()
Dim ws As Worksheet
Dim x As Integer
x = 1
[Link]("A:A").Clear
For Each ws In Worksheets
[Link](x, 1) = [Link]
x = x + 1
Next ws
End Sub
Unhide All Worksheets
This macro will unhide all worksheets.
' Unhide All Worksheets
Sub UnhideAllWoksheets()
Dim ws As Worksheet
For Each ws In [Link]
[Link] = xlSheetVisible
Next ws
End Sub
Hide All Worksheets Except Active
This macro will hide all worksheets except the active worksheet.
' Hide All Sheets Except Active Sheet
Sub HideAllExceptActiveSheet()
Dim ws As Worksheet
For Each ws In [Link]
If [Link] <> [Link] Then [Link] = xlSheetHidden
Next ws
End Sub
Unprotect All Worksheets
This macro example will unprotect all worksheets in a workbook.
' UnProtect All Worksheets
Sub UnProtectAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
[Link] "password"
Next ws
End Sub
Protect All Worksheets
This macro will protect all worksheets in a workbook.
' Protect All Worksheets
Sub ProtectAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
[Link] "password"
Next ws
End Sub
Delete All Shapes
This macro will delete all shapes in a worksheet.
Sub DeleteAllShapes()
Dim GetShape As Shape
For Each GetShape In [Link]
[Link]
Next
End Sub
Delete All Blank Rows in Worksheet
This example macro will delete all blank rows in a worksheet.
Sub DeleteBlankRows()
Dim x As Long
With ActiveSheet
For x = .[Link](xlCellTypeLastCell).Row To 1 Step -1
If [Link](.Rows(x)) = 0 Then
[Link](x).Delete
End If
Next
End With
End Sub
Highlight Duplicate Values in Selection
Use this simple macro to highlight all duplicate values in a selection.
' Highlight Duplicate Values in Selection
Sub HighlightDuplicateValues()
Dim myRange As Range
Dim cell As Range
Set myRange = Selection
For Each cell In myRange
If [Link](myRange, [Link]) > 1 Then
[Link] = 36
End If
Next cell
End Sub
Highlight Negative Numbers
This macro automates the task of highlighting negative numbers.
' Highlight Negative Numbers
Sub HighlightNegativeNumbers()
Dim myRange As Range
Dim cell As Range
Set myRange = Selection
For Each cell In myRange
If [Link] < 0 Then
[Link] = 36
End If
Next cell
End Sub
Highlight Alternate Rows
This macro is useful to highlight alternate rows.
' Highlight Alternate Rows
Sub highlightAlternateRows()
Dim cell As Range
Dim myRange As Range
myRange = Selection
For Each cell In [Link]
If Not [Link](Word:=[Link]) Then
[Link] = 36
End If
Next cell
End Sub
Highlight Blank Cells in Selection
This basic macro highlights blank cells in a selection.
' Highlight all Blank Cells in Selection
Sub HighlightBlankCells()
Dim rng As Range
Set rng = Selection
[Link](xlCellTypeBlanks).[Link] = vbCyan
End Sub
