Results 1 to 9 of 9

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,319

    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
    61,507

    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
    9,970

    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
    61,507

    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
    61,507

    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.

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