0

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.

Community
  • 1
  • 1
Tony Chivers
  • 191
  • 11
  • 4
    Conditional formatting? Will need to see an example of your excel to give you any kind of actual solution – urdearboy Nov 12 '18 at 14:45
  • When I mean highlighting I mean scroll over them ready to drag them down, not actually highlighting the with colour, maybe selecting is a better option. – Tony Chivers Nov 12 '18 at 14:46
  • Sorry @urdearboy my mistake! – Tony Chivers Nov 12 '18 at 14:48
  • 1
    What is your goal after `.Select`? Most of the time, this bit of code is mistakenly used as a middle operator that can be completely scrapped. What is the over all goal here? – urdearboy Nov 12 '18 at 14:50
  • I have tried to create a macro, which goes to cell A1 and presses Ctrl+A, this selects what I need A1-A3, but then if I add another item is say A4, it will only select A1-A3. – Tony Chivers Nov 12 '18 at 14:50
  • My end goal is to select the values in A1-A3 say and then drag this down so that I have a repeated list. I can do the drag down part of the formula, but I just need the select part. – Tony Chivers Nov 12 '18 at 14:52
  • 1
    Something like: `Sheet1.Range("A1:A" & Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))).Select` should do the trick. But you probably don't want to `.select` if you are going to write more code after this for that selection. – JNevill Nov 12 '18 at 14:52
  • 1
    Please look up how to find the last row of a column. Then use `Range(“A1:A” & LRow).Select` – urdearboy Nov 12 '18 at 14:52
  • I can't seem to get any joy from either of these suggestions :( – Tony Chivers Nov 12 '18 at 14:57
  • Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) – urdearboy Nov 12 '18 at 15:01
  • I added a solution and also voted to close this as a duplicate ~ – urdearboy Nov 12 '18 at 15:02
  • I still havent worked this out how to use the code but I am sure I can with your post @urdearboy thanks for your help – Tony Chivers Nov 12 '18 at 15:05

4 Answers4

1

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
urdearboy
  • 14,439
  • 5
  • 28
  • 58
0
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
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
0

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.

Allumearz
  • 259
  • 2
  • 6
0
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/

BanMe
  • 133
  • 1
  • 8