We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Find the first occurance of cells with no value in it.

Thread ID:





76318 Sep 1,2008 09:58 PM UTC Sep 2,2008 01:44 PM UTC ASP.NET Web Forms (Classic) 1
Tags: XlsIO
Vidya Jayakumar
Asked On September 1, 2008 09:58 PM UTC


I want to know if you have any method which will find the last cell with no value in a particular column.
Take for eg. The file i have attached. I want to know if you have a method, if i give a range Sheet1.Range(1,2) which means
start searching from first row and second column and the whole column.Stop when you find no value in a cell.Right now if i give this range
it searches only in first row and second column, basically one cell in that column. Rather i want the search to be the whole second column?
I cannot give a end row index because i do not know.

What i am currently doing is if only i know the range to look for
dim rangetoFindcellswithvalues as Irange = sheet1.range(7,1,20,1)
i.e. start searching in second row & first column, end in 20th row & first column itself
Dim countertopass As Int32 = startRow
Dim startrow as int32 = 7
Dim cellswithvalues As Int32 = 0
For Each cell As IRange In rangetoFindcellswithvalues.Rows()
countertopass += 1
If cell.DisplayText = "" Then
Exit For
End If
cellswithvalues = countertopass - 1
If cellswithvalues = startRow Then
Exit Function
End If
Return cellswithvalues

I cannot apply this method if i do not know the end row index to stop searching.

QUESTION 1) Can you tell me how do i do find a cell with no values in it if i provide just the whole column range and a row to start???

I tried Dim sa As String = sheet1.UsedRange.AddressLocal
It retunrs a string of A1:G132 with a excel file which has 6 columns and 132 rows.
I copied the same file to have 100 rows, i deleted rest of the rows.
Dim sa As String = sheet1.UsedRange.AddressLocal still shows A1:G132 when it should show A1:A100.
QUESTION 2) why is this happening?

I can very fine give a outermost bound for end row index of 1000. but i feel there should be a better
way to handle this situation

Thank you,


Geetha M [Syncfusion]
Replied On September 2, 2008 01:44 PM UTC

Hi Vidya,

Thank you for your interest in Syncfusion products.

I have created a simple sample to find the first empty cell in the workbook. This takes the row and column number to start with and gets the end range using the UsedRange method of the worksheet. By iterating cell by cell using the above information, once an empty cell is reached it is displayed in the message box, along with the row and column numbers.

Please download the sample from the below link and let me know if it helps you.



This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

or the page will be automatically redirected to sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon