Row data and column data count using VBScript

Question

How using VBScript I can count the number of rows filled with data and how many column has a value for a particular row?

       Col1 Col2 Col3 ........ColN-1  ColN+1...ColN+2.......ColN+2

 Row1   A         B                             Null         Null
 Row2   1    2    Y                             .
 Row3        2                                  .
 Row4        P    Z 
  .                                             .
  .
  .
 RowN-2                                         .
 RowN-1                         T        L      Null.......Null
 RowN                     S
 RowN+1 Null ........(till the last column of the excel sheet that its version supprts.)

So here my required Loop iteration which i would use for my other logic is N for rows and for columns it would be N+1 Update

   Option Explicit

   Dim rg,CountBlank

   For C=0 to 10

    Set rg = Ob6.Range(Ob6.Columns(c),Ob6.Columns(c))
    CountBlank = objExcel1.Application.WorksheetFunction.CountBlank(rg)
    MsgBox("Column"&C": "&"Has"&(rg.rows.Count-CountBlank))
 Next

Thanks

nice question, but please show us any efforts you've made to try to do this. We can help you get past your stuck points, but no one is really inclined to write a full script for you. Also, do you want vbscript? or are you wanting VBA (inside an XL application)? — Scott Holtzman
– Scott Holtzman, Commented Dec 13, 2012 at 14:06
VbScript i do want...But i don't have any idea how to do so? So here i posted.Please help me out. — CodeLover
– CodeLover, Commented Dec 13, 2012 at 14:25
You can use the oXLAp.WorksheetFunction.CountA(). I can give you an example but as Scott suggested, give it a try and then post back with what you tried and where are you stuck and we will take it from there :) — Siddharth Rout
– Siddharth Rout, Commented Dec 13, 2012 at 14:29
I tried this one,But it can give only column data count. I don't have any idea about the row data count. — CodeLover
– CodeLover, Commented Dec 13, 2012 at 14:40

ManishChristian · Accepted Answer · 2017-10-25 18:43:07Z

1

Try this to get you started. It will show for each row the number of columns that have data, and for each column the number of rows that have data. You can then modify it to more suit your needs:

EDIT: Code updated to capture first row / column with only 1 column / row of data:

Option Explicit

Dim rg

'loop through columns
For C=0 to 10

    Set rg = Ob6.Columns(c)

    If objExcel1.Application.WorksheetFunction.CountA(rg) =1 Then

       Exit For

    End If

Next

MsgBox("Column" & C & " is first column with only 1 row")

'loop through rows
For C=0 to 10

    Set rg = Ob6.Rows(c)

    If objExcel1.Application.WorksheetFunction.CountA(rg) =1 Then

       Exit For

    End If

Next

MsgBox("Column" & C & " is first row with only 1 column")

edited Oct 25, 2017 at 18:43

ManishChristian

3,7944 gold badges25 silver badges52 bronze badges

answered Dec 13, 2012 at 15:18

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeLover Over a year ago

I just modified my case description for you.Hope it will be helpful for you.But don't delete the code you posted right now.

CodeLover Over a year ago

Perfect!! Thanks for your help! I know you are the only people,can unstuck me from anywhere.:-)

CodeLover Over a year ago

Can you help me in my below post,where i have pasted the code i am using,but its too slow. And also I want those two codes to be combined into a single one.Please help me there. That is a bottleneck for me still. stackoverflow.com/questions/13835758/…

Collectives™ on Stack Overflow

Row data and column data count using VBScript

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related