How to write Count of rows above the header

Hi, I have a data table with multiple rows and i need to write the total count of Rows above my header. Also in A1 I have to write “H”
and in B1 Total count.

Hi @nishant.pandey


Hope it helps!!


Just read the excel from A2

Then use write cell with A1 as cell and value as H

And anither write cell with B2 as cell and value as dt.Rowcount.ToString


Hi @nishant.pandey

You can try this way:


Pass this code in a text file and save it within project folder and give the path in Code File Path of Text file in Invoke VBA and Entry Method Name as WriteHeaderAndTotalCount.

Sub WriteHeaderAndTotalCount()
    ' Declare variables
    Dim ws As Worksheet
    Dim totalCount As Integer
    ' Set reference to the active sheet (you can replace this with a specific sheet if needed)
    Set ws = ActiveSheet
    ' Calculate total count
    totalCount = ws.UsedRange.Rows.Count
    ' Write "H" to cell A1
    ws.Range("A1").Value = "H"
    ' Write the total count to cell B1
    ws.Range("B1").Value = totalCount
    ws.Range("C1").Value = totalCount
    ws.Range("D1").Value = totalCount
    ws.Range("E1").Value = totalCount
    ws.Range("F1").Value = totalCount
End Sub

Text File:
WriteHeaderAndTotalCount.txt (634 Bytes)


Hi @nishant.pandey

Excel Application Scope (FilePath: “YourExcelFilePath.xlsx”)
Read Range (Range: “”, AddHeaders: unchecked, Output: dt)

Assign (rowCount = dt.Rows.Count)

Invoke Method (TargetObject: dt.Rows, MethodName: “InsertAt”)
Parameters: (Direction: In, Type: DataRow, Value: dt.NewRow)
(Direction: In, Type: Int32, Value: 0)

Assign (dt.Rows(0)(0) = “H”)
Assign (dt.Rows(0)(1) = rowCount.ToString)

Write Range (Range: “A1”, DataTable: dt)

Hope this helps