Results 1 to 5 of 5

ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

This is a discussion on ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value within the Excel Questions forums, part of the Question Forums category; I say "wrong" because it's obviously working, it's just not giving me the value I want to see. I am ...

  1. #1
    Board Regular
    Join Date
    Mar 2012
    Posts
    130

    Angry ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

    I say "wrong" because it's obviously working, it's just not giving me the value I want to see.

    I am running this macro on a file I receive each month, which has a varied number of lines.

    Code:
    'Creates column X which will be used to sort Subscription from Perpetual 
        Range("X6").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(ISERROR(FIND(""OLV"",RC[-9],1)),""Subscription"",""Perpetual"")"
        Range("X6").Select
        
      
    'Select cell X6, then autofill the contents of U6 down to whatever the final "used" row in the sheet is
        Range("X6").AutoFill Range("X6:X" & ActiveSheet.UsedRange.Rows.Count)
    The macro works..... kind of.

    The issue with this is that there are ALWAYS 4 lines on the bottom of the sheet that this macro does not count, and I cannot figure out why! The current sheet I'm on has 6864 rows, but the macro runs only down to 6860, no matter what I try. I can put values into the cells themselves, change the formatting on adjacent cells, sorting the sheet... nothing seems to get Excel to understand that I am actually using rows 6860:6864.

    I tried running the macro, then running this afterwards:

    '
    Code:
    Range("X6").Select
        'Selection.End(xlDown).Select
        'Selection.AutoFill Destination:=Range(ActiveCell.Address & ":X" & ActiveSheet.UsedRange.Rows.Count)
    but I think I messed up the syntax so it won't run. I'm trying to say "Okay Excel, select X6 then ctrl+down to the last cell you've got info in, (in this case, 6860.) Then from there, take whatever the active cell is, and fill down to wherever the last row used is." My hope in this was that it would see its mistake and fix it, but alas my syntax is bad so it just errors on me.

    Regardless of the number of rows I have in the sheet (as it varies by month,) there are always these FOUR rows at the end that just don't get counted, regardless of the fact that the exact same CELLS in columns A:W are filled in rows 1:end.


    It's driving me crazy! Can someone help me fix this?

  2. #2
    Board Regular
    Join Date
    Sep 2004
    Posts
    572

    Default Re: ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

    Would this get you anywhere?

    Code:
    Sub What_Does_This_Do()
        Dim LR As Long
        LR = Cells(Rows.Count, 1).End(xlUp).Row    '<---- Change the 1 (one) to the column number you're counting the rows in
        With Range("X6:X" & LR)    '<---- Do you want the formula in Column X
            .Formula = "=IF(ISERROR(FIND(""OLV"",RC[-9],1)),""Subscription"",""Perpetual"")"
        End With
    End Sub
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,316

    Default Re: ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

    I would use the method jolivanes posted, but a possible answer to your original question might be that UsedRange does not always start in cell A1, rather, it starts with the first cell that is in use... so, if you go to an empty sheet and put a value in C6 and another value in H12, then the UsedRange will be C6:H12. Do you have any data in your first four rows? If not, that is why your count is not coming up with the value you were expecting.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Mar 2012
    Posts
    130

    Default Re: ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

    This is great, both of you. Thank you! My UsedRows does NOT start on row 1, it starts on row 5, which means this all makes sense now.

    Thank you!

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,501

    Default Re: ActiveSheet.UsedRange.Rows.Count is returning the "wrong" value

    Another method you can use that utilizes the usedrange is

    ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1


    Though I also prefer the method jolivanes posted.
    However, using the above is usefull if you have several columns, and each column ends in a different row.
    And you need the row of the column that has the most data.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

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