Excel VBA - Clearing Cells from a Specific Row

[Grothgar]

Hello All;

I have the following problem that I need to solve using VBA... I have a sort of solution, but I am sure there are people here with much more knowledge than myself.

So, what I need the Macro to do is, go down to a certain cell from a starting position, and then clear the Rows from one below the last filled cell.

I know how to start this

Range("L7").Select
Selection.End(xlDown).Offset(1, 0).Select

This gets me to the row below the last filled cell, but I have no idea how to get it to select the Rows, rather than just the cell.

Any help would be appreciated.

 

[Joe4]

I am not quite certain it is clear exactly which rows you are trying to clear. Perhaps walking us through a simple example will clarify it.

Also, do you want to delete the rows, or just clear the contents of all cells in those rows?

 

[Grothgar]

I am not quite certain it is clear exactly which rows you are trying to clear. Perhaps walking us through a simple example will clarify it.

Also, do you want to delete the rows, or just clear the contents of all cells in those rows?

I am trying to clear the rows below the last amount of data in Column L.
So, if data is in cell L56, then I would want to clear rows 57 down.

And, I am looking to just clear the contents, not delete the rows

 

[Joe4]

OK, so there is no data in column L below 56. Which column can you use to determine how far down you need to go?

 

[taurean]

From your description, this should work:

Public Sub ClearContents()
Dim lLR As Long
lLR = Range("L7").End(xlDown).Offset(1, 0).Row
Range("L" & lLR & ":L" & Rows.Count).EntireRow.ClearContents
End Sub

But try it on a backup sheet first!

 

[Grothgar]

From your description, this should work:

Public Sub ClearContents()
Dim lLR As Long
lLR = Range("L7").End(xlDown).Offset(1, 0).Row
Range("L" & lLR & ":L" & Rows.Count).EntireRow.ClearContents
End Sub

But try it on a backup sheet first!

Loving it!
That works in exactly the way that I desired... thank you very much.