Scribd
Upload
45 views3 pages

Vbcopy: Range .Activate

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
45 views3 pages

Vbcopy: Range .Activate

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 are on page 1/ 3

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
ActiveCell.Font.Bold = 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
ActiveCell.Value = "Monthly Totals"
ActiveCell.Offset(0, 1).Activate
End Sub

Example 1: Getting the value of the active cell

Dim selectedCell As Range

ThisWorkbook.Activate

Set selectedCell = Application.activecell

MsgBox selectedCell.Value

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

ThisWorkbook.Activate

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

ThisWorkbook.Activate

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.

ThisWorkbook.Activate

Application.activecell.End(XlDirection.xlDown).Select

Active Cell Column


1 Public Sub ActiveColumn()
2 MsgBox ActiveCell.Column
3 End Sub

Active Cell Row


1 Public Sub ActiveRow()
2 MsgBox ActiveCell.Row
3 End Sub

Do-While loop with ActiveCell

Sub DoWhileDemo()
Do While ActiveCell.value <> Empty
ActiveCell.value = ActiveCell.value * 2
ActiveCell.offset(1, 0).Select
Loop
End Sub
Repeating Actions with a Loop

Sub FormatAllCellsInColumn()
Do Until ActiveCell.Value = ""
ActiveCell.Rows.EntireRow.Select
Selection.Interior.ColorIndex = 35
Selection.Interior.Pattern = xlSolid
ActiveCell.Offset(2, 0).Select
Loop
End Sub

You might also like