jwise
02-22-2010, 07:42 PM
Hi,
I have several "tables" (data worksheets) which change in size (usually growing in length). So I found some code which would find the last row number, thus telling me how many rows were in the worksheet. This count was often used as a counter in a For/Next loop.
This code works about 99% of the time. I have no clue why it occasionally fails. I have included a workbook with the code and a dummy routine to demonstrate its use. When it is wrong, it seems it's always returning the row count + 1.
Any ideas?
Public Function GetLastRowWithData(ws As Worksheet) As Long
'
'
' Function calculates last row with data
' From: See wb for code attribution...
Dim row As Long
Dim ExcelLastCell As Range
Set ExcelLastCell = ws.Cells.SpecialCells(xlLastCell) ' rqrd for either
row = ExcelLastCell.row
Do While Application.CountA(ws.Rows(row)) = 0 And row <> 1
row = row - 1
Loop
GetLastRowWithData = row ' Row number
End Function
You normally invoke like this:
Sub CountRows
Dim ws1 as Worksheet
Dim rowCt as Long
Set ws1 = Sheets("Sheet1")
rowCt = GetLastRowWithData(ws1)
MsgBox rowCt
Set ws1 = Nothing
End Sub
Please note that the code works for this case. I wish I could figure out how to make it fail! Maybe then I could prevent the failure.
The usual failure is the rowcount returned is the actual count + 1.
I can get around this problem by editing the worksheet, and deleting rows after the last data row. I have no idea why this works. Since the failures are random, many other things are effected.
Thanks for looking.
I have several "tables" (data worksheets) which change in size (usually growing in length). So I found some code which would find the last row number, thus telling me how many rows were in the worksheet. This count was often used as a counter in a For/Next loop.
This code works about 99% of the time. I have no clue why it occasionally fails. I have included a workbook with the code and a dummy routine to demonstrate its use. When it is wrong, it seems it's always returning the row count + 1.
Any ideas?
Public Function GetLastRowWithData(ws As Worksheet) As Long
'
'
' Function calculates last row with data
' From: See wb for code attribution...
Dim row As Long
Dim ExcelLastCell As Range
Set ExcelLastCell = ws.Cells.SpecialCells(xlLastCell) ' rqrd for either
row = ExcelLastCell.row
Do While Application.CountA(ws.Rows(row)) = 0 And row <> 1
row = row - 1
Loop
GetLastRowWithData = row ' Row number
End Function
You normally invoke like this:
Sub CountRows
Dim ws1 as Worksheet
Dim rowCt as Long
Set ws1 = Sheets("Sheet1")
rowCt = GetLastRowWithData(ws1)
MsgBox rowCt
Set ws1 = Nothing
End Sub
Please note that the code works for this case. I wish I could figure out how to make it fail! Maybe then I could prevent the failure.
The usual failure is the rowcount returned is the actual count + 1.
I can get around this problem by editing the worksheet, and deleting rows after the last data row. I have no idea why this works. Since the failures are random, many other things are effected.
Thanks for looking.