VBA is not selecting the last row in my data

Question

I feel like I am missing something. I have a table with ID, Name, Status as headers. The table's headers start at B2 and data starts at B3 The table is dynamic and generated by vba code from another workbook. There may be times it is filtered data but there are no gaps in the table.

There are times a user will select someone in the Name column (C). A button click should bring them down to the last record.

I get that there are lots of ways to accomplish this but I cannot seem to get any of them to work and I can't figure out why.

Here is my current code but I have tried UsedRange, CurrentRegion, Table names, the whole works.

Sub MoveToLastRow()

    Dim LastRow As Long

    LastRow = ActiveSheet.Cells(Rows.Count, "C").End(x1Up).Row

End Sub

I click on the button or try to step through the code with a name selected and it just does nothing.

Thank you for your input. ~Don

What are you expecting it to do? you're assigning the last row number to a variable but then you're not actually doing anything with it — SierraOscar, Mar 30 '16 at 14:22
Activesheet.Cells requires two numbers as arguments. You give number and "C". — Vityata, Mar 30 '16 at 14:24
@Vityata That's simply not true - try the code and see. `Cells()` is a collection object and you can access elements using their index or key (number or letter) — SierraOscar, Mar 30 '16 at 14:27
Sorry but no @Vityata. Number and "C" is valid input. In fact, Cells only requires one argument: Rows. The column index is optional. — Tim, Mar 30 '16 at 14:28
There is a syntax error in this code snippet: x1Up should be xlUp (see my detailed answer). Best regards, — Alexander Bell, Mar 30 '16 at 14:38

Alexander Bell · Answer 1 · 2016-03-30T14:37:41.397

You may use the following simple VBA code snippet to select the last cell with data in Column "C":

Sub MoveToLastRow()
    ActiveSheet.Range("C" & Rows.Count).End(xlUp).Select
End Sub

Note: your original code contains a syntax error, thus it doesn't work: End(x1Up).Row should be written as End(xlUp).Row. Following is the corrected code snippet performing the same action (i.e. selecting the last cell with data in Column "C")

Sub MoveToLastRow()
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    ActiveSheet.Range("C" & LastRow).Select
End Sub

Hope this may help.

score 1 · Answer 2 · answered Mar 30 '16 at 14:24

1

Add:

Application.GoTo Range("C" & LastRow)

at the end of your code to make the cursor move to that cell.

answered Mar 30 '16 at 14:24

SierraOscar

17,507
6
40
68

score 0 · Answer 3 · answered Mar 30 '16 at 14:30

0

In your code change .End(x1Up) to .End(xlUp) if you use Option Explicit at the top of your code it will show you that VBA thought it was a variable.

answered Mar 30 '16 at 14:30

BerticusMaximus

705
5
16

VBA is not selecting the last row in my data

3 Answers3