I am trying to get the last row of data number from an excel sheet. but it always returns row number of the last row 10200. the sheet I am using varies row numbers from 20 to some times as much as 19000.
Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(objDialog.FileName)
set xlSht = xlApp.activesheet
'xlApp.DisplayAlerts = True
xlApp.Visible = True
lastrow = xlSht.UsedRange.Rows.Count
It doesn't seem to give me the used range lust the total
No that wont solve it. I was just saying that formatting will count as the used range. Is there anything else in the file? Maybe a bit of data you forgot about at the bottom? Any colors? A white background counts as a color. The default is "no fill".
Hmm I dont know whats there. But I copied all the cells and pasted to a new workbook and now it shows 19 like its suppose to. Thanks
I dont know what I fixed but all is well. I thought that, code should have worked properly.
Thanks for the brain duster
I found this method (can't find the origin now, so I can't give proper credit, but it's not mine). Sorry I can't give credit to the code originator, but thanks anyway, the code works well.
It works very well, because it always looks for some value in the last cell.
copy (between dashes) to the declaration
' create the row and col indicators outside.
' this makes them available anywhere
Public intFirstRow As Integer, intFirstCol As Integer, _
intLastRow As Integer, intLastCol As Integer
Public usedRng As Range
copy (between dashes) as a sub
' This sub receives the empty argument "usedRng" and determines
' the populated cells of the active worksheet, which is stored
' in the variable "theRng", and passed back
' to the calling sub (i.e usedRng, see below)
Sub DetermineUsedRange(ByRef theRng As Range)
On Error GoTo handleError
intFirstRow = Cells.Find(What:="*", _
intFirstCol = Cells.Find(What:="*", _
intLastRow = Cells.Find(What:="*", _
intLastCol = Cells.Find(What:="*", _
Set theRng = Range(Cells(intFirstRow, intFirstCol), _
The call to the sub is simple
' alway set usedRng to nothing prior to call
Set usedRng = Nothing
Then intFirstRow, intFirstCol, intLastRow, intLastCol are available as true first and last pointer to "visible data".
I have never used usedRng, so I can't say how it works.
Be careful with active autofilters, they can confuse things.
To find the last row, you should use something like that
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
This will give you the last row of column "A"
Another way is like this
Dim LastRow As Long, LastCell As String
LastRow = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
LastCell = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Address
This method has a little problem though... If the sheet is empty, it will return an error. But you can use it to find the last row, column or address of the whole Range. Just change the last word...
"UsedRange" is like other methods that I forgot and don't want to remember...
It remembers all the changes made in the sheet.
Let's say you have a Range of 100 lines. You go to the line 1000, write something and erase it. UsedRange will return 1000.
The methods I mentioned don't have that problem.
'__ To demonstrate how to count only visible rows
'__ in autofiltered data
'set up an Autofilter
'cancel possibly existing autofilter
.AutoFilterMode = False
'for this examle the data goes from A1 to IV1
'and it's column 5 we want to autofilter
'and it's only the "toaster"s we want in the list
.Range("A1:IV1").AutoFilter field:=5, Criteria1:="toaster"
'initialize a range
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
'extend the range to include the autofilter column
With rng.Resize(, 5)
'-1 is needed on the next line to exclude the top (column name) row
intNumberRows = rng.SpecialCells(xlCellTypeVisible).count - 1
There is a problem with the (my) previous examples "IF" you want to use it on autofilter data.
In the code shown here, intNumberRows should give you the row count, excluding the invisble rows, of a range of filtered data.