Vba Code To Count Rows

Hi,

I’m looking for a VBA code which counts the number of rows which contains data.
I was looking in the forum and found

Thread

Loading data from access with no access licience on machine

Can any one help? I have written a program to pull data from an access data base how ever i will need to use this program on machines that do not have an access licience is there any way of still linking to the access data base via the VB in excel with out a Access licience?

Any help would be great.

Thanks

ash :guitar:

Ash12222222

Sep 2nd 2005

.

Unfortunately the code is not returning the correct number of rows
I was playing a bit around but never got the correct result

Sub countRows()
Dim lngRowsProject As Long
lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count  ' returns me 21 (column AH has 21 rows, but column AG has just 2)
lngRowsProject = Range("AG").CurrentRegion.Rows.Count  ' returns me 43 (the total numbers of rows in the spreadsheet are 43)
MsgBox "The number of rows is " & lngRowsProject
End Sub

Does anyone know how to change the code that it’s just returning the number of rows in column AG???

Thanks a lot in advance!

Martin

Re: Vba Code To Count Rows

Thanks guy, you’re hint’s were very useful!
As I need the number of rows as a variable for other macros I modified it to the following code

[vba]
Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("AG:AG")
countnonblank = Application.WorksheetFunction.CountA(myRange)
[/vba]

as a separate macro it works, so I hope it will work as a variable!

Cheers!

Martin