Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Vba Code To Count Rows

  1. #1
    Join Date
    28th July 2006
    Posts
    75

    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 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
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Vba Code To Count Rows

    Hi Martin,

    When I filter Column AG and there are only 2 meeting the criteria, then your code

    VB:
    lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count 
    
    
    gives me the correct answer of 2.

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    Re: Vba Code To Count Rows

    Another method:
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th July 2006
    Posts
    75

    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

    VB:
    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!

    Cheers!

    Martin

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Possible Answers

  1. Count Hidden Rows
    By bigsteve in forum EXCEL HELP
    Replies: 5
    Last Post: March 21st, 2007, 07:56
  2. Add a Count Rows Column
    By mvanlear in forum EXCEL HELP
    Replies: 1
    Last Post: August 3rd, 2006, 12:56
  3. Count the rows between occurrences
    By dkenebre in forum EXCEL HELP
    Replies: 1
    Last Post: August 11th, 2005, 16:50
  4. [Solved] VBA: Count Function or Count Code
    By ppdoug in forum EXCEL HELP
    Replies: 4
    Last Post: November 21st, 2003, 02:01
  5. count 15 rows down
    By alch in forum EXCEL HELP
    Replies: 1
    Last Post: May 1st, 2003, 03:30

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno