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

[lordterrin]

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.

'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:

'

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?

 

[jolivanes]

Would this get you anywhere?

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

 

[Rick Rothstein]

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.

 

[lordterrin]

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!

 

[Jonmo1]

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.