Find Last cell used & delete 6 rows below that

[berty2000]

Using excel 2003
Trying to figure out how to search down a list of cells in column B.

Find the last cell with data in then delete the next 10 rows below this selection

so far


Sub Cleanup()

'finds the last active cell in range + 1 cell
ActiveSheet.Range("B500").End(xlUp).Offset(1, 0).Select

ActiveCell.Offset(10).Select
End Sub

kind of lost how to do the last bit ?

 

[VoG]

Try

Sub Cleanup()
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(10).EntireRow.Delete
End Sub

 

[berty2000]

Sub Cleanup()

'finds the last active cell in range + 1 cell
ActiveSheet.Range("B500").End(xlUp).Offset(1, 0).Select

Dim xlong As Long
Range(Cells(Rows.Count, 2).End(xlUp).Offset(1), _
Cells(Rows.Count, 2)).EntireRow.Delete
xlong = ActiveSheet.UsedRange.Rows.Count
End Sub

Kind of got it too work but it deletes all rows below the selection not last 6

 

[obiron]

VoG's code is right, but why are you trying to delete empty rows?

What benefit will this give you??

 

[berty2000]

Thank you Vog that work exactly how i wanted

obiron there is more data lower down in other rows just need to delete a couple of rows below the data in B column to bring other data back up if that makes sense ! It does not as im writing it

 

[berty2000]

Thank you that worked just right and in just one line ! this VB code thingy in excel is really good, never even knew it existed until stumbling upon this site.