How to get the last column with data in an excel

[Ajith209]

Hi Team,

I have an excel file which could have N numbers of columns and rows. My requirement is to get the last row letter with data. for eg: if data is there from A to F column i just need F. Please let me know if anyone has a solution for this.

[mkankatala (Mahesh Kankatala)]

Hi @Ajith209

→ Use read range workbook activity to read the excel and store in a datatable called dt.
→ Then use the below one to get the column count

Columncount = dt.ColumnCount

→ Then you can write range workbook activity to write the last column to excel.

Hope it helps!!

[rlgandu (Rajyalakshmi Gandu)]

@Ajith209

If you want Last column last row data please use this query

LastRowdata=Dt.Rows(Dt.Rows.Count - 1)(Dt.Columns.Count - 1).ToString()

If you want last Column all rows data please use this query

outputArray = (From row In inputDataTable.AsEnumerable()
               Select row(inputDataTable.Columns.Count - 1).ToString()).ToArray()

[Umadevi_Sanjeevi]

@Ajith209

I have an excel file which could ha

Hi @Ajith209 ,

1. Read excel file DT

outStr = DT.AsEnumerable().Select(Function(y) y(DT.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName.ToString).reverse(0)).ToString).Reverse(0)

Thanks!

[Ajith209]

This gives data not the column letter like F

[Nawazish_Ahmad]

Hi @Ajith209

Try Below expression for getting last column and last cell of a DataTable.

DT.AsEnumerable.Last()(DT.Columns.Count - 1).ToString()

Hope it will helps you
Cheers!!

[Ajith209]

does not give the last cells. It gives the last column value

[Ajith209]

gives the last column value not the last column cell Like F. it gives the values inside F

[Anil_G (Anil Gorthi)]

@Ajith209

You can use this inside use excel

requiredstring = system.Text.RegularExpressions.Regex.Match(Excel.Sheet("SheetName").FullRangeValue.Split(":"c).Last,"[A-Z]*").Value

Cheers

[Nawazish_Ahmad]

@Ajith209

Its working.

[rlgandu (Rajyalakshmi Gandu)]

@Ajith209

gives the last column value not the last column cell Like F. it gives the values inside F

@Ajith209

outputColumnLetter = Convert.ToChar(65 + (inputDataTable.Columns.Count - 1)).ToString()

I have column upto C so it gives C

[Nawazish_Ahmad]

@Ajith209

IF you want to get the place of the last Column

Try this:

ChrW(AscW("A") + (DT1.Columns.Count - 1)).ToString()

It will return F in your case i.e Last column place

Cheers!!

[sreejith.ss]

Hi @Ajith209,

Please try this

yourstrvariable=yourdatatable.rows(yourdatatable.rowcount-1).item(yourdatatable.columncount-1).tostring

It will give the excel last row last column value in a str variable

Regards
Sreejith S S