I want to go to the end of column J, move up two rows, and delete the
last three rows. I'm just not sure how to get the delete range to
vary as I need it to. See code below:

Range("J2").Select
Selection.End(xlDown).Offset(-2, 0).Select
Rows("160:162").Select
Selection.Delete Shift:=xlUp
End Sub

Thanks in advance for your help!

LastRow = Range("J2").End(xlDown).Offset(-2, 0)
Rows((LastRow - 2) & ":" & LastRow).Delete

"Lotto" wrote:

I want to go to the end of column J, move up two rows, and delete the
last three rows. I'm just not sure how to get the delete range to
vary as I need it to. See code below:

Range("J2").Select
Selection.End(xlDown).Offset(-2, 0).Select
Rows("160:162").Select
Selection.Delete Shift:=xlUp
End Sub

Thanks in advance for your help!

I made i small mistake

LastRow = Range("J2").End(xlDown).Row
Rows((LastRow - 2) & ":" & LastRow).Delete

If there arre blank cells in column J then use this

LastRow = Range("J" & Rows.Count).End(xlup).Row
Rows((LastRow - 2) & ":" & LastRow).Delete


"Lotto" wrote:

I want to go to the end of column J, move up two rows, and delete the
last three rows. I'm just not sure how to get the delete range to
vary as I need it to. See code below:

Range("J2").Select
Selection.End(xlDown).Offset(-2, 0).Select
Rows("160:162").Select
Selection.Delete Shift:=xlUp
End Sub

Thanks in advance for your help!

try this:
'============================
Sub delete_J()

Dim myLastRow As Long
Dim myDeleteRange As Range

myLastRow = Worksheets("sheet1") _
.Range("J5000").End(xlUp).Row

Set myDeleteRange = Worksheets("sheet1") _
.Range("J" & myLastRow & ":J" _
& myLastRow - 2)

myDeleteRange.Delete Shift:=xlUp

End Sub
'=======================
worked for me.
:)
susan



On Mar 24, 9:13*am, Lotto wrote:
I want to go to the end of column J, *move up two rows, and delete the
last three rows. *I'm just not sure how to get the delete range to
vary as I need it to. *See code below:

Range("J2").Select
* * Selection.End(xlDown).Offset(-2, 0).Select
* * Rows("160:162").Select
* * Selection.Delete Shift:=xlUp
End Sub

Thanks in advance for your help!

This should do what you want...

Worksheets("Sheet1").Cells(Rows.Count, "J").End(xlUp).Offset(-2).Resize(3).EntireRow.Delete

--
Rick (MVP - Excel)


"Lotto" wrote in message ...
I want to go to the end of column J, move up two rows, and delete the
last three rows. I'm just not sure how to get the delete range to
vary as I need it to. See code below:

Range("J2").Select
Selection.End(xlDown).Offset(-2, 0).Select
Rows("160:162").Select
Selection.Delete Shift:=xlUp
End Sub

Thanks in advance for your help!