Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Select entire Column till last row VBA

This is a discussion on Select entire Column till last row VBA within the Excel Questions forums, part of the Question Forums category; I feel this is very simple fix but my code is flawed. Dim LastRow As Long With Worksheets("Sample") LastRow = ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    48

    Default Select entire Column till last row VBA

    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

  2. #2
    Board Regular
    Join Date
    Oct 2003
    Posts
    1,322

    Default Re: Select entire Column till last row VBA

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

  3. #3
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: Select entire Column till last row VBA

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

    Code:
    With Worksheets("Sample")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("R2:R" & LastRow).Select
    End With
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,666

    Default Re: Select entire Column till last row VBA

    Wamhoi,

    Try:


    Code:
    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
    Have a great day,
    hiker95

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    48

    Default Re: Select entire Column till last row VBA

    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?

  6. #6
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: Select entire Column till last row VBA

    Something like this

    Code:
    With Worksheets("Sample")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("R2").AutoFill Destination:=.Range("R2:R" & LastRow)
    End With
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    Nov 2008
    Location
    Michigan
    Posts
    11

    Default Re: Select entire Column till last row VBA

    Quote Originally Posted by VoG View Post
    Something like this

    Code:
    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.

  8. #8
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: Select entire Column till last row VBA

    Try removing Select

    Code:
    Range("C1").CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    New Member
    Join Date
    Nov 2008
    Location
    Michigan
    Posts
    11

    Default Re: Select entire Column till last row VBA

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

  10. #10
    New Member
    Join Date
    Apr 2014
    Posts
    15

    Arrow Re: Select entire Column till last row VBA

    Quote Originally Posted by hiker95 View Post
    Wamhoi,

    Try:


    Code:
    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

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com