Excel VBA range select

Question

I have a macro in which I need to select the range R2:last row in sheet. However the last row in my sheet might be blank in column R. At the moment I am using this

Dim t As Range
Set t = Range("R2", Range("R1000").End(xlUp))

For Each Cell In t
    If IsEmpty(Cell) Then
        Cell.Activate
        ActiveCell.Value = "To Be Picked Up"
    End If
Next

However if the last row has a blank in column R then it gets ignored. I am hoping to pull the range using column A, as the last row of data always has column A. So something like,

Dim t As Range
Set t = Range("R2", Range("A1000").End(xlUp).ActiveCell.Offset(0, 17))

For Each Cell In t
    If IsEmpty(Cell) Then
        Cell.Activate
        ActiveCell.Value = "To Be Picked Up"
    End If
Next

It seems so simple but I'm sure im missing something stupid. Any help or alternative methods would be helpful thank you.

SierraOscar · Accepted Answer · 2015-08-21 13:31:17Z

This should do the trick in one line:

Sub SO()     
    Range("R2:R" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = "To Be Picked Up"
End Sub

But in answer to your question specifically

Set t = Range("R2:R" & Range("A" & Rows.Count).End(xlUp).Row)

The Range() method will accept a string as an argument to obtain a range.So we can build this string any way we want:

"A1000"                '// A1000
"A" & 1000             '// A1000
"A" & "10" & "00"      '// A1000
"A" & CStr(1001 - 1)   '// A1000

"A" & Rows.Count will return A65536 or A1048576 depending on the type of worksheet.
Range("A1048576").End(xlUp) as you know, will retrieve the last cell in that area, or the first cell in the next area on the direction specified.
Range("A1048576").End(xlUp).Row will return the row number of that cell (let's say it's A1000 for argument's sake) so the return value is 1000.
"R2:R" & Range("A" & Rows.Count).End(xlUp).Row therefore makes the string R2:R1000.
So finally, Range("R2:R" & Range("A" & Rows.Count).End(xlUp).Row) is the same as Range("R2:R1000")

Collectives™ on Stack Overflow

Excel VBA range select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related