Excel VBA select diagonal

Question

I have a question about using vba and select diagonal value.
For example:

I want to select diagonal value by using vba.
And the code I tried:

Sub SelectDiagonal()

a = Selection.Row()
b = Selection.Column()
c = Selection.Rows.Count
d = Selection.Columns.Count

Dim Rng As Range
Set Rng = Cells(a + c - 1, b)

 For i = 1 To c - 1

  Set Rng = Union(Rng, Cells(a + c - 1 - i, b + i))

 Next
Rng.Select

End Sub

However, it did not work.
Any help? Thanks

[Edit] Sorry for my unclear explanation.
I know this code can work when I select the square A1:D4.
But, what I want is when I just select A4 and execute code. It will automatically select the diagonal for me rather than selecting square before executing.

Answer 1

You could use CurrentRegion and specify any cell in the square except you can't have data immediately adjacent to the square.

To quote from MSDN:

Range.CurrentRegion:

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns.

So if you have data immediately adjacent to the square then the boundary is on the other side of the adjacent data, where the blank cells begin, and not at the end of the square.

Option Explicit

Sub SelectDiagonal()

Dim myRange As Range
Set myRange = Activesheet.Range("A4").CurrentRegion

Dim a As Long, b As Long, c As Long, d As Long, i As Long

a = myRange.Row()
b = myRange.Column()
c = myRange.Rows.Count
d = myRange.Columns.Count

Dim Rng As Range
Set Rng = Cells(a + c - 1, b)

 For i = 1 To c - 1

  Set Rng = Union(Rng, Cells(a + c - 1 - i, b + i))

 Next
Rng.Select

End Sub

I wouldn't normally advocate using UsedRange but, you can here, if your data is set up as shown. It will fall over if there is other data in the sheet.

To quote again from MSDN:

Worksheet.UsedRange Property (Excel)

Returns a Range object that represents the used range on the specified worksheet

So, for example, if cell I7 also had data then the Activesheet.UsedRange.Address would become $A$1:$I$7 not $A$1:$E$5.

Simply use this line instead in the code above

Set myRange = ActiveSheet.UsedRange

There are lots of other ways to essentially define the range (myRange) to work with including finding the last row, last column or last cell and using that in defining the range to work with. Ron De Bruin does a great piece on this Find last row, column or last cell

I think CurrentRegion is quite simple in this instance. The solution you choose will be determined by the layout of your data.