what is wrong with this single line of code (finding last row in a column)

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
299
Code:
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Cells("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
Trying to find the last row in Column F that is not blank.

I get 'Invalid procedure call of argument' (run-time error '5': )
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,701
Office Version
365
Platform
Windows
Try using range, instead of cells...
Code:
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
Incidentally, if it's just the last used row you need, then you can use:
Code:
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F65536").end(xlup).Row
 
Last edited:

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
299
Try using range, instead of cells...
Code:
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
Incidentally, if it's just the last used row you need, then you can use:
Code:
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F65536").end(xlup).Row
Thank you, the second one worked for what I need.

Coincidentally, the first code returns a value of "1" (when I use MsgBox to show me the value of the variable) when there was actually 23 rows in column F.

The second code returns the value I was expecting (23)

Why would the first code only show "1"?

Thanks again, sykes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
It Returns 1, because you are only searching one cell (namely F1)
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
299
It Returns 1, because you are only searching one cell (namely F1)
Thank you, Fluff.

How would I go about searching starting from F1 and down to the last cell? (the 2nd code sykes provided works fine, but I would like to understand how to locate the last row going from the top and down instead of going the very last cell on the sheet and searching up...) Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Like
Code:
Range("F1").End(xlDown).Offset(1).Row
But if you have any blank cells in col F you might get the wrong result
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
299
Like
Code:
Range("F1").End(xlDown).Offset(1).Row
But if you have any blank cells in col F you might get the wrong result
That's it. Thank you. & FWIW I dont have any blanks. Column E does have blanks in it, but the procedure right before this one, I had it copy just the cells with content from column E and paste that over to column F cell 1. thanks again for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,889
Messages
5,489,543
Members
407,697
Latest member
Lotte_4

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top