VBA assigning ActiveCell.SpecialCells to variable

[illusionek]

Hi

Can anyone please explain why below code works?

If I do debug.print x I can see that x got value of the last cell rather than the address of last cell. So it is not clear to me how below loop works if as a second argument of Range I entered is x, which should not provide correct value (according to debug.print) but code is working well. So I am not sure what’s going on here. When I tried to assing ActiveCell.SpecialCells(xlCellTypeLastCell).Address to x I got mismatch error.

Sub test()
   
    Dim x As Range
 
    Set x = ActiveCell.SpecialCells(xlCellTypeLastCell)

    For Each x In Range("d4", x)
    x.Offset(0, 1) = x * 1.5
    Next x
   
 
End Sub

 

[Jerry Sullivan]

Hi illusionek,

In your code example the Range variable x is initially assigned the Range Object returned by ActiveCell.SpecialCells(xlCellTypeLastCell)

The Application.Range(Cell1, Cell2) property accepts many data types for the Cell1 and Cell2 parameters.
So when your code evaluates the expression,

Range("d4", x)

...it uses x as a Range Object instead of reading the Value property of that Range.

When you observe that you "can see that x got value of the last cell rather than the address of last cell" that's probably because hovering your cursor over the "x" variable or doing a Debug.Print x statement will return the default Value property. That doesn't mean that x has been assigned just that Value- it's been assigned the entire Range Object.

btw, Even though your code works, I'd recommend against using the variable x for both Each cell in a Range and one of the arguments used to define that Range. At best, it makes the code harder to follow. At worst, in similar scenarios, it can lead to unintended consequences.