1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 07 VBA - Counting all Visible rows after a filter was applied.

Discussion in 'Business Applications' started by TMeerkat, Jun 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. TMeerkat

    TMeerkat Thread Starter

    Joined:
    Jun 23, 2011
    Messages:
    12
    Hi there

    I would like to count the visible rows of a table (tblProbRep) that has been filterred and then to select the last cell in column H to copy to another sheet.
    I know the code for all except how to count only visible rows in vba .
    Please can someone assist.

    Thanks you
    TMeerkat
     
  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    251
    TMeekat,

    Here's a bit of code that I use to count the "Visible" rows.


    Code:
    Dim Myval as Intiger
    Myval = .Range("c2:c" & lrow).SpecialCells(xlCellTypeVisible).Count
    
     
  3. TMeerkat

    TMeerkat Thread Starter

    Joined:
    Jun 23, 2011
    Messages:
    12
    Hi there

    When I use this code
    Dim Myval As Integer
    Myval = Sheets("ProblemReporting").Range("tblProbRep").SpecialCells(xlCellTypeVisible).Count

    MsgBox "This is " & Myval, vbOKCancel

    it gives me 15 when there is only 6 rows and only 3 rows are visible.
    Why is that?

    Did I do something wrong?
     
  4. AD_Taylor

    AD_Taylor

    Joined:
    Jan 16, 2010
    Messages:
    93
    Is the range "tblProbRep" 5 columns wide?

    If so you because you are counting cells that are visible you have 3 rows by 5 columns = 15 cells visible?

    You could try:
    Code:
    Myval = Sheets("ProblemReporting").Range("tblProbRep").[B]Columns(1).[/B]SpecialCells(xlCellTypeVisible).Count
    This should narrow the range down to just the first column so that it will only count the rows.

    Hope this helps!
    Adam
     
  5. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    251
    Adam,

    Thanks for the answer. I learned something from this.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1004804