Dfyjkt

How do I select the cells, with contents, in column A?

For example,

I might have a list of names

John 
Jack 
James

and other times a longer list of times,

John 
Jack
James
Jimmy

These will always be in cell A1-AX, X being the final row.

For the first example, I would use the code,

Range("A1:A3").Select

For the second,

Range("A1:A4").Select

I would like to use code that automates this regardless of how many in the list.

Edit the sheet name on the 4th line of code (leave quotes there) and then run the code.

Option Explicit

Sub LRow()

 Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
 Dim LRow As Long

 LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

 ws.Range("A1:A" & LRow).Select

End Sub

Private strPrevCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r As Excel.Range

 If strPrevCellAddress <> "" Then
 Range(strPrevCellAddress).Interior.ColorIndex = -4142
 End If

 Set r = Cells(1, Target.Column)
 Set r = r.Resize(r.End(xlDown).Row, 1)
 r.Interior.Color = vbRed
 strPrevCellAddress = r.Address

End Sub

You need to refer to the Worksheet.UsedRange.Rows property to find the number of rows.

Dim x As String
x = Me.UsedRange.Rows.Count
Me.Range("A1:A" + x).Select

Make sure the sheet in question is active or you will receive application error.

Sub SelectStartToEnd(sheetName as string, Column as string, Optional StartIndex as integer)
 Dim StartHere as string

 If IsMissing(StartIndex) = False then
 StartHere = Column + CStr((1 + StartIndex))
 End If

 Sheets(sheetName).Range(startHere + ":" + Range(StartHere).SpecialCells(xlCellTypeLastCell).Address).Select
End Sub 

This goes over the Pros and cons of most methods.

https://www.excelcampus.com/vba/find-last-row-column-cell/