Hey folks, I'm back for some more punishment.
I've an application that must know how many rows in a worksheet are populated with data. Some cells in any given row are expected to not contain data. The first row that contains no data is my endpoint.
What I keep receiving is an Overflow message, presumably because I must be counting every...single...row, until I run out of memory.
Here's my snippet, and from a worksheet that has 2 rows, each containing various columns of data up to 10 columns:
spreadsheet:
Column 1 Column 2 Column 3
1 green blue pink
2 one two three
3 a b c
Code:
Dim cnDB As DAO.Database
Dim rst As DAO.Recordset
Dim xlsAPP As Excel.Application
Dim xlsWBK As Excel.Workbook
Dim xlsWST As Excel.Worksheet
Dim strPath As String
If cnt = 1 Then
msgbox "<placeholder>"
Else
Set xlsAPP = New Excel.Application
strPath = "C:\MyPath\" & "file1.xls"
xlsAPP.Workbooks.Open (strPath)
Set xlsWBK = xlsAPP.ActiveWorkbook
Set xlsWST = xlsWBK.Sheets("WS1")
xlsWST.Activate
Dim strstorage As Integer
With xlsWST
Do While Not IsNull(.Application.Rows.Cells(, "B"))
strstorage = .Application.Rows.Count
Loop
End With
Thanks for any helpful input.
disclaimer: I will only be using DAO.
Counting rows in Excel worksheet
