the only way i could get it working was to use deleterange.
here is the code i used
the only problem here is im trying to use the clearrange method to remove the formatting from the range before deleting it, doesnt sem to work. as u can see there is a yellow format on the first column on all the rows, which also needs to cleared before removing the rows. can u help as to why that is not working?
If objcells.Count > 0 Then
Dim preRow As Int64 = -1
'Console.Write(backspace & "Row " & Format((inti + 1), "###########0") & " of " & Format(objcells.Count, "###########0"))
Dim objCell As Cell = objcells(objcells.Count - 1)
If objCell.Type = CellValueType.IsNull Then
preRow = objCell.Row + 1
End If
For i As Int32 = objcells.Count - 1 To 0 Step -1
Console.Write(backspace & "Row " & Format((i + 1), "###########0") & " of " & Format(objcells.Count, "###########0"))
objCell = objcells(i)
If objCell.Type <> CellValueType.IsNull Then
If (preRow = -1) Then
preRow = objCell.Row
End If
If (preRow <> objCell.Row) Then
If (preRow - objCell.Row > 1) Then
objcells.ClearRange((objCell.Row + 1), 0, CInt(objCell.Row + 1) + CInt(preRow - objCell.Row - 1) - 1, objcells.MaxDataColumn + 1)
DelRows.Add(objCell.Row + 1 & "**" & preRow - objCell.Row - 1)
End If
End If
preRow = objCell.Row
End If
Next
If (preRow <> 0) Then
objcells.DeleteRows(0, preRow)
End If
End If
For Each strdel As String In DelRows
Dim src As String() = Split(strdel, "**")
objcells.DeleteRange((src(0)), 0, CInt(src(0)) + CInt(src(1)) - 1, objcells.MaxDataColumn + 1, ShiftType.Up)
Next