+ Reply to Thread
Results 1 to 3 of 3

Fill values down to the bottom cell of a column range

  1. #1
    Registered User
    Join Date
    12-15-2006
    Location
    Bangalore India
    Posts
    2

    Fill values down to the bottom cell of a column range

    Hi,

    I want to select the bottom cell in a column.
    Define a range in the format Top to bottom cell.
    Paste Values into the cells

    ' Column L contains date values. In I2 input values based on Column L in
    ' format MONTH/2006
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(MID(RC[3],5,3),""/2006"")"
    Range("I2").Select
    Selection.Copy
    Dim i As Integer
    Dim c As Range
    ' Searches for the bottom occupied cell in the Column I
    i = Cells(9, 1).End(xlDown).Row
    Debug.Print (i)
    ' This returns 350

    ' I need to do the below dynamically
    ' Range("I3:I350").Select

    ' So I tried the following but both of the below formats error out.
    ' Range("I3:I(i)").Select
    Range(Cells(9, 3), Cells(9, i)).Select
    ActiveSheet.Paste

    Please suggest ways to do this.

    TIA
    Ramakrishnan

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Ramakrishnan

    You can use this single line of code to do what you want

    Range("i2:i" & Cells(9, 1).End(xlDown).Row).Value = "=CONCATENATE(MID(L2,5,3),""/2006"")"

    or this little macro

    Sub AddFormula()
    Dim iRow As Integer
    iRow = Cells(9, 1).End(xlDown).Row
    Range("i2:i" & iRow).Value = "=CONCATENATE(MID(L2,5,3),""/2006"")"
    End Sub

    You were close with your code of Range("I3:I(i)").
    This is what you were looking for. Range("I3:I" & i).Select
    When trying to use variables make sure you do not enclose them within " as VBA no longer see's it as a varable but as text.

    Use the & or + to join variables, strings etc into one comand

  3. #3
    Registered User
    Join Date
    12-15-2006
    Location
    Bangalore India
    Posts
    2

    Thanks

    Hi Mudraker,

    Thanks,

    It fixes that little problem.

    Regards
    Ram

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1