Reference Last Cell In Column

    I am recording a macro which will update formulas in a worksheet based on a new table which is pasted into a different worksheet monthly. I am trying to reference the last cell containing text in a column. I have tried using Ctrl downarrow while recording the macro, but when I look at the code, a cell reference is used. To test out the macro I inserted more rows in the main sheet and it is not referencing the last cell, regardless of how many rows there are. How can I get excel to "go to the last entry in this column" and use that cell as a reference on another sheet. Each month I want to run the macro so it will automatically use values from the last row, whatever the row number.

    Re: Reference Last Cell In Column


    Code
    Range("H65536").End (xlUp)


    will give you the last cell in column H, change the reference accordingly.


    Hope this helps you out.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Re: Reference Last Cell In Column


    try somethng like this


    Code
    Sub test()
Dim x As Range
Worksheets("sheet1").Activate
Set x = Cells(Rows.Count, "a").End(xlUp)
MsgBox x.Address
Worksheets("sheet2").Activate
Cells(x.Row, "a") = "venkat"
End Sub


    this is only a trivial example. you have to modify to suit you.

    I am not an expert. better solutions may be available. $$$venkat$$$1926@$$$gmail.com

    Re: Reference Last Cell In Column


    Quote from Barrie Davidson;303913
    Code
    Range("H65536").End (xlUp)


    will give you the last cell in column H, change the reference accordingly.


    Hope this helps you out.


    For Excel 2010, last cell is not 65536 so to make sure your code runs well on any Excel version I'd go with:


    Code
    Sheets(1).Cells(Sheets(1).Cells.Rows.Count, "B").End(xlUp).Row


    [INDENT]Where 1 specifies Sheet index (and can be replaced by Sheet name enclosed in double quotes) and "B" specifies the Column you intend to find last used cell.[/INDENT]


    tears

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login