Clear Sheets in Excel

[Emily]

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

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

 

[Ron de Bruin]

Hi Emily

you can use

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

 

[Guest]

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

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

 

[Dave Peterson]

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

 

[Emily]

Hi Dave,

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

-Emily

 

[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

 

[Dave Peterson]

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.

 

[Ron de Bruin]

Why do you have a problem with that ?

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

 

[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

 

[Guest]

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("A22").Clear
.Range("F2:Q2").Clear
.Range("A3:Q" & lastrow).Clear
End With

 

[Ron de Bruin]

See Tom's reply

 

[Emily]

Thanks Tom!

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

-Emily