Delete a Row in Excel Spreadsheet

[Karl Richards]

I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[Denis Dougall]

Check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp . ANd you are looking for the clear cell info.

Cheers,

Denis
I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[Karl Richards]

I want to delete the entire row... I have a range of 1280 rows that I want to remove the duplicate rows... I'm not sure I understand how the "clear cell info" is going to help with that.
Check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp . ANd you are looking for the clear cell info.

Cheers,

Denis
I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[Denis Dougall]

You can then iterate through and delete the empty cells/rows and preserve your formulae. However here is some delete row code.


Range(x,y ).EntireRow.Delete
Range(Cells(1, 1), Cells(99, ubound var)).EntireColumn.Delete

Denis


I want to delete the entire row... I have a range of 1280 rows that I want to remove the duplicate rows... I'm not sure I understand how the "clear cell info" is going to help with that.
Check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp . ANd you are looking for the clear cell info.

Cheers,

Denis
I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[Karl Richards]

I've tried that, but I keep getting "'object' does not contain a definition for 'EntireRow'".
You can then iterate through and delete the empty cells/rows and preserve your formulae. However here is some delete row code.


Range(x,y ).EntireRow.Delete
Range(Cells(1, 1), Cells(99, ubound var)).EntireColumn.Delete

Denis


I want to delete the entire row... I have a range of 1280 rows that I want to remove the duplicate rows... I'm not sure I understand how the "clear cell info" is going to help with that.
Check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp . ANd you are looking for the clear cell info.

Cheers,

Denis
I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[carion1]

If you have the range defined then use ADO.

--

Derek Davis
(e-mail address removed)
I've tried that, but I keep getting "'object' does not contain a definition for 'EntireRow'".
You can then iterate through and delete the empty cells/rows and preserve your formulae. However here is some delete row code.


Range(x,y ).EntireRow.Delete
Range(Cells(1, 1), Cells(99, ubound var)).EntireColumn.Delete

Denis


I want to delete the entire row... I have a range of 1280 rows that I want to remove the duplicate rows... I'm not sure I understand how the "clear cell info" is going to help with that.
Check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp . ANd you are looking for the clear cell info.

Cheers,

Denis
I am attempting to delete duplicate rows in a spreadsheet using the Excel object.

Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it.

What I have so far is:
Excel.Range range = oSheet.get_Range("A1", Type.Missing);
range = range.get_End(Excel.XlDirection.xlDown);
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

range = oSheet.get_Range("A1",downAddress);
object[,] values = (object[,])range.Value2;

for (int i = 2; i < values.GetLength(0); i++)
{
if (values[i-1,1].ToString() == values[i,1].ToString())
{
// This is where I want to delete the row.
}

any help would be greatly appreciated.

 

[kids_pro]

Using Excel Macro record to record your action while you delete the row.
One the row is delete check how VBA is code to fullfill this task.

Then try the samething from your C#.