help with macro
 

My goal is to add a cell between a name i enter and the total so there is
always a blank row. i have this but it only works if cell a4 is changed.

A B c
1 Customers
1 George
2 Sam
3
4 Total

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Intersect(Target, Range("A4")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring is changed!
For i = lastrow To 2 Step -1
If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then
Rows(i).Insert
End If
Next
Application.EnableEvents = True
End If
End Sub

Any help would be appreciated

help with macro
 

You have a solution in general questions. Please don't multi post. Check out
this link

http://www.cpearson.com/excel/newposte.htm
--
HTH...

Jim Thomlinson


"jhyatt" wrote:

My goal is to add a cell between a name i enter and the total so there is
always a blank row. i have this but it only works if cell a4 is changed.

A B c
1 Customers
1 George
2 Sam
3
4 Total

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Intersect(Target, Range("A4")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring is changed!
For i = lastrow To 2 Step -1
If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then
Rows(i).Insert
End If
Next
Application.EnableEvents = True
End If
End Sub

Any help would be appreciated

help with macro
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring is changed!
For i = lastrow To 2 Step -1
If InStr(1, Cells(i, "A"), "Total", vbTextCompare) Then
If Cells(i - 1, "A").Value < "" Then
Rows(i).Insert
Exit For
End If
End If
Next
Application.EnableEvents = True
End If
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jhyatt" wrote in message
...
My goal is to add a cell between a name i enter and the total so there is
always a blank row. i have this but it only works if cell a4 is changed.

A B c
1 Customers
1 George
2 Sam
3
4 Total

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Intersect(Target, Range("A4")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring is changed!
For i = lastrow To 2 Step -1
If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then
Rows(i).Insert
End If
Next
Application.EnableEvents = True
End If
End Sub

Any help would be appreciated

help with macro
 

sorry i thoght i had it in the wrong section

"Jim Thomlinson" wrote:

You have a solution in general questions. Please don't multi post. Check out
this link

http://www.cpearson.com/excel/newposte.htm
--
HTH...

Jim Thomlinson


"jhyatt" wrote:

My goal is to add a cell between a name i enter and the total so there is
always a blank row. i have this but it only works if cell a4 is changed.

A B c
1 Customers
1 George
2 Sam
3
4 Total

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Intersect(Target, Range("A4")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring is changed!
For i = lastrow To 2 Step -1
If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then
Rows(i).Insert
End If
Next
Application.EnableEvents = True
End If
End Sub

Any help would be appreciated