Clear Contents of Specific Cells in Last Row

[Steve]

Hi all. How can I have VBA clear the contents of the cells in the
LAST ROW in columns A,C,F, and K? The last row will vary, so VBA
needs to determine which row is the last. The columns will remain
static.

Thanks!

 

[Mike]

Sub test()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lastrow).ClearContents
Range("C" & lastrow).ClearContents
Range("F" & lastrow).ClearContents
Range("K" & lastrow).ClearContents
End Sub

 

[Chip Pearson]

Try code like the following:

Sub AAA()
Dim LastCell As Range
Dim WS As Worksheet
Dim N As Long
Dim Cols As Variant

Cols = Array("A", "C", "F", "K")
Set WS = ActiveSheet
With WS
For N = LBound(Cols) To UBound(Cols)
Set LastCell = .Cells(.Rows.Count, Cols(N)).End(xlUp)
LastCell.Delete shift:=xlUp
Next N
End With
End Sub

This deletes the last value in each of the columns. It supports the
case when the columns have different lengths.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)