Select entire Column till last row VBA

[Wamhoi]

I feel this is very simple fix but my code is flawed.

Dim LastRow As Long

With Worksheets("Sample")
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("R2:LastRow").Select
End With

 

[doofusboy]

Range("R2:R" & LastRow).Select

 

[VoG]

Try this but it will error if Sample is not the active sheet

With Worksheets("Sample")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("R2:R" & LastRow).Select
End With

 

[hiker95]

Wamhoi,

Try:

Option Explicit
Sub Test()

Dim LastRow As Long
With Worksheets("Sample")
  LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("R2:R" & LastRow).Select
End With

End Sub

 

[Wamhoi]

Thanks so much, they worked out great!! For the sake of curiosity, what if you wanted to drag formulas from A2:D2 to last line?

 

[VoG]

Something like this

With Worksheets("Sample")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("R2").AutoFill Destination:=.Range("R2:R" & LastRow)
End With

 

[clmish]

Something like this

With Worksheets("Sample")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("R2").AutoFill Destination:=.Range("R2:R" & LastRow)
End With

How do I use this code when the Worksheet changes name daily? (I download data from a SharePoint list and name the tab in yyyymmdd format, as well as the file itself in order to work with another script.) I want it to refer to the current active worksheet, whatever the name. Right now when I try to run the macro to select cell C1, go to the bottom of the column, and sort ascending, I get an "object error."

The code I'm using is:
Dim LastRow As Long
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row.Range("C2:C) & LastRow).Select
Range("C1").Select
Range("C1:BK" & Range ("C1").End(xlDown).Row).Select
[And here's where it bombs]
Range("C1").CurrentRegion.Select.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

I believe there is redundancy in my Range commands too, but that doesn't seem to be the main issue.

Thanks.

 

[VoG]

Try removing Select

Range("C1").CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 

[clmish]

Thanks, VOG. Seems to have done the trick to remove the SELECT method.

 

[kit370]

Wamhoi,

Try:

Option Explicit
Sub Test()

Dim LastRow As Long
With Worksheets("Sample")
  LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("R2:R" & LastRow).Select
End With

End Sub

Hi,

In the above case how this code can be used to copy the contents what it is selected & paste it in different workbook.

Thanks,
Kit