Clearing Row data for a given range

[Santhosh Pasupuleti]

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[JLGWhiz]

Try:

rj.Value = ""

 

[Santhosh Pasupuleti]

Thanks for the quick reply. There is no property called Value for range and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

Try:

rj.Value = ""

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Dave Peterson]

If your range to clear has merged cells, you can see this problem with
..clearcontents.

Excel's range object does have a .value property.

This would work in Excel's VBA:
dim rj as range
set rj = activesheet.range("a1:b9")
rj.value = ""

I don't know how to translate it into the language you're using, though.

Thanks for the quick reply. There is no property called Value for range and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

Try:

rj.Value = ""

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[JLGWhiz]

If you have no formulas in the range you are attempting to clear, then
ClearContents should work. If you do have formulas in the range, the
ClearContents will also clear the formulas and you indicated that you only
wanted to clear the data. To just clear the data, you would have to make
the Value of the cells in the range equal a null string value. Like Dave
stated. I am not really sure about the code you are using. It appears that
you are trying to Set amd object variable but with the Set keyword. The way
it is written, if it did anything it would create a variable for a massive
array, I think. If you are trying to create rj as a range then you would
have to use the Set statement. Then the range would have a value property
and you could use the null string to clear the data in the range.

Thanks for the quick reply. There is no property called Value for range
and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

Try:

rj.Value = ""


"Santhosh Pasupuleti" <Santhosh (e-mail address removed)>
wrote
in message news:[email protected]...

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please
tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Santhosh Pasupuleti]

Dave,

Thanks for the reply.

I dont have any merged cells. I have data in a bout 80 rows and 12 columns.
Straight forward. I dont understand why clear contents doesnt work and I am
programming with excel object using C#. In C# Excel Range object there is no
value property. How ever I see Value2 property. I tried that option as
well..no luck. When I try to use macro...it works fine.

Thanks and Regards,
Santhosh Kumar.P

If your range to clear has merged cells, you can see this problem with
..clearcontents.

Excel's range object does have a .value property.

This would work in Excel's VBA:
dim rj as range
set rj = activesheet.range("a1:b9")
rj.value = ""

I don't know how to translate it into the language you're using, though.

Thanks for the quick reply. There is no property called Value for range and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

Try:

rj.Value = ""


in message Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Dave Peterson]

I don't have a guess. I don't know anything about C#.

If you hardcode the address, does it work?

ws.Rows.get_Range("A1", "Z3").ClearContents();

if yes, then maybe it's a syntax error--or maybe dss.Tables[0].Rows.Count isn't
what you think it should be. (Could it be 0???)

Dave,

Thanks for the reply.

I dont have any merged cells. I have data in a bout 80 rows and 12 columns.
Straight forward. I dont understand why clear contents doesnt work and I am
programming with excel object using C#. In C# Excel Range object there is no
value property. How ever I see Value2 property. I tried that option as
well..no luck. When I try to use macro...it works fine.

Thanks and Regards,
Santhosh Kumar.P

If your range to clear has merged cells, you can see this problem with
..clearcontents.

Excel's range object does have a .value property.

This would work in Excel's VBA:
dim rj as range
set rj = activesheet.range("a1:b9")
rj.value = ""

I don't know how to translate it into the language you're using, though.

Thanks for the quick reply. There is no property called Value for range and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

:

Try:

rj.Value = ""


in message Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Santhosh Pasupuleti]

I really appreciate you guys spending time on this. I will make it simple. I
tried as suggested by you and Dave. I dont have any formulas in the sheet but
this data is referenced from different sheet using formulas. Please find the
code below:
As stated in MSDN, the only difference between this property and the Value
property is that the Value2 property doesn’t use the Currency and Date data
types. Since the object doesnt show Value property, I decided to use Value2.

ws = (Excel._Worksheet)wb.ActiveSheet;
Excel.Range rj;
rj = ws.Rows.get_Range("A1", "Z100");
rj.Select();
rj.Value2 = "";
wb.Save();

Still it doesnt work.

Thanks and Regards,
Santhosh Kumar.P
-----------------------------------------------------------------------------------------------------

