Clear all cells in a specific range

Aspose.Cells Product Family
1

Hello aspose team.


I want to clear all cells of specific worksheets which they have row index < 10.
and here is my code :

Sub HideAllUnderTen(ByRef WS As Worksheet)
For Each CL As Cell In WS.Cells
If CL IsNot Nothing Then
If CL.Row < 10 Then
CL.Value = “”
End If
End If
Next

For i As Integer = 0 To WS.Pictures.Count - 1
If WS.Pictures(i).LowerRightRow <= 10 Then
WS.Pictures(i).IsHidden = True
End If
Next

End Sub

But this code will not hide all objects, like bold line and …

Would you help me and show me how should I change this code.

and I attached the file. the top table is not deleted yet.

Thanks
2

Hi,

I think you may use Cells.DeleteRows() method or Cells.DeleteRange() method for your requirement. See the following sample code segment:


Dim sheet As Worksheet = book.Worksheets(0)
Dim cells As Cells = sheet.Cells

cells.DeleteRows(0, 10)

'Or you may use DeleteRange accordingly
'cells.DeleteRange(0, 0, 9, cells.MaxColumn, ShiftType.Up);


book.Save(“e:\test\output.xls”)


Thank you.

3

Hi, Thanks for your attention.

I used this code, it worked,
Cells.DeleteRows(0,10) Will delete all cells under 10 and that’s what I want, but this will shift other
cells up, Cells.DeleteRange with ShiftType.Up option will act approximately the same but not deleting
pictures.
And I also used Cells.InsertRow inorder to insert empty rows at worksheet’s first.
But if I use this code :
cells.InsertRows(0, 10, True)
this inserts 10 rows but they won’t be shown in worksheet ( Problem1.xls )
because the MinRow Property now is set to 9, and it’s readonly, if I could change it to 0
the problem would be solved.

cells.InsertRows(1, 10, True)
a gap of 10 empty rows is inserted between first row and it’s next ( Problem2.xls)

I want to insert 10 rows before first row, or if it’s not possibe move first row to another index.




Thanks a lot.
4

Hi,

I think for your need, you may use Cells.InsertRows() method, see the following code for your requirement:

Dim sheet As Worksheet = book.Worksheets(0)
Dim cells As Cells = sheet.Cells

cells.InsertRows(10, 10)
cells.DeleteRows(0, 10)

book.Save(“e:\test\output.xls”)

5

Thanks a lot again for your attention.


I added this code also :
cells.ClearFormats(0, 0, 9, cells.MaxColumn)

because in some worksheets some thing like table was in first 10 rows and this code solved the problem.

my final code is :

Sub HideAllUnderTen(ByRef WS As Worksheet)
Dim cells As Cells = WS.Cells
cells.DeleteRows(0, 9)
cells.InsertRows(1, 1, False)
cells.CopyRow(cells, 0, 1)
cells.InsertRows(1, 9, False)
cells.ClearContents(0, 0, 9, cells.MaxColumn)
cells.ClearFormats(0, 0, 9, cells.MaxColumn)
End Sub

Thanks