Clear Sheet in Excel
 

Hi,

I have two sheets in an Excel file, Sheet1 and Sheet2. In Sheet1, I
have data columns from A to H with an unkown number of rows. In Sheet2,
I have data columns from A to Q with an unkown number of rows.

Now I want to clear everything from row 2 in Sheet1. And I also want to
clear everything from row 2 in Sheet2 with the exception that the value
in cell "E2" should remain.

I have created a macro as follows:

' Clear Sheet1 and Sheet2 (but keep the the first three rows)
Sheet1.Range("A2", "H65000").Clear()

' The cell of E2 should NOT be cleared!
Sheet2.Range("A2", "D2").Clear()
Sheet2.Range("F2", "Q2").Clear()
Sheet2.Range("A3", "Q65000").Clear()

Two issues he

1) These statements are syntactically incorrect. Could anyone correct
the syntax for me?

2) I didn't intend to use "65000" as the upper bound. But how to get
the maximu row number with non-empty cells?

Thanks!
-Emily

Clear Sheet in Excel
 

Emily, see other thread

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Emily" wrote in message oups.com...
Hi,

I have two sheets in an Excel file, Sheet1 and Sheet2. In Sheet1, I
have data columns from A to H with an unkown number of rows. In Sheet2,
I have data columns from A to Q with an unkown number of rows.

Now I want to clear everything from row 2 in Sheet1. And I also want to
clear everything from row 2 in Sheet2 with the exception that the value
in cell "E2" should remain.

I have created a macro as follows:

' Clear Sheet1 and Sheet2 (but keep the the first three rows)
Sheet1.Range("A2", "H65000").Clear()

' The cell of E2 should NOT be cleared!
Sheet2.Range("A2", "D2").Clear()
Sheet2.Range("F2", "Q2").Clear()
Sheet2.Range("A3", "Q65000").Clear()

Two issues he

1) These statements are syntactically incorrect. Could anyone correct
the syntax for me?

2) I didn't intend to use "65000" as the upper bound. But how to get
the maximu row number with non-empty cells?

Thanks!
-Emily

Clear Sheet in Excel
 

Sheet1.Range("A2:H65000").Clear

' The cell of E2 should NOT be cleared!
Sheet2.Range("A2:D2").Clear
Sheet2.Range("F2:Q2").Clear
Sheet2.Range("A3:Q65000").Clear

--
Regards,
Tom Ogilvy

"Emily" wrote:

Hi,

I have two sheets in an Excel file, Sheet1 and Sheet2. In Sheet1, I
have data columns from A to H with an unkown number of rows. In Sheet2,
I have data columns from A to Q with an unkown number of rows.

Now I want to clear everything from row 2 in Sheet1. And I also want to
clear everything from row 2 in Sheet2 with the exception that the value
in cell "E2" should remain.

I have created a macro as follows:

' Clear Sheet1 and Sheet2 (but keep the the first three rows)
Sheet1.Range("A2", "H65000").Clear()

' The cell of E2 should NOT be cleared!
Sheet2.Range("A2", "D2").Clear()
Sheet2.Range("F2", "Q2").Clear()
Sheet2.Range("A3", "Q65000").Clear()

Two issues he

1) These statements are syntactically incorrect. Could anyone correct
the syntax for me?

2) I didn't intend to use "65000" as the upper bound. But how to get
the maximu row number with non-empty cells?

Thanks!
-Emily