end of row? in a macro

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: end of row? in a macro

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am using Excel 97 and I have written a macro will be applied to a number of spreadsheets with same formats but varying column lengths. Is there a bit of code that XL 97 will recognize as the end of a column that I can replace in my Macro(eg instead of Range (A2:A89)(for argument sake) so that each worksheet will see this as the end of column and I won't have to manually change that line for each spreasheet?

  2. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is what you need:

    Range("A65536").End(xlUp).Row

    This will give you the last cell in Column A that has data.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  3. #3
    Board Regular
    Join Date
    Jan 2003
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to clarify Phantom's suggestion,

    Range("A65536").End(xlUp).Row returns the row number of the last cell with data.

    Range("A65536").End (xlUp) is the range of the last cell with data.

    Bob

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A little VBA demo for you :

    Public Sub LastRowDemo()
    LastRow = Range("A65536").End(xlUp).Row
    NextEmptyRow = Range("A65536").End(xlUp).Row + 1

    NL = Chr(13) & Chr(10)
    ln1 = " In colA the Last Row Used is " & LastRow & NL
    ln2 = " In colA the Last Row UnUsed is " & NextEmptyRow & NL
    ln3 = " I will now select the used range for you..starting at A2" & NL
    Title = "Last Row Demo"

    pt = MsgBox(ln1 & ln2 & ln3, vbInformation, Title)

    Range("A2:A" & LastRow).Select
    End Sub

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    New Member
    Join Date
    Dec 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is the line "Range("A65536").End(xlUp).Row" going to replace "89" verbatim? This is what Bob is suggeting!?

  6. #6
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PUP:

    Look at the demo i've provided for you..

    LastRow = Range("A65536").End(xlUp).Row
    Range("A2:A" & LastRow).Select

    Range("A65536").End(xlUp).Row will give you the row number

    _________________
    NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
    Adieu,Nimrod
    [ This Message was edited by: Nimrod on 2003-02-15 13:14 ]

  7. #7
    Board Regular
    Join Date
    Jan 2003
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is what I was going to post in response to "PUP's" last question, but since it is pretty much identical to Nimrod's I decided not to.

    Try this:
    Dim lngLastRow As Long
    lngLastRow = Range("A65536").End(xlUp).Row
    Range ("A2:A" & lngLastRow)

  8. #8
    New Member
    Join Date
    Dec 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's the relevant line of code as originally written "Selection.AutoFill Destination:=Range("B2:B89"), Type:=xlFillDefault then I replaced 89 with Range("A65536").End(xlUp).Row. I tried parentheses, quotes but I am still getting a syntax error. Please pardon my ignorance.

  9. #9
    New Member
    Join Date
    Dec 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's the relevant line of code as originally written "Selection.AutoFill Destination:=Range("B2:B89"), Type:=xlFillDefault then I replaced 89 with Range("A65536").End(xlUp).Row. I tried parentheses, quotes but I am still getting a syntax error. Please pardon my ignorance.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    LastRow = Range("A65536").End(xlUp).Row


    Selection.AutoFill Destination:=Range("B2:" & LastRow & "), Type:=xlFillDefault "

    [ This Message was edited by: Nimrod on 2003-02-15 13:39 ]

User Tag List

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
  •