If you have no formulas in the range you are attempting to clear, then
ClearContents should work. If you do have formulas in the range, the
ClearContents will also clear the formulas and you indicated that you only
wanted to clear the data. To just clear the data, you would have to make
the Value of the cells in the range equal a null string value. Like Dave
stated. I am not really sure about the code you are using. It appears that
you are trying to Set amd object variable but with the Set keyword. The way
it is written, if it did anything it would create a variable for a massive
array, I think. If you are trying to create rj as a range then you would
have to use the Set statement. Then the range would have a value property
and you could use the null string to clear the data in the range.

Thanks for the quick reply. There is no property called Value for range
and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

Try:

rj.Value = ""


"Santhosh Pasupuleti" <Santhosh (e-mail address removed)>
wrote
in message Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please
tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Chip Pearson]

The following snippet works for me (XL 2007, C# 2008)

Excel.Range R1;
Excel.Range R2;
Excel.Range R3;
Excel.Worksheet WS;
int N = 10;
WS = (Excel.Worksheet)XLApp.ActiveWorkbook.Worksheets[1];
R1 = WS.get_Range("A1", "A10");
R2 = WS.get_Range("B1", "B10");
R3 = WS.get_Range("C1", "C" + N.ToString());
// Clear R1
R1.ClearContents();
// Clear R2
foreach (Excel.Range R in R2.Cells)
{
R.ClearContents();
}
// Clear R3
foreach (Excel.Range R in R3.Cells)
{
R.set_Value(Missing.Value, "");
}


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 4 Sep 2009 12:23:02 -0700, Santhosh Pasupuleti <Santhosh

 

[eliano]

I really appreciate you guys spending time on this. I will make it simple.. I
tried as suggested by you and Dave. I dont have any formulas in the sheetbut
this data is referenced from different sheet using formulas. Please find the
code below:
As stated in MSDN, the only difference between this property and the Value
property is that the Value2 property doesn’t use the Currency and Date data
types. Since the object doesnt show Value property, I decided to use Value2.

            ws = (Excel._Worksheet)wb.ActiveSheet;
            Excel.Range rj;
            rj = ws.Rows.get_Range("A1", "Z100");
            rj.Select();
            rj.Value2 = "";
            wb.Save();

Still it doesnt work.

Thanks and Regards,
Santhosh Kumar.P
---------------------------------------------------------------------------­--------------------------

If you have no formulas in the range you are attempting to clear, then
ClearContents should work.  If you do have formulas in the range, the
ClearContents will also clear the formulas and you indicated that you only
wanted to clear the data.  To just clear the data, you would have to make
the Value of the cells in the range equal a null string value.  Like Dave
stated. I am not really sure about the code you are using.  It appears that
you are trying to Set amd object variable but with the Set keyword.  The way
it is written, if it did anything it would create a variable for a massive
array, I think.  If you are trying to create rj as a range then you would
have to use the Set statement.  Then the range would have a value property
and you could use the null string to clear the data in the range.

Thanks for the quick reply. There is no property called Value for range
and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?
:
Try:
rj.Value = ""
"Santhosh Pasupuleti" <Santhosh (e-mail address removed)>
wrote
in messageHi,
I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please
tell
me
how to delete the data keeping row formatting in tact?
ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or
Excel.Range rj;
           rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
           rj.ClearContents();
Thanks and Regards,
Santhosh Kumar.P- Nascondi testo citato

- Mostra testo citato -

Hi Santosh.
It seems you dont know that excel has his own language (Vba); for
beginning to learn the excel Vba you can use the help on line, and
probably this might be an easy matter for people like you.
Regards
Eliano

 

[JLGWhiz]

Well Samthosh, wish i could help more, but I know absolutely zip about C#.
I do know that this line:

rj = ws.Rows.get_Range("A1", "Z100");

Would make the VBA compiler have coniptions. It could handle the ws.Rows
but the get_Range would throw it for a loop because it would be expecting
something entirely different as a property or method of Rows. Good Luck.

I really appreciate you guys spending time on this. I will make it simple.
I
tried as suggested by you and Dave. I dont have any formulas in the sheet
but
this data is referenced from different sheet using formulas. Please find
the
code below:
As stated in MSDN, the only difference between this property and the Value
property is that the Value2 property doesn't use the Currency and Date
data
types. Since the object doesnt show Value property, I decided to use
Value2.

ws = (Excel._Worksheet)wb.ActiveSheet;
Excel.Range rj;
rj = ws.Rows.get_Range("A1", "Z100");
rj.Select();
rj.Value2 = "";
wb.Save();

Still it doesnt work.

Thanks and Regards,
Santhosh Kumar.P
-----------------------------------------------------------------------------------------------------

If you have no formulas in the range you are attempting to clear, then
ClearContents should work. If you do have formulas in the range, the
ClearContents will also clear the formulas and you indicated that you
only
wanted to clear the data. To just clear the data, you would have to make
the Value of the cells in the range equal a null string value. Like Dave
stated. I am not really sure about the code you are using. It appears
that
you are trying to Set amd object variable but with the Set keyword. The
way
it is written, if it did anything it would create a variable for a
massive
array, I think. If you are trying to create rj as a range then you would
have to use the Set statement. Then the range would have a value
property
and you could use the null string to clear the data in the range.



"Santhosh Pasupuleti" <[email protected]>
wrote
in message news:[email protected]...

Thanks for the quick reply. There is no property called Value for range
and
there is "range2". Were you referring to this? And why isnt the clear
contents method working? any Idea?

:

Try:

rj.Value = ""


"Santhosh Pasupuleti" <Santhosh (e-mail address removed)>
wrote
in message Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear
contents
menthod as well as clear notes and it is of no use. Can anyone
please
tell
me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Santhosh Pasupuleti]

