How to select the last cell in a currently selected range

[Launchnet]

The following code selects all cells from Q12 through the last data row of database.

Let's assume the last row of data is row 300. After the cells of Q12 thru Q300 are selected, I want the active cell to be 1 cell below the last data row. The previous selection is then no longer needed.

My request is for a diffeent need, than what this code is used for else where..

Sub SelectAlleMails()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
    Set Wks = Worksheets("email list")
    Set Rng = Wks.Range("Q12") 
    Set RngEnd = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not RngEnd Is Nothing Then
       Set Rng = Rng.Resize(RngEnd.Row - Rng.Row + 1)
       Rng.Select
    Else
       MsgBox "Range is Empty."
    End If
    Selection.Copy
End Sub

Thanks for the help
Matt

[davesexcel]

Try this,

Sub SelectFirstCellAfterLastCell()

Dim Rws As Long, Rng As Range


Rws = Cells(Rows.Count, "Q").End(xlUp).Row + 1

Set Rng = Cells(Rws, 17)

Rng.Select


End Sub

[JBeaucaire]

A quick way to select the first empty cell in column Q by looking UP from the bottom of the worksheet:

Range("Q" & Rows.Count).End(xlUp).Offset(1).Select

[Launchnet]

Thanks for the try. I forgot to say that there are blank cells in various cells, so I can't use either method. I happen to know these two ways, but I don't know how to get around the blank cells. The sample code I'm using selects all cells including empty cells, all the way down to and including the last data row of the database.

Please take another look

Thanks

Matt

[Leith Ross]

Hello Matt,

A minor change is all that is needed.

Sub SelectAlleMails()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet

    Set Wks = Worksheets("email list")
    Set Rng = Wks.Range("Q12") 
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)

    If RngEnd.Row < Rng.Row  Then
       MsgBox "Range is Empty."
    Else
       RngEnd.Offset(1,0).Select
    End If
  
End Sub

[Launchnet]

Good Morning Leith . . . Good To See Your Name

Only 1 problem . . .

Column Q does not always have data in any particular cell. There currently happens to be 8 blank cells in column Q at the bottom. Some cells in Column Q will never have data. This causes me a problem, as I need to have the last data row, in column Q, selected. Then, I can move the selection down 1 cell and then move it to column "A" on the same blank row or record.

I need this macro so that I can add a new record in the 1st row after the current data row.

Hope that I have explained this to where it can be understood.

Thanks Much

Matt

[Leith Ross]

Hello Matt,

You are up early this morning. If "Q" extends to the end of the database then maybe this change is what you need. This will select one row below the last row of the database.

Sub SelectAlleMails()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet

    Set Wks = Worksheets("email list")
    Set Rng = Wks.Range("Q12") 
    Set RngEnd = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)

    If Not RngEnd Is Nothing Then
       Set Rng = Wks.Cell(RngEnd.Row + 1, "G")
       Rng.Select
    Else
       MsgBox "Range is Empty."
    End If

End Sub