0

Thanks again. I have this code again, I am trying to get the row number of the last used cell. the code doesn't work properly whenever there is a space in between the data in the the specified range. I just stops at the first row.

Sub MyCode()
ActiveSheet.Range("A9:A27").End(xlDown).Select
MsgBox ActiveCell.Row

End Sub

The issue i have with this code is that in the range A9:A27 if there is an empty row somewhere at A18 it stops there and gives the Row as 18 without even going down to A27

Maine
  • 27
  • 5

1 Answers1

0

Here is the corrected code.

Sub MyCode()

    Dim Rng As Range

    With ActiveSheet
        Set Rng =  .Cells(.Rows.Count, "A").End(xlUp)
        MsgBox Rng.Row
    End With
End Sub

To understand this code you need to know the syntax for defining a cell.

Set MyCell = Cells([Row number], [Column number or ID])

In the above code .Rows.Count is a large number (1048576 to be exact). .Cells(.Rows.Count, "A") defines the cell A1048576. Now, from there you look for the "End" going "xlUp" and arrive at the last used cell in the column.

You would use exactly the same method to find the last used cell horizontally, like Cells(2, Columns.Count).End(xlToLeft). Here the sheet isn't specified and therefore the ActiveSheet by default.

Observe the double reference to ActiveSheet in .Cells(.Rows.Count, "A") as indicated by the leading periods. Both the Rows.Count and the cell itself must be in the same sheet.

Since the code explained here defines a cell, and since a range is defined by its first and last cells, you can define a range in column A as shown below.

Sub MyCode2()

    Dim Rng As Range

    With ActiveSheet
        Set Rng =  .Range(.Cells(30, 1),  .Cells(.Rows.Count, "A").End(xlUp))
    End With
    MsgBox Rng.Address(0, 0)
End Sub
Variatus
  • 14,293
  • 2
  • 14
  • 30