How do I figure out last activecell? I can't figure this out.

Posted by A beginner on January 07, 2002 11:34 PM

I'm trying to add code to the worksheet event section where, if the user enters a value in the range b2 to j18, it automatically deletes any other values in that row from column b to j, and leaves the previous cell as is. I don't know how to find any commands for finding the previous cell used once the user either hits enter or clicks to another cell, or if there's a command like worksheetcell_exit like textbox_exit for example??? Thanks

Posted by Dank on January 08, 2002 2:43 AM

Hello,

You can achieve this by saving the value of the changed cell into a temporary variable, deleting the contents of the row in question and then writing that variable back to the cell. If you right click your worksheet tab, choose View Code and try something along these lines:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vTempValue As Variant

If Not (Target.Columns.Count = 1 Or Target.Rows.Count = 1) Then Exit Sub 'More than one cell changed

If Intersect(Target, Me.Range("B2:J18")) Is Nothing Then Exit Sub 'Not in range B2:J18

vTempValue = Target.Value
Application.EnableEvents = False

Me.Range("B" & Target.Row & ":J" & Target.Row).ClearContents

Target.Value = vTempValue
Application.EnableEvents = True

End Sub

Hope it helps,
Daniel.