Clear contents of last two rows of data on sheet
 

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

Clear contents of last two rows of data on sheet
 

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

--
Regards,

OssieMac

Clear contents of last two rows of data on sheet
 

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

Appreciate your help!

Dani

"OssieMac" wrote:

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

--
Regards,

OssieMac

Clear contents of last two rows of data on sheet
 

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

"OssieMac" wrote:

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

--
Regards,

OssieMac

Clear contents of last two rows of data on sheet
 

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.

--
Rick (MVP - Excel)


"Danielle" wrote in message
...
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

"OssieMac" wrote:

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

--
Regards,

OssieMac

Clear contents of last two rows of data on sheet
 

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" wrote:

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.

--
Rick (MVP - Excel)


"Danielle" wrote in message
...
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

"OssieMac" wrote:

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

--
Regards,

OssieMac



.

Clear contents of last two rows of data on sheet
 

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

--
Rick (MVP - Excel)


"Danielle" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"Danielle" wrote in message
...
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

"OssieMac" wrote:

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

--
Regards,

OssieMac



.