start find value from second row

[sitizalekoh]

Hi. I need spire.xls to read start from second row. if it found the value, and it will delete entire row. so far, my coding was like this. Please help me to identify where is the problem. because it start find from first row.

Code: Select all


        Dim lastFilledRow As Integer = worksheet.LastRow
        Dim row As Integer


        For row = 2 To lastFilledRow
            If worksheet.Range.Value = "Material Number" Then
                worksheet.Range.EntireRow.ClearContents()
            End If

            'Dim ranges() As CellRange = worksheet.FindAllString("Material Number" & worksheet.Range.Value, False, False)
            'For Each range As CellRange In ranges
            '    range.EntireRow.ClearContents()
            'Next range

        Next



[Lisa.Li]

Hello,

Thanks for your inquiry.
In the loop, you should add the corresponding parameter to set the start row. Please refer to the following sample code to achieve your demand. If there is any question, please provide your testing file to further look into it. You could send it via email (support@e-iceblue.com).

Code: Select all

Dim workbook As New Workbook()
workbook.LoadFromFile("test.xlsx")
'get the first sheet
Dim workbookSheet As Worksheet = workbook.Worksheets(0)
'start from second row
For row As Integer = 2 To workbookSheet.LastRow
    'travel all columns
    For column As Integer = 1 To workbookSheet.LastColumn
        Dim cellsRange() As CellRange = workbookSheet.Range(row, column).FindAllString("Material Number", False, False)
        For Each cell As CellRange In cellsRange
            cell.EntireRow.ClearContents()
        Next cell
    Next column
Next row
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013)


Sincerely,
Lisa
E-iceblue support team

[sitizalekoh]

Hi Lisa. It doesn't work. okay, what if I want to do like this,; if any row or cell are same with first row, then that one that same will be delete but not the first row.
because my data is like this;

---------------------------------------------------------------------
material number | Type | Shipping Number | Notes |
---------------------------------------------------------------------
111 | 11 |1111111111111111|1111111|
111 | 11 |1111111111111111|1111111|
111 | 11 |1111111111111111|1111111|
111 | 11 |1111111111111111|1111111|
material number| Type | Shipping number | Notes |
11111111111111|11111 | 1111111111111111 | hsshs |
11111111111111|11111 | 1111111111111111 | hsshs |
11111111111111|11111 | 1111111111111111 | hsshs |
11111111111111|11111 | 1111111111111111 | hsshs |


so I want to eliminates the title that same with the first row.

[sitizalekoh]

It's working now. thanks a lot. this is the coding that Lisa replied to my email.

Code: Select all

   Dim cellRanges() As CellRange = workbookSheet.FindAllString("Material Number", False, False)
        For Each cell As CellRange In cellRanges
            If cell.Row <> 1 Then
                cell.EntireRow.ClearContents()
            End If
        Next cell


[Lisa.Li]

Hello,

Thanks for your quick response.
If you encounter any issue related to our products in the future, just feel free to contact us. Wish you all the best!

Sincerely,
Lisa
E-iceblue support team