Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

sub clearrows()
dim lr as long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
rows(3).resize(lr).delete
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul Kraemer" wrote in message
...
Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear
all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data
except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

Range("3:65536").ClearContents
'Assumes normal worksheet size

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

I would do it 1 or 2 ways.

If you want to completly delete all rows below Row 2. Use this:

Sub DeleteRows()
Rows("3:" & Rows.Count).Delete Shift:=xlUp
End Sub

If you just want to clear the contents of the cell, but still preserve the
formatting of the cell. Use this:

Sub ClearData()
Rows("3:" & Rows.Count).ClearContents
End Sub

This code will work for any version of Excel. Hope this helps! If so, let
me know, click "YES" below.


--
Cheers,
Ryan


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

What is considered a "normal" worksheet size? I assume you mean Excel
version 2003. I would recommend using this instead. It will count the rows
no matter which version of Excel you have. See my post. Forgive me, just
being picky! :)

Rows("3:" & Rows.Count).ClearContents
--
Cheers,
Ryan


"Luke M" wrote:

Range("3:65536").ClearContents
'Assumes normal worksheet size

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

Another way -

Sub test2()
Dim row1 As Long, rowsCnt As Long
Dim rng As Range

Set rng = ActiveSheet.UsedRange
row1 = rng.Rows(1).Row
rowsCnt = rng.Rows.Count

If row1 < 3 Then
If rowsCnt row1 + 1 Then
rng.Offset(row1 + 1).Resize(rowsCnt - row1 - 1).Clear
End If
End If

End Sub

Regards,
Peter T

"Paul Kraemer" wrote in message
...
Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear
all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data
except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

Rows("3:" & Rows.Count).Delete Shift:=xlUp

You can leave of the Shift argument as it won't matter whether Excel chooses
to shift up or left... there is no data so it won't matter. So your code
line becomes nice and short...

Rows("3:" & Rows.Count).Delete

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
I would do it 1 or 2 ways.

If you want to completly delete all rows below Row 2. Use this:

Sub DeleteRows()
Rows("3:" & Rows.Count).Delete Shift:=xlUp
End Sub

If you just want to clear the contents of the cell, but still preserve the
formatting of the cell. Use this:

Sub ClearData()
Rows("3:" & Rows.Count).ClearContents
End Sub

This code will work for any version of Excel. Hope this helps! If so,
let
me know, click "YES" below.


--
Cheers,
Ryan


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear
all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data
except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

Thanks for noticing. Force of habit I guess.
--
Cheers,
Ryan


"Rick Rothstein" wrote:

Rows("3:" & Rows.Count).Delete Shift:=xlUp

You can leave of the Shift argument as it won't matter whether Excel chooses
to shift up or left... there is no data so it won't matter. So your code
line becomes nice and short...

Rows("3:" & Rows.Count).Delete

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
I would do it 1 or 2 ways.

If you want to completly delete all rows below Row 2. Use this:

Sub DeleteRows()
Rows("3:" & Rows.Count).Delete Shift:=xlUp
End Sub

If you just want to clear the contents of the cell, but still preserve the
formatting of the cell. Use this:

Sub ClearData()
Rows("3:" & Rows.Count).ClearContents
End Sub

This code will work for any version of Excel. Hope this helps! If so,
let
me know, click "YES" below.


--
Cheers,
Ryan


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to write a VBA Subroutine to delete/clear
all
data on a particular worksheet except for the first two rows.

This worksheet can have any number of rows filled with data. Can anyone
give me a hint on how I can select and clear all rows containing data
except
for the first two rows?

Thanks in advance,
Paul
--
Paul Kraemer

.