How to find last line of a column

Help Studio
, ,
1

Hi ,

I want to find out last cell no of particular column .

Eg. Excel contain 10,000 rows but in A column data is present upto 30 row only.
So I want 30 row no.

I don’t want to use for each loop to check line by line as number of rows may be very high.
Is there any other way to achieve this

2

Hi @TUSHAR_DIWASE ,
You can find by index
read all to get data,
data.rowcount → we have number of rows
Data.Columns.Count → we have last column as int
Convert.ToChar(Convert.ToUInt16("A"c)+3) → we have index of last cell

my sample :smile:

image
image613×666 18.5 KB

my code
Excel.xaml (10.9 KB)

regards,

3

Hi @TUSHAR_DIWASE

Use the below code Invoke VBA acitivty:

Sub FindLastCellInColumn()
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim columnToCheck As String
    
    ' Set the worksheet and column to check
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    columnToCheck = "A" ' Change "A" to the column you want to check
    
    ' Find the last used row in the specified column
    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
    
    ' Display the result in the Immediate Window (Ctrl + G to open the Immediate Window)
    Debug.Print "Last used cell in column " & columnToCheck & ": " & lastRow
End Sub

Paste the above code in a text file and pass the path in Invoke VBA. The Method name will be FindLastCellInColumn.

Regards

4

@TUSHAR_DIWASE

can you try this once

dt1.AsEnumerable.Select(Function(a) a(0).ToString).ToList.where(Function(a) not a.ToString.Equals("")).count

to get the value try this once

dt1.AsEnumerable.Select(Function(a) a(1).ToString).ToList.where(Function(a) not a.ToString.Equals("")).last
5

Let me reframe,
image
as per image , i want output as last row no of B column. i.e 5.

6

Use Read Column activity and you will get collection then get a count of that collection that will your last row value then append it with your column
collection.Count.ToString
my run

image
image687×583 18.7 KB

my output
image

my code
Excel.xaml (10.6 KB)

regards,