CODEWITHSUNDEEP
c#excel-interop
Zkai
Zkai

Reputation: 211

Delete before last column in excel

This code will delete the last column. It's working fine: 

Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);      
last.EntireColumn.Delete(Missing.Value);

However, if I combine the code above with the code below:

Microsoft.Office.Interop.Excel.Range last1 = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
last1.EntireColumn.Delete(Missing.Value);

..the codes combined are going to delete 1 column instead of two. Why?

My logic is that If I delete the last column and then try to get the new LastCell again and delete, It will delete 2 but that's not happening for some reason.

Upvotes: 1

Views: 500

Answers (2)

41686d6564
41686d6564

Reputation: 19661

That's because the value of xlCellTypeLastCell will stay the same until the workbook is closed and reopened (it can be reset programmatically. See the "update" section below). You can make sure of that by checking the value of last1.Column, you'll notice that it's the same as last.Column.

This behavior can also be observed manually in Excel itself. Try deleting a column manually in Excel and then press Ctrl+End. You will notice that the activated cell is still in the empty column. What xlCellTypeLastCell does is emulate the behavior of Ctrl+End.

Instead, you can use a different method that gets the last used column. Something like this should work:

First, add this line on top of the file to simplify the namespace:

using MSOffice = Microsoft.Office.Interop;

Then you can do something like this:

MSOffice.Excel.Range last = 
    xlWorkSheet.Cells.Find(What: "*", After: xlWorkSheet.Cells[1, 1], 
                           SearchOrder: MSOffice.Excel.XlSearchOrder.xlByColumns, 
                           SearchDirection: MSOffice.Excel.XlSearchDirection.xlPrevious);
last.EntireColumn.Delete();

MSOffice.Excel.Range last1 =
    xlWorkSheet.Cells.Find(What: "*", After: xlWorkSheet.Cells[1, 1],
                           SearchOrder: MSOffice.Excel.XlSearchOrder.xlByColumns,
                           SearchDirection: MSOffice.Excel.XlSearchDirection.xlPrevious);
last1.EntireColumn.Delete();

Note: Because newer versions of C# (Since C# 4.0) support optional parameters, you don't have to use Missing.Value unless you're using a version prior to C# 4.0.

Update:

Looks like there's a way to reset the value of the last cell retrieved by the first method, and that's by calling Worksheet.UsedRange. Therefore, you can still use your method if you prefer to, but you'd have to reset the value each time:

MSOffice.Excel.Range last = 
    xlWorkSheet.Cells.SpecialCells(MSOffice.Excel.XlCellType.xlCellTypeLastCell);
last.EntireColumn.Delete();

var dummy = xlWorkSheet.UsedRange;

MSOffice.Excel.Range last1 = 
    xlWorkSheet.Cells.SpecialCells(MSOffice.Excel.XlCellType.xlCellTypeLastCell);
last1.EntireColumn.Delete();

Upvotes: 2

Snailtamer
Snailtamer

Reputation: 21

I think you should just use For Loop do delete how many columns you want. Something like : 

for (int i = 0; i < 2; i++)
{
  last.EntireColumn.Delete(Missing.Value);
}

didnt tested this yet but hope this works :) You get the idea

Upvotes: -1

Related Questions