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

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
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

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top