VBA - Excel 2013 - Dynamic Ranges

[CRUTHERFORD]

Hi,

So, I a have a command button set up which will clear certain cells. For example, in cells A1:A5 i have various numeric values and this command button clears contents. However, if I wanted to insert a row after cell A2, how can i get the VBA to incorporate all cells from A1:A6 or if i wanted to insert 2 new rows then A1:A7?

Thanks,
Cam

 

[JackDanIce]

Try:

Sub FindandClearLastRow()

Dim LR as Long

LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").Resize(LR,1).ClearContents

End Sub

 

[njimack]

The easiest way is to create a dynamic range - see here: http://www.contextures.com/xlNames01.html#Dynamic

Alternatively, if there is nothing else in column A, you could do something like this:

Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row).ClearContents