CODEWITHSUNDEEP
excelvbscript
CodeLover
CodeLover

Reputation: 1074

Row data and column data count using VBScript

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

Upvotes: 1

Views: 11049

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

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")

Upvotes: 1

Related Questions