When I filter Column AG and there are only 2 meeting the criteria, then your code
gives me the correct answer of 2.Code:lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count
Iím looking for a VBA code which counts the number of rows which contains data.
I was looking in the forum and found this thread.
Unfortunately the code is not returning the correct number of rows
I was playing a bit around but never got the correct result
Code: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!
Code:Sub TestButton1() NonBlankRange ("Sheet2!A:A") End Sub Sub NonBlankRange(sRange As String) Dim countNonBlank As Integer, myRange As Range Set myRange = Range(sRange) countNonBlank = Application.WorksheetFunction.CountA(myRange) MsgBox "Number of Non-Blank Cells: " & countNonBlank, , sRange End Sub
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
Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("AG:AG")
countnonblank = Application.WorksheetFunction.CountA(myRange)
as a separate macro it works, so I hope it will work as a variable!
There are currently 1 users browsing this thread. (0 members and 1 guests)