When Sub Col_A() moves a short list of text or numbers up a row at a time, when the top entry of the list gets to A1, I need one of the event codes to fire so I can process the A1 entry.

Neither of these event codes work unless I manually enter something in A1, where the Change event works, or do a simple calculation in a cell =1+1 enter, then the Calculate event code works.

(Calc on the sheet settings is set to Automatic)

Howard


Sub Col_A()

Sheets("Sheet2").Range(Range("A1").End(xlDown), Cells(Rows.Count, "A").End(xlUp)).Cut Range("A1").End(xlDown).Offset(-1)

Sheets("Sheet2").Calculate
End Sub


Private Sub Worksheet_Calculate()

If Range("A1") < "" Then
MsgBox "A1 = " & [A1]
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address < "$A$1" Then Exit Sub

MsgBox "A1 = " & [A1]

End Sub

Hi Howard,

Am Tue, 21 Apr 2015 05:15:30 -0700 (PDT) schrieb L. Howard:

When Sub Col_A() moves a short list of text or numbers up a row at a time, when the top entry of the list gets to A1, I need one of the event codes to fire so I can process the A1 entry.

Neither of these event codes work unless I manually enter something in A1, where the Change event works, or do a simple calculation in a cell =1+1 enter, then the Calculate event code works.

Worksheet_Change does not work with copy & paste
Worksheet_Calculate does not work if there are no cells to calculate on
the sheet.
Write in a empty cell =TODAY() and Worksheet_Calculate works.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Tue, 21 Apr 2015 05:15:30 -0700 (PDT) schrieb L. Howard:

When Sub Col_A() moves a short list of text or numbers up a row at a time, when the top entry of the list gets to A1, I need one of the event codes to fire so I can process the A1 entry.

Neither of these event codes work unless I manually enter something in A1, where the Change event works, or do a simple calculation in a cell =1+1 enter, then the Calculate event code works.

or try it without event macro:

Sub Col_A()
Dim lngStart As Long
Dim lngEnd As Long

With Sheets("Sheet2")
lngStart = .Range("A1").End(xlDown).Row
lngEnd = .Cells(Rows.Count, 1).End(xlUp).Row
.Range(.Cells(lngStart, 1), .Cells(lngEnd, 1)).Cut .Cells(lngStart -
1, 1)
End With

If lngStart - 1 = 1 Then MsgBox "A1 = " & [A1]

End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

or try it without event macro:

Sub Col_A()
Dim lngStart As Long
Dim lngEnd As Long

With Sheets("Sheet2")
lngStart = .Range("A1").End(xlDown).Row
lngEnd = .Cells(Rows.Count, 1).End(xlUp).Row
.Range(.Cells(lngStart, 1), .Cells(lngEnd, 1)).Cut .Cells(lngStart -
1, 1)
End With

If lngStart - 1 = 1 Then MsgBox "A1 = " & [A1]

End Sub



Regards
Claus B.


Oh yeah. That is nice. Does a great job.

Thanks.

I've got one really goofy project going, have to take it a step at a time so will probably have some more questions.

Howard