EX 03 - Unlock Excel's Potential with These Must-Know VBA Codes!

Unlock Excel's Potential with These Must-Know VBA Codes!

Excel VBA empowers users to automate tasks, streamline workflows, and unleash the full potential of spreadsheet data. Dive into these essential VBA codes that can revolutionize your Excel experience, from automating repetitive tasks to enhancing data analysis and visualization.

1. VBA Code for coloring the selected cells.

Sub ChangeColor()

Static color As Integer

If ActiveCell.Interior.color = RGB(255, 255, 0) Then

color = 2

ElseIf ActiveCell.Interior.color = RGB(146, 208, 80) Then

color = 3

ElseIf ActiveCell.Interior.color = RGB(255, 165, 0) Then

color = 0

Else

color = 1

End If

 

Select Case color

    Case 1

        Selection.Interior.color = RGB(255, 255, 0) ' yellow

    Case 2

        Selection.Interior.color = RGB(146, 208, 80) ' green

    Case 3

        Selection.Interior.color = RGB(255, 165, 0) ' orange

    Case Else

        Selection.Interior.ColorIndex = xlNone ' no fill

End Select

End Sub

2. Opening multiple links on one go

 

Sub Openhyperlinks()

 

Dim xHyperlink As Hyperlink

Dim WorkRng As Range

Dim FilteredRng As Range

Dim Cell As Range

Dim LinkIndex As Long

 

On Error Resume Next

 

xTitleId = "OpenFilteredHyperlinks"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

' Filter the range based on visible cells only

WorkRng.AutoFilter Field:=1, VisibleDropDown:=True

 

' Get the filtered range

On Error Resume Next

Set FilteredRng = WorkRng.SpecialCells(xlCellTypeVisible)

On Error GoTo 0

 

If Not FilteredRng Is Nothing Then

    For Each Cell In FilteredRng

        LinkIndex = 1 ' Initialize the hyperlink index

        Do Until LinkIndex > Cell.Hyperlinks.Count ' Loop through the hyperlinks in the cell

            Set xHyperlink = Cell.Hyperlinks(LinkIndex)

            xHyperlink.Follow

            LinkIndex = LinkIndex + 1

        Loop

    Next Cell

    MsgBox "All links in the filtered range have been opened in the order they appear. Enjoy!"

Else

    MsgBox "No hyperlinks found in the filtered range.", vbInformation

End If

 

' Clear the filter

WorkRng.AutoFilter Field:=1

 

' Clear the selection

WorkRng.Select

 

End Sub

 

3. Listing the names of sheets in excel – “You must have sheet named list”- or modify the code accordinly

Sub ListSheetNames()

    Dim ws As Worksheet

    Dim i As Integer

   

    ' Specify the cell to start listing the sheet names

    Dim startCell As Range

    Set startCell = ThisWorkbook.Sheets("List").Range("A1")

   

    ' Loop through each sheet and write its name to the specified range

    For Each ws In ThisWorkbook.Sheets

        startCell.Offset(i, 0).Value = ws.Name

        i = i + 1

    Next ws

End Sub 

4. Paste special shortcut

Sub CopyPasteAsValues()

    Dim selectedRange As Range

    Dim cell As Range

    

    ' Check if anything is selected

    If TypeName(Selection) <> "Range" Then

        MsgBox "No range selected!"

        Exit Sub

    End If

    

    ' Loop through each selected area

    For Each selectedRange In Selection.Areas

        ' Copy and paste each area as values

        selectedRange.Copy

        selectedRange.PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False

    Next selectedRange

End Sub