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

 

[HaHoBe]

Hi Eric,

insert anywhere in the code (keeping the same number of rows) or at the end exceeding the number of rows (meaning that you paste 5 values using only the last 3 of the data)? And I would need to know what should happen if you use ClearContents?

Ciao,
Holger

 

[Eric Penfold]

Hi Eric,

insert anywhere in the code (keeping the same number of rows) or at the end exceeding the number of rows (meaning that you paste 5 values using only the last 3 of the data)? Pasting would always be at the end of the list this is when the module needs to run

And I would need to know what should happen if you use ClearContents? If you did this it would not run the code

Ciao,
Holger

 

[HaHoBe]

Hi Eric,

I would prefer to get some information instead of quotes from my posting.

Holger

 

[Eric Penfold]

If you used clear contents it would not run the code.

Pasting would always be at the end of the list this is when the module needs to run.

 

[HaHoBe]

Hi Eric,

ClearContents is triggered on my version (using 2019 with Windows11). And what should happen if the command is applied to Column A? Take your time to answer as I will take some time to re-think the problem.

Again for me to understand:
adding more entries to the list - calling procedure (running on all rows)
deleting entries, shortening list - still unsure on what to do and on what rows to work
ClearContents/paste values, keeping the number of rows - calling procedure (running an all rows)

Please correct me if I got something wrong here.

Holger

 

[Eric Penfold]

