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


Board Regular
Mar 21, 2012
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 
    ActiveCell.FormulaR1C1 = _
'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:

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
Upvote 0
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.
Upvote 0
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!
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back