Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)
Why Stack Overflow recommends it
Stack Overflow users, especially frequent VBA answerers, link it when askers struggle with referring to ranges, finding the last row, or relying on Select and ActiveCell.
Recommended by 3 people across 26 different months, 2016 to 2024.
What it is
A near one-hour Excel VBA tutorial by Andrew Gould of Wise Owl, part 5 of an introductory series. It covers referring to and selecting cells with Range, Cells, ActiveCell, End and Offset, selecting regions, plus basic formatting and copy/paste in VBA.
What people said
Strong rec user6432984 · 2016-11 · Stack Overflow · on “VBA code in Excel is very slow (copying 130 cells takes 15 sec)”
You should watch this series Excel VBA Introduction [LINK] . This one is a must: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video]
Strong rec user6432984 · 2016-12 · Stack Overflow · on “Go to next visible cell using offset”
You should watch this video series: Excel VBA Introduction [LINK] . This one is a must: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video]
Strong rec user6432984 · 2016-12 · Stack Overflow · on “VBA: PasteSpecial method of Range class failed”
Must watch: Excel VBA Introduction [LINK] . This is the relevant video: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video]
Strong rec user6432984 · 2017-09 · Stack Overflow · on “Excel VBA - Do Until Blank Cell”
Stop what you are doing and watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video] . You should watch the entire series but that video is a must see.
Recommends user6432984 · 2016-07 · Stack Overflow · on “How to create dynamic variable names VBA”
This will get you started. But before you start I recommend watching these WiseOwlTutorials tutorial on Youtube: Selecting Cells (Range, Cells, Activecell, End, Offset) [this video] Worksheets, Charts and Sheets [LINK] Variables [LINK] Arrays [LINK] Dim i, x As Integer x = Range("J4").Value Dim Team() As Integer Dim Manager() As String ReDim Team(1 To x) As Integer ReDim Manager(1 To x) As String Range("A3").Select For i = 1 To x Team(i) = i Next
Recommends user6432984 · 2016-07 · Stack Overflow · on “Excel VBA Range Variable Throws Error 1004 - I'm in”
Set rngDataHeadings = Range("B11", "T11") references B11:T11 of the ActiveSheet. Selecting another worksheet and try rngDataHeadings.Select will throw an exception Runtime Error '1004' Select method of Range class failed It's best to avoid Select and Active. You should watch Selecting Cells (Range, Cells, Activecell, End, Offset) [this video] If you have standard tables this will work. Sub FormatTable(wsWorksheet As Worksheet, HeaderAddress As String) Dim rDataBody As Range Dim rHeader As Range With wsWorksheet Set rHeader = .Range(HeaderAddress, .Range(HeaderAddress).End(xlToRight)) Set rDataBody = Range(HeaderAddress).CurrentRegion Set rDataBody = rDataBody.Offset(1).Resize(rDataBody.Rows.Count
Recommends user6432984 · 2016-10 · Stack Overflow · on “Excel vba paste on last row of data”
You need to watch all the Wise Owl VBA tutorials on Youtube. Here is the relevant on Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video]
Recommends user6432984 · 2016-11 · Stack Overflow · on “VBA code in Excel is very slow (copying 130 cells takes 15 sec)”
@IstvanHeckl you should watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video] and Part 6 - Worksheets, Charts and Sheets [LINK]
Recommends user6432984 · 2016-12 · Stack Overflow · on “Refering Workbooks Using a Variable”
STOP!!!! Watch this video: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) - YouTube [this video]
Recommends user6432984 · 2016-12 · Stack Overflow · on “xlDown not working as intended”
It is better to refer to cells directly, instead of selecting them and referring to the ActiveCell . Watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [this video] It looks like you are trying to make a unique identifier (ID) to refer to your records. But you are adding 1 to the empty cell below: ActiveCell.Value = ActiveCell.Offset( 1 , 0).Value + 1 instead you should be adding 1 to the value of the cell above ActiveCell.Value = ActiveCell.Offset( -1 , 0).Value + 1 Sheet1.Activate With Range("B2").End(xlDown).Offset(1, 0) .Value = .Offset(-1, 0).Value + 1 .Offset(0, 1).Value = txtSifraOsobe.Value .Offset(0, 2).Value = txtImeIPrezime.Value .Offset(0, 3)