Basic While Essential Excel Macros You Must Grab

A macro is a rule or pattern that specifies how a certain input sequence should be mapped to an output sequence according to a defined procedure. A macro in Excel is a set of instructions that can be triggered by a keyboard shortcut, toolbar button or an icon in a spreadsheet.

Excel macro is extremely convenient for repeated tasks. In Excel, macros are written in Visual Basic for Applications (VBA). For those who cannot write VBA code, Excel allows you to record a series of steps – using keyboard and mouse – that Excel then converts into VBA.

1. Selecting the Active Workbook

ThisWorkbook.Activate

In case you want to select an opening Excel document, the file extension is necessary.

Windows(”Example.xls”).Activate

2. Selecting a Worksheet

Sheets(”Balance”).Activate

3. Selecting a Cell

Range(”A1″).Select

4. Selecting Continuous Cells

Range(”A2:F8″).Select

5. Selecting Non-continuous Cells

Range(”A2,C6,D9″).Select

Range(”A1,B5:B10,F9″).Select

6. Move/Offset the Cells

ActiveCell.Offset(12, 16).Select

Selection.Offset(-1, -6).Select

Range(”D8″).Offset(-2, -8).Select

7. Selecting the Entire Worksheet

Cells.Select

8. Selecting the Range Where the Active Cell Locates

Range(”A1″).CurrentRegion.Select

9. Select a Row or Column

Rows(”1″).Select

Columns(”A”).Select

or

ActiveCell.EntireRow.Select

ActiveCell.EntireColumn.Select

10. Select Contiguous Rows or Columns

Columns(”A:C”).Select

Rows(”1:10″).Select

11. Select Non-contiguous Rows or Columns

Range(”A:A, C:D, E:F”).Select

Range(”1:1,5:8,9:9″).Select

12. Select a Range downwards from the Active Cell

Range(”A1″, Range(”A1″).End(xlDown)).Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select

13. Select a Range upwards from the Active Cell

Range(”A32″, Range(”A32″).End(xlUp)).Select

Range(ActiveCell, ActiveCell.End(xlUp)).Select

14. Select a Range rightward from the Active Cell

Range(”A1″, Range(”A1″).End(xltoRight)).Select

15. Select a Range leftward from the Active Cell

Range(ActiveCell, ActiveCell.End(xltoLeft)).Select 

16. Select the first blank cell from the active row

Range(”A1″).End(xltoRight).Offset(0,1).Select

17. Macros about “If..Then…EndIf”

 If there’s only one condition and action, you can write code as follows:

If Selection.Value > 10 Then

Selection.Offset(1,0) = 100

End If

 Or:

If Selection.Value > 10 Then Selection.Offset(1,0) = 100

For more conditions and actions, you should do the following:

If Selection.Value > 10 Then

If Selection.Value = 12 Then

Selection.Offset(1,0) = 100

End If

Selection.Offset(1,0) = 20

End If

18. Macro about “f..Then…And…EndIf”

For bio-conditions:

If Selection.Value = 10 And Selection.Offset(0,1).Value = 20 Then

Selection.Offset(1,0) = 100

End If

19. Macro about “f..Then…Or…EndIf”

For bio-conditions:

If Selection.Value = 10 Or Selection.Offset(0,1).Value = 20 Then

Selection.Offset(1,0) = 100

End If

20. Macro about “if..Then…Else…EndIf”

If Selection.Value > 10 Then

Selection.Offset(1,0) = 100

Else

Selection.Offset(1,0) = 0

End If

21. Macro about “If..Then..ElseIf…EndIf”

If Selection.Value = 1 Then

Selection.Offset(1, 0) = 10

ElseIf Selection.Value = 2 Then

Selection.Offset(1, 0) = 20

ElseIf Selection.Value = 3 Then

Selection.Offset(1, 0) = 30

ElseIf Selection.Value = 4 Then

Selection.Offset(1, 0) = 40

ElseIf Selection.Value = 5 Then

Selection.Offset(1, 0) = 50

End If

22. Macro about “Select Case”

Sub Test()

Select Case Selection.Value

Case Is >= 85

Selection.Offset(0, 1) = “A”

Case Is >= 75

Selection.Offset(0, 1) = “B”

Case Is >= 65

Selection.Offset(0, 1) = “C”

Case Is >= 50

Selection.Offset(0, 1) = “D”

Case Else

Selection.Offset(0, 1) = “F”

End Select

End Sub

23. Example of LCase, Now() and UCase Functions

To convert uppercase to lowercase using LCase function:

Select Case LCase(Selection.value)

To show the current time in cell A1:

Range(”A1″).Formula = “=Now()”

To show current time in cell A1 as fixed value that doesn’t change until you execute it once more:

Range(”A1″).Value = Now()

To convert lowercase to uppercase:

Select Case UCase(Selection.value)