Selecting Cells in VBA - Sheets, Ranges, ActiveCell, SpecialCells, End, and Offset - Code Included

Grab the Free VBA Quick Reference Guide
When you start coding you will want to know how to Select Cells. In many ways, this is one of the first steps in your journey.

In this video, we review selecting Sheets (or worksheets), Ranges which are a child of sheets and cells which are a child of both ranges and sheets.

Things to keep in mind before you select a cell if the sheet is referenced (like Sheets("Sheet1").cells(1,1).select) you will need to select that sheet or make it the active sheet.

Select activates the Worksheet, Range, or Cell. You can also use the Selection property object that represents the currently active selection.

One last thing, while recording a macro selection is one of the most commonly returned item even though it may not be essential to what you are trying to accomplish.

Code:
Sub SelectCell()

Sheets("Sheet1").Select

Range("A1").Select
Range("A1:B10").Select

Cells(1, 1).Select

Range("C1:D10").Select
Range("C1:D10").Cells(2, 1).Select

Debug.Print ActiveCell.Row
Debug.Print ActiveCell.Column

Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Column

ActiveCell.Cells.End(xlUp).Select

Range("D9").Select
Range("D9").Offset(1, 1).Select
Range("D9").Offset(-1, -1).Select

Sheets("Sheet1").Select
Sheets("Sheet1").Cells(1, 1).Select

Debug.Print Selection.Row

Selection.Font.Bold = True
Range("B8").Font.Bold = True

End Sub

[andrewburgess]

This is awesome - just starting out on VBA and love your channel
Thanks

[mervynportis]

Have to say my friend, you are the best at explaining this stuff.. clear, intelligible and at a relaxed pace.

[kebincui]

You are an excellent instructor, explaiining very well. Thank you

[redhaakhund]

Best channel ever seen. I really enjoying your video and learning. Best of the best brother..👍🏆

[digitalbauble]

Thanks man, keep it up. You are good in explaining things while demonstrating. It helps me learn faster.

[italocampoli]

Very well explained, i need to use this kind of selection to select some random data generated and delete it, then make the program in question(a simple accumulator) just start over on the cell it started. See i have developed a code but it just deletes the data and then keeps going on the next row instead of going back to the 1st row it started.

[backandtheleft]

from a teaching perspective that was a great video. cheers

[educacionportable]

Thank you so much EverydayVBA very usefull tutorial. God bless you more. Best regards from Nicaragua.

[AschwandenJakob]

You're a damn good teacher for an old fart like me.
The last programming I did was around the turn of the millennium. (except for a few modifications to old, existing ones)😂

[bradturner]

Love your VBA channel, just getting started on VBA. Was wondering how one can reference rows dynamically when wanting to hide rows? Since the design of the sheet is not complete I am inserting more rows and adding more items but when I write my code:

eg.


'** List of new Equipment Section
'** Amplifier
'** Reveals or hides row 56
'** Amplifier Reveals line: 56

'** Room Technical Mods Section
'** 1. There is an Amp, we show line 56 and lines 90-91 but hiding line 89
'** 2. There is no Amp, we hide lines 56 and lines 90-91 but show line 89
'** 3. Shows all lines for review - nothing hidden


If Not Application.Intersect(Range("N11"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Amplifier": Rows("56").EntireRow.Hidden = False
Rows("89").EntireRow.Hidden = True
= False
Case Is = "No Amplifier": Rows("89").EntireRow.Hidden = False
Rows("56").EntireRow.Hidden = True
= True
Case Is = "Reveal All Amp": Rows("56").EntireRow.Hidden = False
= False
End Select

End If
If I have to insert a new line before line 56 or even after 56 but before 89-91 then my references which to me are a direct reference no longer work on the correct rows unless I change the numbers appropriately. Is there a way to dynamically assign a name to row and reference it that way in my formulas above? Note: this code works (after lots of research) but it is not in a module, it is a worksheet macro. I do this because I want to be able to select (based on drop down list which rows are shown and not shown for what I am designing. Many thanks to anyone who can help.

[oleksijm]

best VBA channel on YT. and I've seen a few.

[bobbylooney]

Hey Chris, Great Video! I used the offset as in your video, works great. I'm using this in reports with variable numbers of rows. I can count the rows, and make my offset for the range i want, accordingly. How do I select this range in code when I have only the address for the first cell?

[mofojackson]

Hi Chris I am just getting into VBA and Macros etc and I am glad I another excellent instructor. (I have a few of you who's videos I love and prefer among so many I have searched for, and you're in that list) So at the very end with the:

Selection.Font.Bold = True

Would that 'unbolden' it once I clicked off those cells onto a different blank cell? Or do those cells stay in an a boldened appearance?

[EdiLipo]

Will you ever consider making VSTO tutorial videos?

[JDO]

Great video :) Still one question: How can I refer to a cell on another sheet? Normally this can be done by =Sheet2!B6, but how can you do it in VBA?

[mostainbillah]

Dear Sir, I have See your Tutorial, You have Not discuss, How Select Next (Middle) Empty cell Offset Function . Could You Please Describe the Middle Offset Function,

[matthewthomas]

So first, thanks for some teaching. I know I am late so not sure if you are still active.

Second, I am trying to create a Form that will input data on a specific row. I know how to get the row address, but I am not sure how to get the VBA to select that row and add the remaining data.

The code looks like:

Range("L4").Select
ActiveCell.Formula2R1C1 = _
"=CELL(""address"", INDEX('Data'!RC[-11]:R[30]C[-11], MATCH(RC[-6], 'Data'!RC[-11]:R[30]C[-11], 0)))"

Now how do I use that information to select the row and complete the data?

Maybe something like:

Row = dataSheet.Range("A" & Range("L4").Select
ActiveCell.Formula2R1C1 = _
"=CELL(""address"", INDEX('Data'!RC[-11]:R[30]C[-11], MATCH(RC[-6], 'Data'!RC[-11]:R[30]C[-11], 0)))").End(xlUp).Offset(1).Row

I think I am over complicating it....

[DaltonKevinM]

I have a workbook with 3 sheets. This workbook pulls dynamic data from a network, so while I can sometimes know what column I'm pulling from, I never know what row I'm on. Excel won't let me use the following line of code:

Sheet3.Range(Cells(report_row, 2), Cells(report_row, 3)).Select

I've also tried this without the Range method (just cells), but nope.

[maryclairehadap]

Hi i would like to ask. I have here pivot table and i would like to select the cell beside the certain cell with specific value. How do i do that?

[devabratacharya]

can you correct this code, if wrong
Cells(Sheet4.Rows.Count, "B").End(x1UP).Offset(1, 0).Select