Macro

[chazzerman01]

is it possible for a macro to run if the active cell value is different to the value above it

 

[My Aswer Is This]

Try this:
See if this works Table names changed.
I will read your last post more and see what needs to be done about automatic running

You did not answer my question about Total Row.

Sub Resize_Me()
'Modified  1/8/2020  9:36:27 AM  EST
Dim rng As Range
Dim ans As Long
ans = InputBox("How Many Rows")
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table6")
Set rng = Range("Table6[#All]").Resize(tbl.Range.Rows.Count + ans, tbl.Range.Columns.Count)
tbl.Resize rng
Range("Table6[#All]").Select
End Sub

 

[My Aswer Is This]

I really do not know how you can have this done automatically.

 

[FatBoyClam]

I think what you're asking is how to run your code (or @My Aswer Is This 's code) on submitting the value "Bob" (or whatever) to the new row.

To do this you want to use the Worksheet_Change function in the Sheet module.

This function has a "Target" parameter, which is the range that has changed (in this case the single cell you've just typed in).

Don't simply invoke your code from this function - first test that the Target range is indeed the one you want (it'll trigger this code when you alter the value of any cell on the worksheet.

In your case, I'd check it was the cell in the last row of the first column of the table

for example

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table 6")
If Not Application.Intersect(Target, tbl.ListRows(tbl.ListRows.Count).Range(1, 1)) Is Nothing Then

    

End If

 

[FatBoyClam]

Sorry - accidentally posted that before it was complete.

You need to call the Resize_Me function from within the If...End If clause

 

[chazzerman01]

sorry to keep being a pest, but at the start would I put "Private Sub Worksheet_Change(ByVal Target As range)"

 

[FatBoyClam]

Yes (although I tend to select "Worksheet" and "Change" from the drop-downs at the top of the editor pane, and it puts in that line and the End Sub line automatically).

 

[My Aswer Is This]

Fat Boy provide a answer that worked. Thanks I did not no how to do this using a Table
you need the previous code I provided named Resize Me
And this script.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/8/2020  10:27:51 AM  EST
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table6")
If Not Application.Intersect(Target, tbl.ListRows(tbl.ListRows.Count).Range(1, 1)) Is Nothing Then
Application.EnableEvents = False
Call Resize_Me
End If
Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

Yes (although I tend to select "Worksheet" and "Change" from the drop-downs at the top of the editor pane, and it puts in that line and the End Sub line automatically).

See my latest post

 

[My Aswer Is This]

See my last post for the complete answer

 

[FatBoyClam]

Oooh - one more thing that's just occurred to me: The act of inserting a row will invoke the Change function, so the test needs to be that the target cell is the first column of the last row and the cell has a value.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/8/2020  10:27:51 AM  EST
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table6")
If Not Application.Intersect(Target, tbl.ListRows(tbl.ListRows.Count).Range(1, 1)) Is Nothing and Target.Value<>"" Then
Application.EnableEvents = False
Call Resize_Me
End If
Application.EnableEvents = True
End Sub