Hi,
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
Next
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,
Vidya
frxsync_73e6862b.zip