How to write Count of rows above the header

[nishant.pandey]

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.
Thanks

[pravallikapaluri]

Hi @nishant.pandey

Output

Hope it helps!!

[Anil_G (Anil Gorthi)]

@nishant.pandey

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

Cheers

[Parvathy (PS Parvathy)]

Hi @nishant.pandey

You can try this way:

Workflow:

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)

Output:

[sanjay3 (Sanjay)]

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