Clear contents of last two rows of data on sheet

[Danielle]

Hi.

I'm using v 2003.

I need a couple of lines of code that will delete or clear the contents of
the last two rows on a sheet that have data in them. In other words, if there
are 800 lines of data and I want rows 799 and 800 to be deleted or contents
cleared. The number of the last row varies everytime I run the report.

Can anyone help? Thanks!

Dani

 

[OssieMac]

Hi Dani,

Here are 2 options. See the comments for when to use the option.

'Option 1
Sub DeleteRows1()
'When a specific column will always have data in last row.

Dim lastRow As Long

With Sheets("Sheet1")
'Can replace "A" with any column that
'will always have data in the last row.
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(lastRow - 1 & ":" & lastRow).Delete

End With

End Sub


'Option 2
Sub DeleteRows2()

'If it is not know which column
'will always have data in the last row.

Dim lastRow As Long

With Sheets("Sheet1")

lastRow = .Cells _
.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

.Rows(lastRow & ":" & lastRow - 1).Delete

End With

End Sub

 

[Danielle]

The first option works great! Thanks! I should've specified that there is
always data in column A.

Appreciate your help!

Dani

 

[Danielle]

Ossie,

Along those same lines, is there a couple lines of code that will clear the
contents on all of the rows below the last of row of contiguous data, even if
the rows are hidden.

In other words, a set of data is in A1:J100, and then there are about 20
blank rows, then some more data starts in A120. I'd want the code to find the
last row of the initial contiguous data (100), then delete everything in the
spreadsheet below it even if some rows are hidden.

Thanks again!
Dani

 

[Rick Rothstein]

Perhaps this code will do what you want...

Dim FirstBlankCell As Range
'....
'....
On Error Resume Next
Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
Rows(FirstBlankCell(1).Row & ":100").ClearContents
End If

Note 1: Since you cannot Undo the deletions from a macro, you should
first test this code out on a copy of your worksheet and not
on your live data.

Note 2: Change my reference to Worksheets("Sheet1") to reflect the
actual worksheet name you want to apply this code to.

 

[Danielle]

Thanks Rick,

This works except that the range on the spreadsheet is different with each
report so it doesn't always end on row 100. I need it to find where it ends,
then do the delete.

Any other thoughts?

 

[Rick Rothstein]

Okay, try this code instead...

Dim FirstBlankCell As Range, LastRow As Long
'....
'....
On Error Resume Next
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
Set FirstBlankCell = Worksheets("Sheet1").Range("A:J"). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
Rows(FirstBlankCell(1).Row & ":" & LastRow).ClearContents
End If