Deleting a range of rows

Question

Hi,If I want to delete the header row (say it is the first row) of a range of an excel sheet, I can do it by following C# code: usedRange[1, 1, 1, usedRange.Columns.Length].Clear(ExcelMoveDirection.MoveUp)assuming usedRange is the range on which I want to do the clear operation. Here I have a problem, say my range is A1 to C7 and if execute the above line of code, I'm able to remove the first row, but as I'm doing a MoveUp​, I'm still getting the new range as A1 to C7 and 7th row contains empty cells, but still it is included in the usedRange. So, I tried applying Trim() like usedRange.Trim(). Here I was able to remove that extra 7th row but because of Trim(), if I have all the cells of the 'C' column as empty, it is also trimming the 'C' column along with extra 7th row. I don't want the empty 'C' column to be deleted. So is there a way to do that?Thanks,Nivas.

Ramya Sivakumar · Answer

Hi Nivas,Greetings from Syncfusion.We can reproduce the reported scenario at our end and
validating it currently. We will share the validation details on July 15, 2022.As for now, we suggest using the DeleteRow() option to delete the rows. Please use the following code
snippet and let us know if it helps.Code snippet:

worksheet.DeleteRow(1);

Regards,Ramya

Nivas · Answer

Hi Ramya, My actual intention is to subtract/remove a desired range from a given range. Thanks for the info but I want to know is there any operation on a given range not on a worksheet.Thanks,Nivas.

Ramya Sivakumar · Answer

Hi Nivas,We can remove the range of worksheet using the Clear and
MoveTo options. But the used range is not updated after using these options. As
mentioned earlier we have logged the bug task for this issue.So once we provided the fix you can use the following code snippet and remove the range.Code
snippet:

IRange source = worksheet.Range["A2:C7"];
  IRange destination = worksheet.Range["A1"];
  source.MoveTo(destination);

Please
refer the following link to know more about the MoveTo() option.https://help.syncfusion.com/file-formats/xlsio/worksheet-cells-manipulation#copy-or-move-a-rangeWe have confirmed the issue as UsedRange is not updated while clearing
worksheet range with move option and logged a defect report. We will
include the fix for this issue in our upcoming weekly NuGet release scheduled
for July 26, 2022. You can track the
status of the defect report through the following feedback link.Track Status: https://www.syncfusion.com/feedback/36350/usedrange-is-not-updated-while-clearing-worksheet-range-with-move-optionWe will let you know once the new package version is
available on nuget.org.Disclaimer: Inclusion of this solution in the weekly release may
change due to other factors including but not limited to QA checks and works
reprioritization.Regards,Ramya

Nivas · Answer

Thanks for the update.

Ramya Sivakumar · Answer

Hi Nivas,You’re welcome. We will get back to you on July 26, 2022.Regards,Ramya

Ramya Sivakumar · Answer

Hi Nivas,We appreciate your
patience.We have included the fix to resolve the issue UsedRange is not updated while clearing
worksheet range with move option in our
weekly NuGet release version 20.2.0.40. Kindly upgrade your Syncfusion
packages to this new 20.2.0.40 version and let us know if the issue is
resolved.Regards,Ramya

Nivas · Answer

Hi Ramya,Thanks for the update. I've tested it. But still I'm getting the extra range of empty cells. For example, consider a used range with a single column('A') i.e. A1:A12 having some text in 1st, 5th and 12th cells. Now, when I try to do  usedRange[1, 1, 1, usedRange.Columns.Length].Clear(ExcelMoveDirection.MoveUp), there are two issues with this. One is, it clears the first column and then the next non-empty cell (which is the 5th cell previously) occupies the first position(A1) which is not correct. The A1 cell should be occupied by the next cell A2(even if it is an empty one) instead of A5. The other one is, even After using ' .Clear(ExcelMoveDirection.MoveUp)', I'm still getting the range as A1:A12 which shouldn't be the case.Please look into these issues.Regards,Nivas.

Ramya Sivakumar · Answer

Hi Nivas,We have checked the issue in the latest NuGet, in which the
issue is resolved. So, kindly delete the bin and obj folder from the sample and
upgrade your Syncfusion packages to this new 20.2.0.40
version and let us know if the issue is resolved.Please find the Input and Output screenshots.Input:Output: Used Range - 'Sheet1'!A1:A11Regards,Ramya

Nivas · Answer

Hi Ramya,Like you suggested, I deleted bin, obj folders and also updated the package to  20.2.0.43(latest stable release) and tested it again. The cells are moving up as expected but still I'm getting the same address(A1:A12) instead of updated address(A1:A11). If possible, can you please provide a sample solution for the same to look into.Thanks,Nivas.

Ramya Sivakumar · Answer

Hi Nivas,We have prepared the sample to Clear the range with the MoveUp
option. kindly try this and let us know if the solution helps. The complete
sample can be downloaded from the below link.Sample link -https://www.syncfusion.com/downloads/support/directtrac/general/ze/F-176166670799962Please find the screenshots for the UsedRange of before
Clear and after Clear options.Before Clear:After Clear:Regards,Ramya