• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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
0
the_duduman
Asked:
the_duduman
  • 2
  • 2
1 Solution
 
bobbit31Commented:
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
0
 
the_dudumanAuthor Commented:
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

0
 
bobbit31Commented:
yes... Sheet1 should be the name of the sheet

OR

replace all instances of Sheet1 w/ Application.ActiveSheet
0
 
the_dudumanAuthor Commented:
Works like a charm!

Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now