Excel Macros to find the length of the row

Question

I am working with Excel Macros,

I had come across command ActiveCell.End(xlDown).Select to go to the last row of the table

I am currently using this command to find the total no of rows

ActiveCell.End(xlDown).Select

y = ActiveCell.Row

but the problem is for large data the command skips the data, where is a change in data of the next row.

what maybe the possible problem?

(you can see the table 1069 & 1070 the data type is date which is in same format but the code skipped out uncertainly[note: when there is a change in second column] )

.
.
.
1068 >  20-08-2012    C0    138225    98.91608138    99.25925926
1069 >  20-08-2012    C0    138226    99.71159982    98.95776825
1070 >  21-08-2012    __    506821       65335U11     0     
1071 >  21-08-2012    C0    100381     0              0
.
.
.

I'm not sure I understand your problem. Are there blank rows in the column you are searching ? If so, you can do the opposite, start from the last row and go up using `ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row` — ApplePie, Oct 01 '12 at 13:29
See this http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Oct 01 '12 at 13:30
Maybe a screenshot of your worksheet would be helpful to understand your exact problem. — ApplePie, Oct 01 '12 at 13:30
I am counting the dates here, C0 here is not taken in account for the code, still it is affecting my code — Makarand Chaudhari, Oct 01 '12 at 13:47
again specifing there are no blank rows, there are blank rows in the next column,but they are not taken into consideration while using this command :( — Makarand Chaudhari, Oct 01 '12 at 13:52
If you run just `ActiveCell.End(xlDown).Select`, is it really ending up selecting the very last row? — mattboy, Oct 01 '12 at 14:45
It should at-least run that way, but it is not going to the last one — Makarand Chaudhari, Oct 01 '12 at 14:48
How about this `y = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row` — Scott Holtzman, Oct 01 '12 at 15:58

score 0 · Answer 1 · answered Oct 01 '12 at 19:42

I'm spinning off the comments but you should always use xlUp rather than xlDown so as to avoid problems with blanks.

Please select a cell in the target column and run the following:

Sub findTherow()
Dim y As Integer
With Excel.ActiveSheet
            y = .Cells(.Rows.Count, ActiveCell.Column).End(Excel.xlUp).Row
End With
MsgBox ("Look what I found:" & y)
End Sub

Excel Macros to find the length of the row

1 Answers1