Delete data starting from specific row

[andytpl]

I have a worksheet that have data starting row 8 from Col A to Col 8. I like to how will the codes be like to clear all the data (value only) starting from Row 8 from Col A to Col R upto 5 rows below the last cell that contains data.

Thanks :yes

[rbrhodes]

Hi andy,

So you want to start at Column A, Row 8 to Column R, Row (lastrow -5). Thereby leaving 5 rows of data at the bottom?



Option Explicit
Sub Clear()
Dim LastRow As Long
'//get last row of data - 5 rows
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row - 5

'//Clear A8 to R & last row - 5 rows
Range("A8:R" & LastRow).Clear
End Sub

[andytpl]

What I meant is to clear the data 5 rows below the last data entry. For example, if Row 20 is the last entry the codes is suppose to clear the data entry up to Row 25 between Col A to Col R

[rbrhodes]

Hi Andy,

Don't know why you want to clear 5 rows below the data but here you go.

It's the same as above but with + 5 instead of - 5 rows.


Option Explicit

Sub Clear()
Dim LastRow As Long
'//get last row of data - 5 rows
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 5

'//Clear A8 to R & last row + 5 rows
Range("A8:R" & LastRow).Clear

End Sub


Unless you want to delete the rows?

[andytpl]

rbrhodes,

Thx!

I have changed the last part of your codes as I need only to clear the values of each cell rather than clear all.

Range("A8:R" & LastRow).ClearContents