Trying to set a function as oldrows count before the delete row command is activated

[Eric Penfold]

Please help to make the function count the oldrows

Public Sub Delete_Or_Insert_Rows()

        Dim NewRowCount As Long
        Dim OldRowCount   As Long
        Dim LRow                As Long
        Dim rng                   As Range
        Dim ws                    As Worksheet
    
           Set ws = ActiveSheet
           LRow = Range("A1").End(xlDown).Row
           Set rng = ws.Range("A1:A" & LRow)
           OldRowCount = OldrowQty

        NewRowCount = rng.Rows.Count
        If OldRowCount > NewRowCount Then
         Exit Sub
         OldRowCount = NewRowCount
         ElseIf OldRowCount < NewRowCount Then
         Exit Sub
         ElseIf OldRowCount = NewRowCount Then
         Call Group_OrderNos
    End If
    End If
    
End Sub

Public Function OldrowQty(rng As Range) As String

        Dim OldRowCount As Long
        Dim LRow As Long
        Dim ws As Worksheet

           Set ws = ActiveSheet
           LRow = Range("A1").End(xlDown).Row
           Set rng = ws.Range("A1:A" & LRow)
           
           OldrowQty = rng.Rows.Count


End Function

 

[Eric Penfold]

Yes there are 12 sheets

 

[Eric Penfold]

Sorry but when I paste a value in column A it does not run?

 

[HaHoBe]

Hi Eric,

like I stated when posting the code: the event will only get triggered if the change is on the sheets I assumed you want to work with. And I would run this procedure to make sure that events will get triggered and the normal hints when deleting sheets or closing workbook without saving are back on:

Sub TurnOnApps()
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
  End With
End Sub

If you want the macro to get started on any sheet I would need to restrict the worksheets in the procedure but I have a lot of problems understanding the flow of the macro (or why you let the code perform certain steps).

Ciao,
Holger

 

[Eric Penfold]

When you understanding the flow or certain steps please explain further so I can understand.

 

[Eric Penfold]

The paste issue is now sorted just I need it so any row or rows deleted in the list it won`t run the code.

 

[Eric Penfold]

The paste issue is now sorted just I need it so any row or rows deleted in the list it won`t run the code.

Please ignore the above.

CLng(strValue)

How is this calculated ? Can`t get it to count current worksheet?

 

[HaHoBe]

Hi Eric,

regarding your question: if you look at the Name Manager you will find just one value for the variable I used and RefersTo. The number behind = is the number of old rows. As I wasn't aware üf more sheets that you would need the information for I planned only one instance to store that number - my bad.

Could you please explain why you would need to know the number of (old) entries on a worksheet amd why you decided to start in A1 going down: if you insert a new row in Row 7 this would get the last row of continuous entries to be 6. What about any other entries below? You want the procedure to be run if an entry is made (either by inserting a row or by pasting) or deleted only on certain sheets?. If so is it really necessary to have the procedure which is called work on the entire range limited by the new last entry? Will the changes be made by hand or by update/calculation? All this should be monitored in the event and for me the call of the procedure should be made there and maybe a number or range passed to work on as another parameter.

Tell an idiot like me in plain words what you want to happen at which point, then I should be able to come up with proper codings for the problem.

Ciao,
Holger

 

[Eric Penfold]

The idea is to only run the code if you paste value or values but if you delete a row or rows then if will not run.
Their are the months of the year tabs to work on the rest cannot change.
I don`t think the old row number matters just to make it work as above if that makes sense.
All changes will be by hand.
All entries below or above will stay the same.

 

[HaHoBe]

Hi Eric,

please test the following code in a new workbook - there should be a message box on each action where Column A is involved giving the cell address - it should work on multiple cells as well. Like I said: please use a new workbook, it's only to test. Code goes into ThisWorkbook, please test all options you would like to take and see if the MsgBox shows up (it did in all of my tests for one or more cells and changing or pasting values, inserting rows, deleting rows. If there is more feel free to test and report):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rngWork As Range
Dim rngTarg As Range

If Not Intersect(Target, Columns(1)) Is Nothing Then
  If Target.Cells.Count = 1 Then
    Set rngWork = Cells(Target.Row, 1)
  Else
    For Each rngTarg In Target
      If rngTarg.Column = 1 Then
        If rngWork Is Nothing Then
          Set rngWork = rngTarg
        Else
          Set rngWork = Union(rngTarg, rngWork)
        End If
      End If
    Next rngTarg
  End If
  MsgBox rngWork.Address
  Set rngWork = Nothing
End If
End Sub

And from there you would need to explain if the procedure should be called for all actions captured here and maybe even which range to work on. What shall happen if a row is deleted: work only from row 2 to the row given in the MsgBox? Same for inserting values. What shall happen if you paste or change values?

From the answer given it will depend if I would need further coding to take each single action into consideration. Fact is: the code can be restricted to some sheets only (the sheet is passed as an object by now), depending on what range to work on that information (namely the information how many rows have to be worked on).

Ciao,
Holger

 

[Eric Penfold]

This works
Also when i say paste can the code run?
As in the Group Order No Module