Cells, Rows, Columns, Range, UsedRange, Offset, Resize, Union .....
This section contains many examples of the code to show how to move around and work with the components of a worksheet.
Range, UsedRange, Select
’ select a range of cells Range("A1:B4") ‘ select a set of non contiguous cells Range("A1,B4,B8").Select
’ selects the used range on active Sheet ActiveSheet.UsedRange.Select
’ count the number of columns of the used range ActiveSheet.UsedRange.Columns.Count
’ replace small "square-box" characters Y which is chr(13) Carriage-Return, with chr(10) which is a Line-Feed.
ActiveSheet.UsedRange.Replace Chr(13), Chr(10)
’ Select
the named range "MyRange" on the another worksheet, you can use either
of the two examples below:
Worksheets("Sheet1").Activate
Range("MyRange").Select
Application.Goto Worksheets("Sheet1")."MyRange"
Value
’ First example is to enter a numerical value in a selection of cells.
’ Second example shows entering a text in a cell
Sheets(1).Range("A1:C5").Value = 15
Range("A1").Value = "William"
’
entering a text in a cell in the active worksheet
Dim ws As Excel.Worksheet
Set ws = Excel.ActiveSheet
ws.Cells(2, 1).Value = "Demo"
Formula
’
to enter a formula in a cell
Range("A2").Select
Selection.Formula = "=C2-D2"
’
to enter formula in a range of cells
Range("A2:A10").Formula = "=C2-D2"
’
to have the exact formula =C2-D2 in all the cells, you use:
Range("A2:A10").Formula = "=$C$2-$D$2"
Offset, Resize
’
to move one cell down, and two cells to the right
Activecell.Offset(1,2).Select
‘
to move one cell to the left. (your active cell can not be in column A)
Activecell.Offset(0,-1).Select
’
to move one cell down
Activecell.Offset(1,0).Select
’
to move one cell up and two cells to the right
Activecell.Offset(-1,2).Select
‘
either of these 2 statements will select one cell and two more cells
down
Range(Activecell,Activecell.Offset(2,0)).Select
Range("A1",Range("A1").Offset(2,0)).Select
’
select a cell that is five rows below and four columns to the right of
cell B5 which is Cells(5,2)
ActiveSheet.Cells(5, 2).Offset(5, 4).Select
‘ select a range of cells Offset from a specified range. Here it shifts 2 Rows down and 3 Columns to the right ActiveSheet.Range("MyRange").Offset(2, 3).Select
’ select a named Range and Resize (or extend) the selection by 3 Rows down ’ syntax is expression.Resize(RowSize, ColumnSize) Range("MyRange").Select
Selection.Resize(Selection.Rows.Count + 3, Selection.Columns.Count).Select ’ shift 2 rows down and 3 columns to the right of the named range "MyRange" and extend 4 rows and 5 column more ' than the named range Range("MyRange").Offset(2, 3).Resize(Selection.Rows.Count + 4, _
Selection.Columns.Count + 5).Select
’ resize the active cell by 3 rows down and 5 columns to the Right ActiveCell.Resize(3, 5).Select
Selection, ActiveCell ‘ select specified range, remove content of the cells A1 to A30, move cursor to A31 Range("A1:A20").Select Selection.ClearContents ActiveCell.Offset(21, 0).Select ’ it moves cursor to the position of row 1 and active column Cells(1, ActiveCell.Column).Select
’ it moves the cursor to cell B65536 Cells(Rows.Count, "B").Select ' it counts the column of active cell as 1 ActiveCell.Columns.Count ' sets the formula for the active cell Selection.Formula = "=A1*C1" ' sets the font for the selection to Bold Selection.Font.Bold = True ’ count the numbers of cells in selection ActiveSheet.Selection.Cells.Count ‘ select the entire row(s) of your single or multiple cells selection Selection.EntireRow.Select
Column, Row, Columns, Rows, Count, Select, EntireRow,
EntireColumn
‘ select
columns A and B
Columns("A:B").Select
‘
select columns A, C and E, a set of non contiguous columns
Range("A:A,E:E,G:G").Select
‘
select row 2
Rows("2").Select
’
select columns 1, 5 and 7, a set of contiguous columns
Range("1:1,5:5,7:7").Select
‘
select columns from 1 to 10
Rows("1:10").Select
‘
select the entire column of your active cell
ActiveCell.EntireColumn.Select
‘
select the entire row of your active cell
ActiveCell.EntireRow.Select
‘
select entire column of A1
Range("A1").EntireColumn.Select
‘
select entire row of A1
Range("A1").EntireRow.Select
‘ If
you select more than one cell, this select all rows and columns covered
by your selection:
Selection.EntireColumn.Select
Selection.EntireRow.Select
‘ assign
row number of the active cell to the variable
variable = Activecell.Row
‘
assigns column number of the active cell to the variable
variable = Activecell.Column
’
count the number of rows in the selection
Selection.Rows.Count
’
count the number of columns in the selection
Selection.Columns.Count
’
it counts the row of active cell as 1
ActiveCell.Rows.Count
’
it means active row number + 1
ActiveCell.Row + ActiveCell.Rows.Count
’
it counts the number of rows in a worksheet, displays 65536
Cells.Rows.Count
RowOffset, ColumnOffset
‘
activates the cell three columns to the right of and three rows down
from the active cell
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
Cells, RowIndex, ColumnIndex
, CurrentRegion
' Either
way will work. Select
the cell at B1, at intersection of Row 1 and Column 2
Cells(1,2).Select
Cells(1,"B").Select
’
this is same as Cells(1,2).Select
Cells(RowIndex:=1, ColumnIndex:=2).Select
‘ select
all the cells in the active sheet, same as when you press Ctrl+A
Cells.Select
’
this is the equivalent of Cells(65536, 256)
Cells(Cells.Rows.Count, Cells.Columns.Count)
‘ select
all cells from the selection to the first empty row and the first empty
column
‘ the range selected by the CurrentRegion is an area bounded by any
combination of blank rows and blank columns
Selection.CurrentRegion.Select
’
from active cell, this selects the CurrentRegion without selecting the
header row
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
’
resizes the selection to extend it by one row and one column
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
'
Moving down one cell from position at active Row and Column A
Cells(ActiveCell.Row + 1, 1).Select
' Moving to the right one cell from position at active Column and Row 1
Cells(1, ActiveCell.Column + 1).Select
’
moving 1 Row down and 2 Columns to the Right, from current location
Cells(ActiveCell.Row + 1, ActiveCell.Column + 2).Select
' activate the worksheet, and select the range A2, C3
Application.Workbooks("Book1").Worksheets("Sheet1").Activate
ActiveSheet.Range(Cells(2, 1), Cells(3, 3)).Select
' another
alternative to activate the worksheet, and select the range
Application.Goto Workbooks("Book1").Sheets("Sheet1").Range("A2", "C3")
Union
(Note that Union method
does not work across sheets)
‘ fills the union of two named ranges, myRng1 and myRng1, with a text
Set unionRng = Application.Union(Range("myRng1"), Range("myRgn2"))
unionRng.Value = "Example"
’
you can also use the Sheet!A1 Notation to work with the Union method
Set URng = Application.Union(Range("Sheet1!A1:B2"), _
Range("Sheet1!C3:D4"))
'
this let you fill a function formula to the non-adjacent cells
For i = 1 To 20
Set x = Application.Union(Cells(i, 2), Cells(i, 3), Cells(i, 7))
x.Value = "=MyFunction()"
Next