Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Vancouver, BC
    Thanked 0 Times in 0 Posts

    Number of Rows for Macro (Excel 2003)


    This is probably simple, but I'm stumped.

    Each month I get a set of spreadsheets, each with a couple thousand rows of data in them. I have a macro that inserts a couple of new columns into each spreadsheet, performs some calculations in those columns, then deletes the original columns the calculations were based on. The macro has a count of the number of rows (e.g. it will do the calculations in the new columns from Row R2 to R4867) So far, so good.

    The number of rows in the spreadsheets vary from month to month. So before I run my macro each month, I open each spreadsheet and see how many rows there are. Then I update the VBA code in the macro so that I've got the correct number of rows in it. I then run the macro and repeat this procedure for each spreadsheet.

    What I'd like to do is correct the macro so that it will always "know" how many rows are in each spreadsheet, and do the calculation functions based on that number of rows. I thought this might involve using the ROWS() function in Excel, but I haven't figured out how.

    Anyone have an idea?



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Number of Rows for Macro (Excel 2003)

    Is there a specific cell that will always be populated in the last row, for example the cell in column A? If so, you can do it like this:

    Dim lngMaxRow As Long
    lngMaxRow = ActiveSheet.Range("A65536").End(xlUp).Row

    lngMaxRow will contain the highest row number. You can use it in your code, for example:

    Dim lngRow As Long
    For lngRow = 2 To lngMaxRow
    ' do something with cells in row lngRow
    Next lngRow


    Range("P2:P" & lngMaxRow).ClearContents

    You can replace ActiveSheet with a reference to a specific sheet (and workbook, if necessary).

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Number of Rows for Macro (Excel 2003)

    I prefer this vairation on the method used by Hans since it will work on sheets that have more than 65,536 rows.

    Dim lLastRow As Long
    lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Number of Rows for Macro (Excel 2003)

    An excellent suggestion.

    We should all probably remember to start writing code (even in answers to earlier XL versions) to allow compatibility with XL2007 (and the analogous version for columns. )

    We should also remember to DIM col numbers as long as well....


Posting Permissions

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