asked on

Simple VB operation needed for excel Macro

Hello,

I could really use a macro that can perform the following function:

Delete the contents of the last non-empty cell in the column before the active cell.

For example:

Say the active cell is B5, I would like the macro to delete the contents of the last non-empty cell in column A (so if A1-A10 is a list, delete the contents of cell A10). If possible B5 would return to be the actice cell.

Greatly appreciated.

Dudu

bobbit31

Sub DeleteLastNonEmpty()
Dim curCol As Integer
curCol = Application.ActiveCell.Column

prevCol = curCol - 1

'' make sure there is a previous column
Dim i As Integer
i = 1
If prevCol >= 1 Then
While Not IsNull(Sheet1.Cells(i, prevCol)) And Trim(Sheet1.Cells(i, prevCol)) <> ""
i = i + 1
Wend
End If

Sheet1.Cells(i - 1, prevCol) = Null
End Sub

the_duduman

ASKER

Thanks for your quick reply bobbit31,

I seem to get an "1004 run-time error - application defined- or object-defined error". Pressing debug takes me to the the last line of the script:

Sheet1.Cells(i - 1, prevCol) = Null

The sheet is not called Sheet1, could this be the problem?

There is no deletion taking place by the way.

Is this fixable?

Dudu

ASKER CERTIFIED SOLUTION

bobbit31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

the_duduman

ASKER

Works like a charm!

Thanks!