Relative cell reference selecting enitre row and entire column?

[Lil2606]

Hi all,

I literally started learning VBA 3 days ago, using excel-pratique's VBA course but as I'm trying to be thorough I started to make a "dictionary" and I'm writing up the different selections that are possible.

Could someone send me a working code for: Select entire Row AND entire Column, 1 Row down AND 1 Column to the right from the reference cell A1 , and explain a bit, why

---ActiveCell.Offset(1, 1).Range("1:1, A:A").Select--- doesn't work (application defined or object defined error), and how does their code work?

Thank you very much for the help!!

 

[MARK858]

First of all there is no need to select cells to work with them so the code below highlights the ranges.
You can use union to join the ranges . There are other ways to achieve the result.


Obviously ask about anything you don't understand.

Sub HighlightRow()
    Dim xRng As Range
    'Declare the variable
    Set xRng = ActiveCell.Offset(1, 1)
    ' assign the cell to the variable
    Union(xRng.EntireRow, xRng.EntireColumn).Interior.ColorIndex = 6
    ' use Union to join the ranges
End Sub

 

[Lil2606]

Hi, thank you for your reply but why is it not necessary to select cells or ranges to work with them? Do I need to select them if I want to copy them?

 

[MARK858]

No you don't need to select to copy and the big issue is it slows your code down dramatically as well as screen flicker. You also can't select anything not on the active sheet.