VBA Syntax finding the last occupied cell in the column

Question

How do I make this work? I'm trying to make the code identify the last used cell in column C and go to it. I am having trouble with the last part which is selecting the cell in rng1

Sub jupiter3()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Belmont")
Set rng1 = ws.Columns("c").Find("*", ws.[c1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then
    MsgBox "last cell is " & rng1.Address(0, 0)
Else
    MsgBox ws.Name & " columns A:B are empty", vbCritical
End If
Range("rng1.address(0, 0)").Select

End Sub

user2140173 · Accepted Answer · 2013-08-19 14:24:42Z

1

the easiest way for me would be to use this one line of code

Sheets("Belmont").Range("C" & Sheets("Belmont").Range("C" & Rows.Count).End(xlUp).Row).Select

but if you want to modify yours then

Sub jupiter3()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("Belmont")
    Set rng1 = ws.Columns("c").Find("*", ws.[c1], xlValues, , xlByRows, xlPrevious)
    If Not rng1 Is Nothing Then
        MsgBox "last cell is " & rng1.Address(0, 0)
        rng1.Select
    Else
        MsgBox ws.Name & " columns A:B are empty", vbCritical
    End If
End Sub

Changing Range("rng1.address(0, 0)").Select to rng1.Select or Range(rng1.address).Select

answered Aug 19, 2013 at 14:24

user2140173

Sign up to request clarification or add additional context in comments.

Comments

PocketLoan · Accepted Answer · 2013-08-19 14:08:22Z

0

The rng1 variable is defined as a range which is interpreted by the language in a way in which it doesn't fit into range().select You want to define a string variable and do it like this. replace Range("rng.address(0,0)").Select with

Dim zen as String
zen=rng.address(0,0)
range(zen).select

answered Aug 19, 2013 at 14:08

PocketLoan

5324 gold badges13 silver badges29 bronze badges

Collectives™ on Stack Overflow

VBA Syntax finding the last occupied cell in the column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related