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

Hi Emily

you can use

Sheet1.Range("A2:D2,F2:H2,A3:H" & Rows.Count).Clear



--
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

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

Just curious if you really wanted .clear or .clearcontents?

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

--

Dave Peterson

Hi Dave,

What's the difference between ".clear" and ".clearcontents"?

-Emily

Hi Ron,

Rows.Count gives the maximum number of rows, 65535, inm a sheet. It
doesn't give me the info I need. I need it to be 29 the maximum row #
in the sheet is 29. Or 31 if the maximum row # in the sheet is 31.

Hope you see what I mean.

-Emily

Try it manually in a test worksheet/workbook.

Select a couple of cells.

Put 12345 in each.
Give each cell a custom format (000.00) and some nice font color on a nice fill
color--add some borders.

Then select one of the cells and use
edit|clear|All

On the other, use Edit|Clear|ClearContents

You'll see the nice colors are still there on one of them. And if you type
12345 in each, you'll see that the numberformat hung around on one of them.



Emily wrote:

Hi Dave,

What's the difference between ".clear" and ".clearcontents"?

-Emily

--

Dave Peterson

Why do you have a problem with that ?

Do you have a data that you want to keep below row 29 ?


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



"Emily" wrote in message ps.com...
Hi Ron,

Rows.Count gives the maximum number of rows, 65535, inm a sheet. It
doesn't give me the info I need. I need it to be 29 the maximum row #
in the sheet is 29. Or 31 if the maximum row # in the sheet is 31.

Hope you see what I mean.

-Emily

Hi Ron,

My macro automatically imports file names to the excel from a folder -
each row contains one file name plus other info. Since I don't know how
many files there are in the folder, I don't know how many rows there
are in the Excel.

When I clear the sheet, I only need to clear up to the last row in the
sheet instead of clearing 65K rows which is too expensive and
unnecessary (because the number of file names is only in the range of
hundreds or thousands), no need to clear 65K row!

Therefore, I'll need to dynamically get the row number. However, I
really don't know how to get that #.

-Emily

didn't see question 2

with worksheets("Sheet1")
lastrow = .cells(rows.count,"H").End(xlup).row
.Range("A2:H" & Lastrow).Clear
End with
With Worksheets("Sheet2")
lastrow = .cells(rows.count,"Q").End(xlup).row
.Range("A2:D2").Clear
.Range("F2:Q2").Clear
.Range("A3:Q" & lastrow).Clear
End With

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

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

See Tom's reply


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



"Emily" wrote in message ups.com...
Hi Ron,

My macro automatically imports file names to the excel from a folder -
each row contains one file name plus other info. Since I don't know how
many files there are in the folder, I don't know how many rows there
are in the Excel.

When I clear the sheet, I only need to clear up to the last row in the
sheet instead of clearing 65K rows which is too expensive and
unnecessary (because the number of file names is only in the range of
hundreds or thousands), no need to clear 65K row!

Therefore, I'll need to dynamically get the row number. However, I
really don't know how to get that #.

-Emily

Thanks Tom!

I think this is the answer I was looking for. Let me try it.

-Emily

Tom Ogilvy wrote:
didn't see question 2

with worksheets("Sheet1")
lastrow = .cells(rows.count,"H").End(xlup).row
.Range("A2:H" & Lastrow).Clear
End with
With Worksheets("Sheet2")
lastrow = .cells(rows.count,"Q").End(xlup).row
.Range("A2:D2").Clear
.Range("F2:Q2").Clear
.Range("A3:Q" & lastrow).Clear
End With

--
Regards,
Tom Ogilvy