Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Select entire Column till last row VBA

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,325
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select entire Column till last row VBA

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

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    17,139
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    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

    Windows 10, Excel 2007, on a PC.

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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