Need an autoselect macro that is activated by a button

[lauraexcels]

Hi guys

I am a medical transcriptionist (in training), and I have two ever-growing lists in an excel workbook (on separate sheets). What I would like to do is be able to plop a button down at the top of the sheet, and have a macro that checks to see where the last row and column of data is, and select it. It also needs to work from a starting point, as I don't want to sort column A, and I don't want to sort the first 4 rows. If anyone could give me advice on how to go about doing this, I would really appreciate it. It would be nice, since I am new to VBA, to actually learn how to do it on my own. So guidance, rather than code, would be great! Thanks

[tony h]

You can do it simply from the spreadsheet.

Select the first cell then hold down the CTRL and SHIFT and then press the END button

[Kalithro]

Can shorten with some dims.

ThisWorkbook.Sheets("Sheet2").Range(ThisWorkbook.Sheets("Sheet2").Cells(5, 2), ThisWorkbook.Sheets("Sheet2").Cells(5, 2).SpecialCells(xlLastCell)).Select

More specific...

Sub Macro1()
'
Dim rng As Range
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

    Set rng = sh.Range(sh.Cells(5, 2), sh.Cells(5, 2).SpecialCells(xlLastCell))

    With rng
    
    .Sort Key1:=sh.Cells(5, 2), Order1:=xlDescending, Orientation:=xlTopToBottom
    End With
    
End Sub