Clear all rows except first two

[Paul Kraemer]

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

 

[Don Guillett]

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

 

[Luke M]

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

 

[Ryan H]

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.

 

[Ryan H]

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

 

[Peter T]

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

 

[Rick Rothstein]

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

 

[Ryan H]

Thanks for noticing. Force of habit I guess.