none
Counting Number of Rows in a Table

    Question

  • Hi

    I have a table in my application which can have up to 3000 entries. I filter this , copy the filetered data (say 500 rows) to a holding space, clear the original 3000 entries , then copy the 500 entries back to the sheet. All this works fine.

    Later in the process I come back and need to do some further processing. At that point I try to determine the number of rows in the sheet using this command.

    intNoOfRows = wshAnalysisSheet.Cells(Rows.count, 1).End(xlUp).row

    I have used this command successfully on many different occassions.

    The issue here seems to be that because it was a table , it maintains that format and 'keeps' the 2500 empty rows. So that when i try to get the row count i get 3000 instead of 500.

    I tried to convert the table to range , get the count and convert back to table - it is a bit clumsy.

    I  know i can also loop to get to the first balnk row but that is not very elegant.

    I have tried various thing (e.g. specialcells(visiblecells) but to no avail.

    any thoughts please?

    By the way - i have 4 table that I need to process in this way so a neat soultion would be preferable!

    thanks

    Peter

     

    Thursday, November 9, 2017 10:29 AM

Answers

  • You can use

    intNoOfRows = wshAnalysisSheet.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Thursday, November 9, 2017 1:15 PM
    Thursday, November 9, 2017 11:29 AM

All replies

  • You can use

    intNoOfRows = wshAnalysisSheet.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Thursday, November 9, 2017 1:15 PM
    Thursday, November 9, 2017 11:29 AM
  • great Hans - works perfectly!

    thank you.

    Thursday, November 9, 2017 1:15 PM