Scribd
Upload
15 views3 pages

VBA Methods for Activating Cells

The document discusses various ways to work with the active cell in VBA, including using the Activate and Select methods to designate a cell as active, using the Offset property to move the active cell without changing selections, getting the value of the active cell, activating a cell either through Activate or Select, finding the last cell with data in the active column, getting the active cell's column and row numbers, using Do-While and Do-Until loops to repeat actions based on the active cell, and formatting entire rows based on the active cell.

Uploaded by

Ravi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views3 pages

VBA Methods for Activating Cells

The document discusses various ways to work with the active cell in VBA, including using the Activate and Select methods to designate a cell as active, using the Offset property to move the active cell without changing selections, getting the value of the active cell, activating a cell either through Activate or Select, finding the last cell with data in the active column, getting the active cell's column and row numbers, using Do-While and Do-Until loops to repeat actions based on the active cell, and formatting entire rows based on the active cell.

Uploaded by

Ravi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

You can use the Range .Activate method to designate which cell is the active cell.

For example,
the following procedure makes B5 the active cell and then formats it as bold.
VBCopy
Sub SetActive_MakeBold()
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("B5").Activate
[Link] = True
End Sub

You can use the Offset property to move the active cell. The following procedure inserts text
into the active cell in the selected range and then moves the active cell one cell to the right
without changing the selection.
VBCopy
Sub MoveActive()
Worksheets("Sheet1").Activate
Range("A1:D10").Select
[Link] = "Monthly Totals"
[Link](0, 1).Activate
End Sub

Example 1: Getting the value of the active cell

Dim selectedCell As Range

[Link]

Set selectedCell = [Link]

MsgBox [Link]

You can make a cell active either by using the activate method or by using the select
method on the range you want to activate

[Link]

Worksheets("Sheet1").Range("B4").Activate
which is equivalent to

[Link]

Worksheets("Sheet1").Range("B4").Select

If you want to select the last cell that has data in this active column, you can use the code
snippet below.

[Link]

[Link]([Link]).Select

Active Cell Column


1 Public Sub ActiveColumn()
2 MsgBox [Link]
3 End Sub

Active Cell Row


1 Public Sub ActiveRow()
2 MsgBox [Link]
3 End Sub

Do-While loop with ActiveCell

Sub DoWhileDemo()
Do While [Link] <> Empty
[Link] = [Link] * 2
[Link](1, 0).Select
Loop
End Sub
Repeating Actions with a Loop

Sub FormatAllCellsInColumn()
Do Until [Link] = ""
[Link]
[Link] = 35
[Link] = xlSolid
[Link](2, 0).Select
Loop
End Sub

You might also like