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("A2:D2,F2:H2,A3:H" & Rows.Count).Clear



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



"Emily" <[email protected]> wrote in message news:[email protected]...
> 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
>

[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

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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
>

[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

[Ron de Bruin]

See Tom's reply


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



"Emily" <[email protected]> wrote in message news:[email protected]...
> 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
>

[Tom Ogilvy]

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

[Tom Ogilvy]

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

[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

[Dave Peterson]

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

--

Dave Peterson

[Emily]

Hi Dave,

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

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



Emily wrote:
>
> Hi Dave,
>
> What's the difference between ".clear" and ".clearcontents"?
>
> -Emily

--

Dave Peterson