YEL Tutorial on Excel VBA: Selecting and Referring to Cells

Overview

• Understanding how to select and refer to cells in Excel VBA is fundamental.
• Techniques covered:
  • Absolute referencing using range or cells.
  • Using ActiveCell keyword.
  • Selecting multiple cells.
  • Using range names.
  • Relative selection methods.

Absolute Position Selection

Using Range and Cells

• Range: Used to select individual cells by referencing cell address.
• Cells: Allows referencing by row and column numbers.
• ActiveCell: Refers to the currently selected cell.
  • Change value using ActiveCell.Value = ....

Selecting Multiple Cells

• Range: Use a colon to select a block of cells, e.g., Range("A1:C1").
• Selection: Refers to and modifies multiple selected cells.
  • Example: Selection.Interior.Color = ....
• Shortcut with Square Brackets: Shorthand for Range without Intellisense.
• Range Names:
  • Created in Excel; refer to specific cells or blocks.
  • Use Range("Name") to refer to named ranges.

Relative Selection Techniques

Using End and Offset

• End: To navigate to the end of a list: ActiveCell.End(xlDown).
• Offset: To move a specific number of rows or columns: ActiveCell.Offset(1, 0).
  • Combining: Range("A1").End(xlDown).Offset(1, 0).Select.

Changing Values Without Selection

• Directly modify cell values: Range("A14").Value = 12.
• Extend referencing to specific worksheets/workbooks before modifying cell values.

Advanced Techniques

Selecting Continuous Regions

• CurrentRegion: Automatically selects an entire block of contiguous data.
  • Use: Range("A1").CurrentRegion.Copy to copy the entire data block.

Dynamic Range Selection

• Select blocks regardless of size using:
  • Range("A3", Range("A1").End(xlDown)).Select
  • Extend to multiple columns: Range("A3", Range("A1").End(xlDown).End(xlToRight)).Select

VBA Copy and Paste

Basic Copy and Paste

• Use Copy and PasteSpecial to move data between sheets.
• Methods to adjust column widths:
  • PasteSpecial with xlPasteColumnWidths.
  • Columns("B:C").AutoFit() to adjust automatically.

Copy with Destination

• Use Copy Destination:=... to copy directly into another sheet or workbook.
• Avoids needing to Select and Activate sheets or cells.

Practical Application

• Each technique demonstrated through examples based on a top 10 films list.
• Procedures include selecting cells, modifying content, and dynamically interacting with Excel data without manual selection.

Conclusion

• Understanding these selection methods is crucial for effective Excel VBA programming.
• Further resources available at YEL's website for more detailed training.