How to use shifttype.down

Aspose.Cells Product Family
1

I am trying to select a range by using shifttype.down. My top range is Range range = sheet1.Cells.CreateRange(“A10”, “H10”); and I would like to select all cells under that range. After that range is selected i suppose i will need to loop thru all those cells to apply formatting to each one. I need to know how to use shifttype.down in this situation. It seems to be the same as shift:=xlDown.

2

Hi,

Thanks for your posting and using Aspose.Cells.

For shifting down, kindly explain your requirements in more detail.

For applying style to range, you do not need to loop through all cells. Aspose.Cells provides ApplyStyle() method that can apply a style to entire range at once.

Please see the following code. It fills the entire range A10:H10 with yellow fill color. I have attached the output file created with this code for your reference.

C#


Workbook workbook = new Workbook();


Worksheet sheet1 = workbook.Worksheets[0];


Range range = sheet1.Cells.CreateRange(“A10”, “H10”);


//Apply style to entire range without looping

Style style = workbook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Yellow;


StyleFlag flag = new StyleFlag();

flag.CellShading = true;


range.ApplyStyle(style, flag);


workbook.Save(“output.xlsx”);

3

Range range = sheet1.Cells.CreateRange(“A10”, “H10”); is only the top row that I need formatted. I need to select every row under that to format. How do I select every cell under that top range?

4

VBA Example


Sub test()
Dim rng As Range

Sheets(“account activity”).Activate

Set rng = Range(“A10:H10”)
rng.Select

Range(Selection, Selection.End(xlDown)).Select

End Sub
5

Hi,

Thanks for your posting and considering Aspose.Cells.

If you want to select entire cells below this range, then create another range starting from A11 till H1048576. Since the maximum row is 1048576.

C#
Range downRange = sheet1.Cells.CreateRange(“A11”, “H1048576”);

6

I don’t want to select all the rows in the worksheet. I only want to select the rows that have data in the cells.

7

Hi,

Thanks for your posting and considering Aspose.Cells.

You can find the Maximum Row which contains some cell data using Worksheet.Cells.MaxDataRow property.

Please see the following code and the source xlsx file used inside it and output xlsx file generated by it for your reference. Here the maximum row with data is 33, so I am dynamically finding the cell H33 and creating a range from A7:H33 and then filling it with yellow color.

C#


Workbook workbook = new Workbook(“source.xlsx”);


Worksheet sheet1 = workbook.Worksheets[0];


//Get the last data cell with column H and MaxDataRow

Cell lastDataCellInColumnH = sheet1.Cells[sheet1.Cells.MaxDataRow, 7];


//Create range starting from A7 till ColumnH and MaxDataRow

Range range = sheet1.Cells.CreateRange(“A7”, lastDataCellInColumnH.Name);


//Apply style to entire range without looping

Style style = workbook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Yellow;


StyleFlag flag = new StyleFlag();

flag.CellShading = true;


range.ApplyStyle(style, flag);


workbook.Save(“output.xlsx”);