Chip,

Thanks for the info.

I tried all three options and the only option that worked in my case is
foreach (Excel.Range R in R3.Cells)

{
R.set_Value(Missing.Value, "");
}

I am little concerned about performance as it needs to clear data in each
cell but overall it serves the purpose. Thanks again!!

- Santhosh Pasupuleti

The following snippet works for me (XL 2007, C# 2008)

Excel.Range R1;
Excel.Range R2;
Excel.Range R3;
Excel.Worksheet WS;
int N = 10;
WS = (Excel.Worksheet)XLApp.ActiveWorkbook.Worksheets[1];
R1 = WS.get_Range("A1", "A10");
R2 = WS.get_Range("B1", "B10");
R3 = WS.get_Range("C1", "C" + N.ToString());
// Clear R1
R1.ClearContents();
// Clear R2
foreach (Excel.Range R in R2.Cells)
{
R.ClearContents();
}
// Clear R3
foreach (Excel.Range R in R3.Cells)
{
R.set_Value(Missing.Value, "");
}


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 4 Sep 2009 12:23:02 -0700, Santhosh Pasupuleti <Santhosh

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P

 

[Santhosh Pasupuleti]

Thanks Chip.

Following snippet worked.

foreach (Excel.Range R in R3.Cells)

{
R.set_Value(Missing.Value, "");
}

Thans again
- Santhosh Pasupuleti

The following snippet works for me (XL 2007, C# 2008)

Excel.Range R1;
Excel.Range R2;
Excel.Range R3;
Excel.Worksheet WS;
int N = 10;
WS = (Excel.Worksheet)XLApp.ActiveWorkbook.Worksheets[1];
R1 = WS.get_Range("A1", "A10");
R2 = WS.get_Range("B1", "B10");
R3 = WS.get_Range("C1", "C" + N.ToString());
// Clear R1
R1.ClearContents();
// Clear R2
foreach (Excel.Range R in R2.Cells)
{
R.ClearContents();
}
// Clear R3
foreach (Excel.Range R in R3.Cells)
{
R.set_Value(Missing.Value, "");
}


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 4 Sep 2009 12:23:02 -0700, Santhosh Pasupuleti <Santhosh

Hi,

I am trying to clear the row data keeping formatting in tact and
unfortunately the code below doesnt work. I tried using Clear contents
menthod as well as clear notes and it is of no use. Can anyone please tell me
how to delete the data keeping row formatting in tact?


ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString()).ClearContents();
or

Excel.Range rj;

rj = ws.Rows.get_Range("A1", "Z" +
(dss.Tables[0].Rows.Count).ToString());
rj.ClearContents();

Thanks and Regards,
Santhosh Kumar.P