VBA Select and Active – Range & Sheet

Spread the love

This article describes the basic component of excel which VBA handles through scripting.

A VBA Programmer can be better if has good knowledge of excel which helps to use inbuilt functionality and leads to excellent control on excel.

VBA – Active commands on objects

Active” it refers the current state.
Example – If user working in workbook (like Mywork.xlsx) and his selection of cell in worksheets (“sheet1”) and cell “a2”. So VBA reads it

Selection of windows = Windows (“Mywork.xlsx”).activate
Active Workbook         = Myworkbook.xlsx
Active Sheet                 = Sheet1
Active Cell                    = A2

 

Range – can be single cell or combination of multiple cells. On range certain operation can be done like formatting, manipulation, copy and paste etc.
Multiple ways to select range in excel

Single cell selection                                     = Range(“a1”).select
Single cell selection                                     = Cells(1,1).select
Multiple cells selection                                = Range (“a1: e5”). selects
Whole worksheet’s cells selection             = Cells.select
A complete Single Row selection               = Rows(“1:1”).select
Multiple rows selection                               = Rows(“1:20”).select
Single column selection                              = Columns(“A:A”).select
Multiple Columns selection                         = Columns(“E:E”).select
Selection different rows (with gaps)           = Range(“A1,A5,A10”).EntireRow.Select
Selection different columns (with gaps)    = Range(“A1,E1,H1”). EntireColumn.Select

 

Copy below codes and try (paste in module) with F8 Key
Sub rangeSelections()

Range(“a1”).Select
Cells(1, 1).Select
Range(“a1:e5”).Select
Cells.Select
Rows(“1:1”).Select
Rows(“1:20”).Select
Columns(“A:A”).Select
Columns(“E:E”).Select
Range(“A1,A5,A10”).EntireRow.Select
Range(“A1, E1, H1”).EntireColumn.Select

End Sub

 

Sheets – VBA can target to a sheet by multiple method.

 Select sheet sequentially                                      = Sheets(1).select
 Sheet selection by name                                            = Sheets(“Sheet1”).select
 Use property of sheet (click on properties)              = Sheet1.select