The below shows the yellow rows need to be deleted but when you do it it can`t run the code which is what i need. Thing is when you delete rows or a row the rest of the sheet data must stays the same.
But if you paste values to the bottom it will run the code.

2022 Alton Back OrderT.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
131	19/10/2022	Goldmax Ltd	SO0073726	NIJ	ME6060150	Ecolite D400 600x600mm Access Chamber Section x150mm High	16	8		IS	BRE002	A			3	Underground Drainage
132	19/10/2022	Goldmax Ltd	SO0073726	NIJ	110TWBL	110mm OD Twinwall Electrical Duct x50m; BLACK	6	7		IS	BRE002	A			3	Underground Drainage
133	19/10/2022	Ground Construction Ltd	SO0073728	LR	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	1	1		IS	JFC001	A			3	Underground Drainage
134	19/10/2022	Ground Construction Ltd	SO0073728	LR	110TWBL	110mm OD Twinwall Electrical Duct x50m; BLACK	2	63		IS	ULM001	A				Drainage Channel
135	20/10/2022	Acorn Groundwork Ltd	SO0073638	LK	VFFAC6000	6" Clay/Plastic, 180-200mm - 160-180mm Flexible Adaptor Coupling	1	19		IS	HAR001	A				Underground Drainage
136	20/10/2022	Independent Building Merchants Group Ltd T/A Chandlers	SO0073682	NIJ	32WP100	MDPE Water Pipe: 32mm x 100m Coil; BLUE 12.5 bar/ PE80/ SDR11	1	13		SO	JFP001	A				Ducting
137	20/10/2022	M.O. Buckland & Son Ltd	SO0073739	LR	450VCR	450mm Inspection Chamber Shaft 300mm Height	1	747		IS	ULM001	A				Drainage Channel
138	20/10/2022	Harringtons Builders Plc	SO0073791	NIJ	RB100X6	100mm RIDGIDUCT Twinwall x6m c/w Coupler Complies with ENATS 12-24 & BS EN 61386-24 Class 2	49	1		IS	ULM001	A				Drainage Channel
139	20/10/2022	Harringtons Builders Plc	SO0073791	NIJ	8VF30	200mm S/Socket Bend 30deg	3	0		ONI		A
140	20/10/2022	Bear Construction South Ltd	SO0073821	LR	6VF90D	160mm UPVC Drainage D/Socket Bend 87.5deg	6	-1		LS	RAD002	A			1	Service Pipe
141	20/10/2022	Independent Building Merchants Group Ltd T/A Chandlers	SO0073849	LR	4VF22D	110mm UPVC Drainage D/Socket Bend 22.5deg	20	3304		LS	RAD002	A			1	Service Pipe
142	21/10/2022	A D Bly Construction Ltd	SO0072558	LK	KVDH100.10R	KompaqDrain 100mm W x 235mm D CITY PolymerChannel x1m D400	25	0		ONI	GLE001	A			3	Underground Drainage
143	21/10/2022	A D Bly Construction Ltd	SO0072558	LK	CKV100	KompaqDrain 100mm Galv Silt Bucket	4	0		ONI	GLE001	A			3	Underground Drainage
144	21/10/2022	A D Bly Construction Ltd	SO0072558	LK	AKVDH100MF10RS	KompaqDrain 100mm W x 235mm D CITY SumpAccess Unit x 1m; D400	4	0		SO	GLE001	A			3	Underground Drainage
145	21/10/2022	A D Bly Construction Ltd	SO0072558	LK	AKVD100B	KompaqDrain 100mm W x 260mm D Sump Basex0.5m; D400	4	0		ONI	GLE001	A	18/10/2022		3	Underground Drainage
146	21/10/2022	Shaca Construction Ltd	SO0073954	LK	VFFDC95	80-95mm Flexible Coupling	5	0		SO	VIP001	A	11/10/2022		3	Flexible Fittings
147	21/10/2022	Statom Group Ltd	SO0073955	LK	96BTT	96mm Telecoms Duct Equal Swept 90deg Jct Grey (BT Type)	1	5		SO	VIP001	A	11/10/2022		3	Flexible Fittings
148	21/10/2022	Statom Group Ltd	SO0073955	LK	96-54BTRC	BT Duct 96/54mm Reducer; Grey (BT Type Telecoms Duct)	2	0		IS	ULM001	A				Drainage Channel
149	21/10/2022	Statom Group Ltd	SO0073960	LK	50PD25EDF	50/60mm Polyduct x25m Coil; EDF Spec Polypipe Electric	1	0		IS	ULM001	A				Drainage Channel
150	24/10/2022	HACS Construction Limited	SO0066799	LK	TF1023	F100-400K Channel Screw	476	2026		ONI	ACO003	A			2	Drainage Channel
151	24/10/2022	HACS Construction Limited	SO0066799	LK	T300K10C	F300K 300mm W x 390mm D Blank End Cap	2	2		IS	WRE001	A	10/10/2022		3	Cover & Frame
152	24/10/2022	HACS Construction Limited	SO0066799	LK	FNX300FTFM	F300K 300mm D/Iron Slotted Grating x0.5m;	139	182		ONI	ESS001	A	11/10/2022		3	Underground Drainage
153	24/10/2022	HACS Construction Limited	SO0066799	LK	F300K00R	F300K 300mm W x 390mm D Polymer Concrete Channel x1m; F900	68	114		SO	VIP001	A	14/10/2022		3	Flexible Fittings
154	24/10/2022	HACS Construction Limited	SO0066799	LK	C300	M300 Galv. Steel Bucket	3	8		SO	PLA002	A	11/10/2022		3	Underground Drainage
155	24/10/2022	HACS Construction Limited	SO0066799	LK	AF300	F300K 300mm W x 390mm D Sump Unit x0.5m; F900	3	17		SO	VIP001	A	14/10/2022		3	Flexible Fittings
156	24/10/2022	Bartack Ltd	SO0073876	LK	32WP50	MDPE Water Pipe: 32mm x 50m Coil; BLUE 12.5 bar/ PE80/ SDR11	1	0		LS	DAV002	A	06/10/2022		3	Drainage Channel
157	24/10/2022	Kuropatwa Limited	SO0073921	RJ	25WP50	MDPE Water Pipe: 25mm x 50m Coil; BLUE 12.5 bar/ PE80/ SDR11	2	11		LS		A	04/10/2022		3
158	25/10/2022	Entex Projects Ltd	SO0073969	NIJ	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	14	104		ONI	WES002	A				Service Pipe
159	25/10/2022	Avtar Construction Ltd	SO0074134	LR	PB160	158-170mm Zinc Plated Pipe Bracket Rubber Lining - M8/M10 Thread	100	18		SL	WRE001	A				Cover & Frame
160	25/10/2022	Avtar Construction Ltd	SO0074134	LR	M10ST	M10 Threaded Bar x1m	125	257		ONI	INT001	A				Underground Drainage
161	25/10/2022	SNS Building Products Ltd	SO0074163	NIJ	D3104A	RECESSED SCREED Cover Galvanised: 300 x 300mm Sealed & Locked	3	0		IS	JFP001	A				Ducting
162	25/10/2022	Keaney Brothers Ltd	SO0074174	NIJ	225TW90	225mm ID Twinwall Duct Bend Long Radius 90deg; P/E	4	5		IS	VIP001	A				Flexible Fittings
163	25/10/2022	Shaca Construction Ltd	SO0074184	NIJ	VFFSC115	100-115mm Flexible Coupling VSC115	3	6		IS	MID003	A				Accessories
164	26/10/2022	MC Builders (Dorset) Ltd	SO0074222	LR	SF27/50	Non-Woven GP Geotextile 2.25 x 50m Roll	1	22		IS	MID003	A				Accessories
165	26/10/2022	MC Builders (Dorset) Ltd	SO0074222	LR	6VCE1069	250mm Vision 600 Chamber Spigot End Cap c/w seal	2	1		SL	WRE001	A				Cover & Frame
166	26/10/2022	MC Builders (Dorset) Ltd	SO0074222	LR	6VCA10225	250mm Vision 600 chamber, outlet adaptor to 225mm TW – Socket	2	36		ONI	INT001	A				Underground Drainage
167	26/10/2022	Tinnelly Construction Limited	SO0074305	KB	VFFSC115	100-115mm Flexible Coupling VSC115	3	6		ONI	NAY001	A				Underground Drainage
168	26/10/2022	DR Groundworks Ltd	SO0074308	NIJ	63TWB	63mm OD Twinwall Duct x50m; BLUE	4	4		IS	NAY001	A				Ducting
169	26/10/2022	DR Groundworks Ltd	SO0074308	NIJ	32WP50	MDPE Water Pipe: 32mm x 50m Coil; BLUE 12.5 bar/ PE80/ SDR11	2	12		SO	NAY001	A				Ducting
170	26/10/2022	Statom Group Ltd	SO0074329	LK	150RTWPY	150mm Twinwall Duct x6m PERFORATED; YELLOW c/w coupler (178mm)	11	0		IS	WES002	A				Service Pipe
171	27/10/2022	Galamast Ltd	SO0074365	LK	225RTWB	225mm ID Twinwall Duct x6m; BLUE; P/E	1	0		ONI	RAD001	A				Ducting
172	27/10/2022	O'Halloran & O'Brien Ltd	SO0074379	LK	60PGD50	PERFORATED Gas Duct: 60mm x 50m Coil; YELLOW	6	1		SO	NAY001	A				Ducting
173	27/10/2022	O'Halloran & O'Brien Ltd	SO0074379	LK	110TWB	110mm OD Twinwall Duct x50m; BLUE	1	0		ONI	NAY001	A				Ducting
174	27/10/2022	Viking Projects (UK) Ltd	SO0074398	LR	110TWB	110mm OD Twinwall Duct x50m; BLUE	1	0		ONI	NAY001	A				Ducting
175	28/10/2022	O'Halloran & O'Brien Ltd	SO0074433	LK	6VCE1069	250mm Vision 600 Chamber Spigot End Cap c/w seal	1	721		SO		A
176	28/10/2022	O'Halloran & O'Brien Ltd	SO0074433	LK	6VCA10225	250mm Vision 600 chamber, outlet adaptor to 225mm TW – Socket	3	39		IS		A
177	28/10/2022	Erith Contractors Limited	SO0074450	RJ	VFFDC65	50-65mm Flexible Coupling	1	5		IS		A
178	28/10/2022	McKenna Consulting Ltd	SO0074452	GW	TEUROKITC	EUROKIT 100mm W x 85mm D Blank End Cap	1	444		IS		A
179	28/10/2022	R Moulding & Co (Salisbury) Ltd	SO0074486	LK	JMF104	QuadBox Access Chamber 915x445x150mm BT Approved	3	4		LS		A
180	28/10/2022	Leidon Limited	SO0074511	NIJ	25PTN50	PURITON Barrier Pipe: 25mm x 50m Coil; Type A SDR11	1	6		LS		A
181	28/10/2022	Newlands Construction Ltd	SO0074514	LK	25WP100	MDPE Water Pipe: 25mm x 100m Coil; BLUE 12.5 bar/ PE80/ SDR11	1	0		ONI		A
182	31/10/2022	Red Hill Construction	SO0074506	LR	LIB100S	Polymer Concrete Sump Unit Driveway Duty c/w Galvanised A15 Grating	4	4		SO		A
Oct

Hope this makes it clear?

 

[HaHoBe]

Hi Eric,

how do you delete these rows: by selecting the entire row via row header, which command do you apply: shortcut CTRL&-, Delete rows from the Ribbon menu or Delete Rows from the Contextual menu (all of which fire on my system)? Or will you just work with single cells on Column A? My actual concept for building the range to further work on takes care of both of these scenarios. And the procedure to be called: on which range shall it work: the entire range, range starting with first row of change to the end or just to the end of the change?

Sorry for all the questions.

From what I see in your data and all the problems I have getting a working code: have you considered to w ork witha UserForm? You can restrict the dates in a combobox or listbox, have only the entires listed for the dates chosen, or search for a match from Column C, have only entries listed for dates who have more than one SO... from Column C on one day.... And you can start a procedure from there stating to delete an entry or add new data.

Ciao,
Holger

 

[Eric Penfold]

Yes selecting all rows via row header. The user will highlight the row or rows then delete.
As to User Form I have plans to do this but at the minute people are on my back to get this done.

 

[HaHoBe]

Hi Eric,

from #24 which may be found in the opening code as well

Pasting would always be at the end of the list this is when the module needs to run.

From #26

The below shows the yellow rows need to be deleted but when you do it it can`t run the code which is what i need.

Sorry but it seems to be impossible for me to figure out what event should be catched and get the procedure to work on which rows.

Ciao,
Holger