End of column text vba

[ash59]

I have an convoluted macro that takes 3 columns of info from one sheet, rearranges it on the same sheet, cuts it and then dumps into another sheet. I am not that confident with VBA so the second sheet has a straight copy from the first, including 3 columns down to row 671. The problem that I have is that the written data could be anything from 5 rows to 671. If it is less than 671 ctrl end goes to row 671 even though there is no written data in it. How do I set the spreadsheet, via vba, so that ctrl end goes to the end of the written data?

 

[Joe4]

Welcome to the Board!

You could try going to the end of one the columns, and doing a CTRL+Up Arrow from there.
In VBA code, it would look like the following (for column A):

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

 

[ash59]

Welcome to the Board!

You could try going to the end of one the columns, and doing a CTRL+Up Arrow from there.
In VBA code, it would look like the following (for column A):

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

Thanks, tried that manually but Ctrl up takes me to the top of the column, not the to the bottom of the text so if the vba matches the manual method, then it will not work either. Need to some how find the blanks and delete them but not sure how to define them when the number of rows can vary.

 

[ash59]

Thanks, tried that manually but Ctrl up takes me to the top of the column, not the to the bottom of the text so if the vba matches the manual method, then it will not work either. Need to some how find the blanks and delete them but not sure how to define them when the number of rows can vary.

The annoying thing is that if you took a new spreadsheet, entered info to A1 to A10 and the copied A1 to A20 to another row, Ctrl down works perfectly and takes you to the last text entry.

 

[Joe4]

Thanks, tried that manually but Ctrl up takes me to the top of the column, not the to the bottom of the text so if the vba matches the manual method, then it will not work either. Need to some how find the blanks and delete them but not sure how to define them when the number of rows can vary.

The only way I know that would happen is if the entirety of column A is blank (except maybe for row 1), or it is full (formula or formatting applied to every single row in column A).

Are any of these situations going on?

Or perhaps you are running this at the wrong time or against the wrong sheet, and there is no data in column A at the point this code is being run.
You should be able to verify this by stepping through your code as you watch what is happening to the sheets at that point of the code.

 

[ash59]

The only way I know that would happen is if the entirety of column A is blank (except maybe for row 1), or it is full (formula or formatting applied to every single row in column A).

Are any of these situations going on?

Or perhaps you are running this at the wrong time or against the wrong sheet, and there is no data in column A at the point this code is being run.
You should be able to verify this by stepping through your code as you watch what is happening to the sheets at that point of the code.

Thanks again for the reply. I have the sheet in front of me. There is written info in cells C1 to C344. If, on C1, I ctrl down, it goes to C691. If I go ctrl up from here, it goes to C1. There are no formulas and the format is set to general.

1961 G F Gordon 202108.xlsm
	C	D	E
1	25kg Ordinary Portland Cement	OPC099B	3.54
2	25Kg Sulphate Resisting Cement - Paper Bags	OPC001LSR	4.6
3	25Kg WHITE Ordinary Portland Cement - Paper Bags	OPC001W	7.34
4	25kg Extra Rapid Cement - Plastic Bags	OPC001RER	13.49
5	20Kg Bag of Postmix Professional	OPC001PCP	4.77
6	25kg SikaLevel 25 Latex Self Levelling	EV-SKLEV25LAT25	14.96
7	25kg Bag GP Non-Shrink Grout	NSG25	9.52
8	BLACK MACADAM 25KG	25MAC	5.88
9	Instarmac 6mm road repair 25kg Tubs (Green Lid)	INST6MAC	20.15
10	25kg Bags of Granite 6mm - Dust	DUST002	3.74
11	Joint Filling Agg 1- 4mm - 25kg (Grey)	GRIT3	6.02
12	Bag Of Kiln Dried Sand	KDSAND	2.21
13	25Kg Bagged Aggregate - 10MM SHINGLE	25SHIN10	2.11
14	25Kg Bagged Aggregate - 20MM ALL IN BALLAST	25BALLAST	2.11
15	25kg Bagged Aggregate - 20mm Shingle	25SHING20	2.11
16	25Kg Bagged Aggregate - BUILDING SAND	25BSAND	2.11
17	25Kg Bagged Aggregate - SHARP WASHED SAND	25SSAND	2.11
18	25Kg Bagged Aggregate - Type 1	25TYPE1	2.4
19	BULK BAG 2 - 6MM GRIT	6MMGRIT	69.19
20	Bulk Bag Aggregate - 10MM SHINGLE	SHIN1	42.3
21	Bulk Bag Aggregate - 20MM SHINGLE	SHIN2	42.3
22	Bulk Bag Aggregate - BALLAST 20mm	BALLAST	42.3
23	Bulk Bag Aggregate - BUILDING SAND	BSAND	42.3
24	Bulk Bag Aggregate - SHARP WASHED SAND	WSAND	42.3
25	Bulk Bag Aggregate - TYPE1	TYPE1	42.3
26	Bulk Bag of Topsoil	TOPSOIL	62.99
27	125mm x 255mm Half Battered (HB2) Straight Kerb 914mm	KRB001	5.51
28	125mm x 255mm Half Battered (HB2) External Radius - 1 Metre	KRB035	9.07
29	125mm x 255mm Half Battered (HB2) External Radius - 2 Metre	KRB036	9.07
30	125mm x 255mm Half Battered (HB2) Internal Radius - 2 Metre	KRB079	9.07
31	125mm x 255mm Half Battered (HB2) External Radius - 3 Metre	KRB037	9.07
32	125mm x 255mm Half Battered (HB2) Internal Radius - 3 Metre	KRB045	9.15
33	125mm x 255mm Half Battered (HB2) External Radius - 4 Metre	KRB038	9.15
34	125mm x 255mm Half Battered (HB2) Internal Radius - 4 Metre	KRB046	9.15
35	125mm x 255mm Half Battered (HB2) External Radius - 5 Metre	KRB117	9.15
36	125mm x 255mm Half Battered (HB2) External Radius - 6 Metre	KRB039	9.15
37	125mm x 255mm Half Battered (HB2) Internal Radius - 6 Metre	KRB047	9.07
38	125mm x 255mm Half Battered (HB2) External Radius - 8 Metre	KRB040	9.07
39	125mm x 255mm Half Battered (HB2) Internal Radius - 8 Metre	KRB048	9.07
40	125mm x 255mm Half Battered (HB2) External Radius - 10 Metre	KRB042	9.07
41	125mm x 255mm Half Battered (HB2) Internal Radius - 10 Metre	KRB080	9.07
42	125mm x 255mm Half Battered (HB2) External Radius - 15 Metre	KRB043	9.07
43	125mm x 255mm Half Battered (HB2) Internal Radius - 15 Metre	KRB081	9.07
44	125mm x 255mm Half Battered (HB2) to Bullnosed 150mm (BN3) Dropper - Left Hand DL1	KRB015	9.07
45	125mm x 255mm Half Battered (HB2) to Bullnosed 150mm (BN3) Dropper - Right Hand DR1	KRB014	9.07
46	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Left Hand - 4 Metre DL1	KRB059	19.24
47	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Right Hand - 4 Metre DR1	KRB060	19.24
48	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Left Hand - 6 Metre DL1	KRB061	19.24
49	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Right Hand - 6 Metre DR1	KRB062	19.24
50	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Left Hand - 8 Metre DL1	KRB077	19.24
51	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Right Hand - 8 Metre DR1	KRB063	19.24
52	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Left Hand - 10 Metre DL1	KRB065	19.24
53	125mm x 255mm/150mm Half Battered (HB2) to Bullnosed (BN3) Radius Dropper Right Hand - 10 Metre DR1	KRB064	19.24
54	125mm x 255mm Half Battered (HB2) Angle - External HBXA	KRB021	17.34
55	125mm x 255mm Half Battered (HB2) Angle - Internal HBIA	KRB020	17.34
56	305mm x 255mm Half Battered (HB2) Quadrant QHB	KRB026	18.65
57	455mm x 255mm Half Battered (HB2) Quadrant QHB	KRB027	21.79
58	125mm x 255mm Bullnosed (BN2) Straight Kerb 914mm	KRB005	5.44
59	125mm x 255mm Bullnose (BN2) External Radius - 1 Metre	KRB242	9.07
60	125mm x 255mm Bullnosed (BN2) External Radius - 2 Metre	KRB049	9.07
61	125mm x 255mm Bullnosed (BN2) External Radius - 3 Metre	KRB050	9.07
62	125mm x 255mm Bullnosed (BN2) External Radius - 4 Metre	KRB051	9.07
63	125mm x 255mm Bullnosed (BN2) External Radius - 5 Metre	KRB228	9.07
64	125mm x 255mm Bullnosed (BN2) External Radius - 6 Metre	KRB052	9.07
65	125mm x 255mm Bullnosed (BN2) External Radius - 8 Metre	KRB053	9.07
66	125mm x 255mm Bulllnosed (BN2) External Radius - 10 Metre	KRB055	9.07
67	125mm x 255mm *Bullnosed* (BN2) to Bullnosed 150mm (BN3) Dropper - Left Hand	KRB016	9.07
68	125mm x 255mm *Bullnosed* (BN2) to Bullnosed 150mm (BN3) Dropper - Right Hand	KRB017	9.07
69	125mm x 255mm Bullnosed (BNIA) Angle - Internal	KRB022	18.65
70	125mm x 255mm Bullnosed (BNXA) Angle - External	KRB023	18.65
71	305mm x 255mm Bullnosed (BN) Quadrant QBN	KRB028	18.65
72	125mm x 255mm Bullnosed (BN2) to Half Battered (HB2) Transition Kerb - Right Hand	KRB007	18.6
73	125mm x 255mm Half Battered (HB2) to Bullnose (BN2) Transition Kerb - Left Hand	KRB006	18.6
74	125mm x 150mm Bullnosed (BN3) Straight 914mm	KRB012	4.53
75	125mm x 150mm Bullnosed (BN3) External Radius - 1 Metre	KRB065A	7.44
76	125mm x 150mm Bullnosed (BN3) External Radius - 2 Metre	KRB066	7.44
77	125mm x 150mm Bullnosed (BN3) External Radius - 3 Metre	KRB084	7.44
78	125mm x 150mm Bullnosed (BN3) Internal Radius - 3 Metre	KRB071	7.44
79	125mm x 150mm Bullnosed (BN3) External Radius - 4 Metre	KRB067	7.44
80	125mm x 150mm Bullnosed (BN3) Internal Radius - 4 Metre	KRB231	7.44
81	125mm x 150mm Bullnosed (BN3) External Radius - 5 Metre	KRB067A	7.44
82	125mm x 150mm Bullnosed (BN3) External Radius - 6 Metre	KRB068	7.44
83	125mm x 150mm Bullnosed (BN3) Internal Radius - 6 Metre	KRB233	7.44
84	125mm x 150mm Bullnosed (BN3) External Radius - 8 Metre	KRB069	7.44
85	125mm x 150mm Bullnosed (BN3) Internal Radius - 8 Metre	KRB234	7.44
86	125mm x 150mm Bullnosed (BN3) External Radius - 10 Metre	KRB070	7.44
87	125mm x 150mm Bullnosed (BN3) Internal Radius - 10 Metre	KRB106	7.44
88	125mm x 150mm Bullnosed (BN3) External Radius - 15 Metre	KRB070A	7.44
89	125mm x 255mm Half Battered (LH) to Splay Transition Kerb (RH)	KRB008	18.6
90	125mm x 255mm Half Battered (RH) to Splay Transition Kerb (LH)	KRB076	18.6
91	125mm x 255mm Splayed (SP2) Straight Kerb 914mm	KRB004	5.44
92	125mm x 255mm/150mm Splayed (SP2) to Bullnosed (BN3) Dropper - Left Hand	KRB018	10.49
93	125mm x 255mm/150mm Splayed (SP2) to Bullnosed (BN3) Dropper - Right Hand	KRB019	10.49
94	125mm x 150mm Square Channel CS2	KRB010	4.62
95	125mm x 255mm Dished Channel	KRB011	10
96	125mm x 255mm Square Channel CS1	KRB009	6.18
97	50mm x 150mm x 914mm Path Edging - ****** Bullnosed EBN ******	KRB029	2.17
98	50mm x 150mm x 914mm Path Edging - Flat Top EF	KRB030	2.18
99	50mm x 205mm* x 914mm Path Edging - Flat Top EF	KRB032	5.68
100	50mm x 255mm** x 914mm Path Edging - Flat Top EF	KRB033	6.27
101	600mm x 600mm x 50mm Natural Grey Paving B50	PAV082	4.76
102	750mm x 600mm x 50mm Natural Grey Paving C50	PAV083	6.05
103	900mm x 600mm x 50mm Natural Grey Paving D50	PAV084	5.99
104	400mm x 400mm x 50mm Chamferred Edge Natural Grey Paving	PAV087	3.57
105	400 x 400 x 50mm Cycleway Paving - Buff (Sq Edge Lines)	PAV202CB	6.279
106	400 x 400 x 50mm Cycleway Paving - Natural (Sq Edge Lines)	PAV201CN	5.397
107	400mm x 400mm x 50mm Hazard Corduroy Paving - Buff	PAV204	5.7645
108	400mm x 400mm x 50mm Hazard Corduroy Paving - Natural	PAV203	5.817
109	400mm x 400mm x 50mm Tactile Blister Paving - Buff	PAV077	5.7225
110	400mm x 400mm x 50mm Tactile Blister Paving - Red	PAV076	5.67
111	400mm x 400mm x 65mm Tactile Blister Paving - Buff	PAV077F	8.6625
112	450mm x 450mm x 50mm Natural Grey Paving - Square Edged	PAV079	4.7145
113	450mm x 450mm x 50mm Tactile Blister Paving - Buff	PAV078	9.7965
114	450mm x 450mm x 50mm Tactile Blister Paving - Red	PAV078R	9.7965
115	900mm Diameter x 250mm High Concrete Chamber Ring - NO STEPS	CMB121	52.27
116	900mm Diameter x 500mm High Concrete Chamber Ring - NO STEPS	CMB122	78.02
117	900mm Diameter x 750mm High Concrete Chamber Ring - NO STEPS	CMB123	78.02
118	900mm Diameter x 1000mm High Concrete Chamber Ring - NO STEPS	CMB124	104.03
119	900mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB125	95.79
120	1050mm Diameter x 250mm High Concrete Chamber Ring - Including Double Steps	CMB126	59.61
121	1050mm Diameter x 500mm High Concrete Chamber Ring - Including Double Steps	CMB127	78.95
122	1050mm Diameter x 750mm High Concrete Chamber Ring - Including Double Steps	CMB129	103.27
123	1050mm Diameter x 1000mm High Concrete Chamber Ring - Including Double Steps	CMB130	125.91
124	1050mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB131	95.04
125	1200mm Diameter x 250mm High Concrete Chamber Ring - Including Double Steps	CMB132	69.52
126	1200mm Diameter x 500mm High Concrete Chamber Ring - Including Double Steps	CMB133	95.38
127	1200mm Diameter x 750mm High Concrete Chamber Ring - Including Double Steps	CMB134	119.31
128	1200mm Diameter x 1000mm High Concrete Chamber Ring - Including Double Steps	CMB135	164.73
129	1200mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB136	125.34
130	1200mm Diameter Heavy Duty Cover Slab - 675mm x 675mm Access	CMB136B	125.34
131	1200mm Diameter Heavy Duty Cover Slab - 750mm x 600mm Access	CMB136A	125.34
132	1350mm Diameter x 500mm High Concrete Chamber Ring - Including Double Steps	CMB145	143.25
133	1350mm Diameter x 750mm High Concrete Chamber Ring - Including Double Steps	CMB146	186.22
134	1350mm Diameter x 1000mm High Concrete Chamber Ring - Including Double Steps	CMB147	248.28
135	1350mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB148	202.93
136	1350mm Diameter Heavy Duty Cover Slab - 675mm x 675mm Access	CMB148B	202.93
137	1350mm Diameter Heavy Duty Cover Slab - 750mm x 600mm Access	CMB148A	202.93
138	1500mm Diameter x 500mm High Concrete Chamber Ring - Including Double Steps	CMB137	160.95
139	1500mm Diameter x 750mm High Concrete Chamber Ring - Including Double Steps	CMB138	205.31
140	1500mm Diameter x 1000mm High Concrete Chamber Ring - Including Double Steps	CMB139	255.45
141	1500mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB140	248.28
142	1500mm Diameter Heavy Duty Cover Slab - 675mm x 675mm Access	CMB140675	248.28
143	1500mm Diameter Heavy Duty Cover Slab - 750mm x 600mm Access	CMB140A	257.84
144	1800mm Diameter x 500mm High Concrete Chamber Ring - Including Double Steps	CMB175	204.13
145	1800mm Diameter x 750mm High Concrete Chamber Ring - Including Double Steps	CMB176	261.42
146	1800mm Diameter x 1000mm High Concrete Chamber Ring - Including Double Steps	CMB177	349.76
147	1800mm Diameter Heavy Duty Cover Slab - 600mm x 600mm Access	CMB179	315.13
148	375mm Diameter x 750mm Deep x 150mm Outlet Concrete Road Gully	GUL144	53.13
149	450mm Diameter x 750mm Deep x 150mm Outlet Concrete Road Gully	GUL145	54.91
150	450mm Diameter x 900mm Deep x 150mm Outlet Concrete Road Gully	GUL146	57.3
151	450mm Diameter x 1050mm Deep x 150mm Outlet Concrete Road Gully	GUL147	60.56
152	Naylor Gully Connection pipe 160mm dia x 25m	NPGULLYCON1	125.55
153	600mm x 450mm x 150mm Concrete Rectangular Inspection Chamber Section	DIC149	14.78
154	600mm x 450mm x 225mm Concrete Rectangular Inspection Chamber Section	DIC150	19.93
155	750mm x 600mm x 150mm Concrete Rectangular Inspection Chamber Section	DIC154	22.74
156	750mm x 600mm x 225mm Concrete Rectangular Inspection Chamber Section	DIC155	26.22
157	750mm x 600mm Light Duty Concrete Cover Slab - 600mm x 450mm Access	DIC156	31.14
158	1000mm x 675mm x 150mm Concrete Rectangular Inspection Chamber Section	DIC158	30.68
159	1000mm x 675mm x 225mm Concrete Rectangular Inspection Chamber Section	DIC159	35.88
160	1000mm x 675mm Light Duty Concrete Cover Slab - 600mm x 450mm Access	DIC160	53.06
161	1200mm x 750mm x 150mm Concrete Rectangular Inspection Chamber Section	DIC162	37.82
162	1200mm x 750mm x 225mm Concrete Rectangular Inspection Chamber Section	DIC162/	50.6
163	1200mm x 750mm Light Duty Concrete Cover Slab - 600mm x 600mm Access	DIC165	78.46
164	1200mm x 750mm Heavy Duty Concrete Cover Slab - 600mm x 600mm Access	DIC166	153.22
165	Caswick Galv Double Encapsulated Step Iron	SS402	11.95
166	600mm POLO Precast TYPE 2 Concrete Seating Rings	SEAT60	24.99
167	600mm Type 1 Square Precast Concrete Seating Rings - Eccentric	SEAT60E	32.15
168	675mm Square Precast Concrete Seating Rings	SEAT67	22.77
169	750 x 600mm Precast Concrete Seating Rings	SEAT75	24.99
170	750mm x 650mm x 100mm square Gully raising Slab - 450mm Dia Access	GUL149	21.66
171	U Shaped Gully Raising Piece, 100m Deep	GUL154	21.66
172	610x100x12mm SHIMPAC	SHIMP12	2.21
173	610x100x24mm SHIMPAC	SHIMP24	4.36
174	610x100x36mm SHIMPAC	SHIMP36	6.39
175	200mm x 100mm x 60mm Omega Block Paving - Brindle	OM60BR	15.13
176	200mm x 100mm x 60mm Omega Block Paving - Charcoal	OM60CL	15.13
177	450mm x 450mm x 32mm Bradstone Peak Riven Paving - Buff	PEAK45B	2.31
178	450mm x 450mm x 32mm Bradstone Peak Riven Paving -*Natural *	PEAK45N	2.31
179	600mm x 600mm x 32mm Bradstone Peak Riven Paving - Buff	PEAK60B	4.81
180	600mm x 600mm x 32mm Bradstone Peak Riven Paving - *Natural*	PEAK60N	5.05
181	450mm x 450mm x 32mm Bradstone Textured Paving - Buff	TEXT45B	4.03
182	450mm x 450mm x 32mm Bradstone Textured Paving - *Natural*	TEXT45N	4.03
183	600mm x 600mm x 35mm Bradstone Textured Paving - Buff	TEXT60B	7.19
184	600mm x 600mm x 35mm Bradstone Textured Paving - *Natural*	TEXT60N	7.19
185	450mm x 450mm x 32mm Chaucer Paving - Buff	CHAU001	7.19
186	BOX 225mm Stainless Steel Type 4 Housing Ties BBA and Part E Type A (250 per box)	VE4-225-STST	30.23
187	BOX 250mm Stainless Steel Type 4 Housing Ties BBA and Part E Type A (250 Per Box)	VE4-250-STST	41.47
188	100mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC100	2.29
189	150mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC150	3.43
190	225mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC225	5.42
191	300mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC300	7.22
192	450mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC450	10.82
193	600mm Wide x 30 Metre Roll Polythene Damp Proof Course	DPC600	14.42
194	900mm Wide X 30 Metre Roll Polythene Damp Proof Course	DPC900	21.64
195	4 Metre Wide x 25 Metre Roll 300MU Polythene Damp Proof Membrane	DPM300	40.14
196	4 Metre Wide x 12.5 Metre Roll 500MU Polythene Damp Proof Membrane	DPM500	38.23
197	Builders Black PVC Tape 100x33mtr	EV111	6.94
198	Telescopic Void Vent	1201	2.1
199	Vertical Extension Piece (To Suit Telescopic Vent)	1204	2.32
200	9" x 3" UPVC Airbrick - Black	1201ABBL	0.62
201	9" x 3" UPVC Airbrick - Buff	1201ABBU	0.51
202	9" x 3" UPVC Airbrick - Terracotta	1201ABTC	0.51
203	Timloc BUFF Weep Vents	TIM1143B	0.48
204	Timloc RED Weep Vents	TIM1143R	0.47
205	Timloc Concealed Wall Weep Vent Natural	TW1CL	0.47
206	100 x 65 x 600mm Long Prestressed Concrete Lintel	PSCL600	2.42
207	100 x 65 x 750mm Long Prestressed Concrete Lintel	PSCL750	3.01
208	100 x 65 x 900mm Long Prestressed Concrete Lintel	PSCL900	3.61
209	100 x 65 x 1200mm Long Prestressed Concrete Lintel	PSCL1200	4.82
210	140 x 65 x 600mm Long Prestressed Concrete Lintel	PSCL600140	3.61
211	140 x 65 x 900mm Long Prestressed Concrete Lintel	PSCL900140	5.42
212	25mm Diameter x 100 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM180	52.92
213	25mm Diameter x 25 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM178	17.01
214	25mm Diameter x 50 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM179	26.46
215	32mm Diameter x 25 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM197	28.35
216	32mm Diameter x 50 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM198	46.62
217	32mm Diameter x 100 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM181.	85.05
218	50mm Diameter x 50 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM199	102.98
219	63mm Diameter x 50 Metre Blue Medium Density Polyethylene (MDPE) Water Pipe	WTM201	166.42
220	94mm (I.D) x 110mm (O.D.) 6m Length Twinwall Duct c/w Coupling - Orange "SL"	29085	12.8
221	94mm (I.D) x 110mm (O.D.) x 6 Metre Length Twinwall Ducting c/w Coupling - BLACK	29120	12.8
222	94mm (I.D) x 110mm (O.D.) x 6 Metre Length Twinwall Ducting c/w Coupling - BLUE	29110	12.8
223	94mm (I.D) x 110mm (O.D.) x 6 Metre Length Twinwall Ducting c/w Coupling - GREEN	29281	12.8
224	94mm (I.D) x 110mm (O.D.) x 6 Metre Length Twinwall Ducting c/w Coupling - PURPLE	29043	12.8
225	94mm (I.D) x 110mm (O.D.) x 6 Metre Length Twinwall Ducting c/w Coupling - YELLOW	29045	12.8
226	94mm (I.D) x 110mm (O.D.) x 6m Length Twinwall Duct c/w Coupling - Orange "TS"	29084	12.8
227	94mm/110mm Diameter Twinwall Duct Coupling	29225	12.8
228	94mm (I.D) x 110mm (O.D.) Twinwall Duct Bend - 11.25 Degree	29144	8.48
229	94mm (I.D) x 110mm (O.D.) Twinwall Duct Bend - 22.5 Degree	29141	8.48
230	94mm (I.D) x 110mm (O.D.) Twinwall Duct Bend - 45 Degree	29138	8.48
231	94mm (I.D) x 110mm (O.D.) Twinwall Duct Bend - 90 Degree	29149	8.48
232	125mm x 6m Ridgiduct RB125 c/w Coupler (ENATS)	RB125	22.79
233	125mm Black RB125 R-Duct Coupler (ENATS)	RB125C	10.55
234	125mm x 45deg RB125 R-Duct Bend 0.6 Rad (ENATS)	RB125B45	10.34
235	125mm x 90deg RB125 R-Duct Bend 0.6 Rad (ENATS)	RB125B90	10.34
236	125mm/145mm Diameter Twinwall Duct Coupling	29100	3.6
237	125mm (I.D) x 145mm (O.D.) Black Twinwall Duct Bend - 11.25 Degree	29243	10.43
238	125mm (I.D) x 145mm (O.D.) Black Twinwall Duct Bend - 22.5 Degree	29165	10.43
239	125mm (I.D) x 145mm (O.D.) Black Twinwall Duct Bend - 45 Degree	29166	10.43
240	125mm (I.D) x 145mm (O.D.) Black Twinwall Duct Bend - 90 Degree	29156	10.43
241	150/178mm x 6 Metre Length METROPOWER Duct ENATS c/w coupling - BLACK	29716	24.99
242	150/178mm x 6 Metre Length METRO Twinwall Ducting c/w Coupling - BLUE	29355	24.99
243	150/178mm x 6 Metre Length METRO Twinwall Ducting c/w Coupling - YELLOW	29358	24.99
244	150/178mm Diameter Twinwall Duct Coupling	29098	4.95
245	150mm (I.D) x 178mm (O.D.) METRO Twinwall Duct Bend - 11.25 Degree	29262	10.97
246	150mm (I.D) x 178mm (O.D.) METRO Twinwall Duct Bend - 22.5 Degree	29183	10.97
247	150mm (I.D) x 178mm (O.D.) METRO Twinwall Duct Bend - 45 Degree	29163	10.97
248	150mm (I.D) x 178mm (O.D.) METRO Twinwall Duct Bend - 90 Degree	29162	10.97
249	225mm (266) Dia x 6mtr Length METRO Twinwall Duct Plain Ended - Blue	29413	68.14
250	225mm (266) Dia x 6mtr Length METRO Twinwall Duct Plain Ended - Yellow	29411	68.14
251	300mm (353) Dia x 6mtr Length METRO Twinwall Duct Plain Ended - Blue	29414	93.86
252	300mm (353) Dia x 6mtr Length METRO Twinwall Duct Plain Ended - Yellow	29412	93.86
253	Modular Access Chambers 300x300x330mm HIGH	NACH33	28.74
254	Modular Access Chambers 300x450x330mm HIGH	NACH34	31.65
255	Modular Access Chambers 450x450x330mm HIGH	NACH44	36.73
256	Modular Access Chambers 450x600x330mm HIGH	NACH46	40.39
257	Modular Access Chambers 600x600x330mm HIGH	NACH66	44.05
258	300 x 300mm COMPOSITE Inspection Chamber Cover & Galv Frame 12.5glvw	ECOM288AST	55.42
259	A15 450 x 450mm COMPOSITE Solid Top Access Cover & Frame	GR022	34.57
260	A15 600mm x 450mm COMPOSITE Solid Top Access Cover & Frame	GR020	30.21
261	A15 600 x 600mm COMPOSITE Solid Top Access Cover & Frame	GR021	50.16
262	B125 450 x 450mm COMPOSITE Inspection Chamber Cover & Frame	ECOM289ST	61.28
263	B125 600 x 450mm COMPOSITE Inspection Chamber Cover & Frame	ECOM290ST	87.8
264	B125 600 x 600mm COMPOSITE Inspection Chamber Cover & Frame	ECOM291ST	103.75
265	Quadbox 1310 x 610 x 150mm deep 106 Sections	JMF106	102.82
266	Quadbox 915 x 445 x 150mm deep 104 Sections	JMF104	62.91
267	1310x610mm c/o Concrete Infilled Cover & Frame Badged 'BT' B125	EN6	178.99
268	915x445mm c/o Concrete Infilled Cover & Frame Badged 'BT' B125	EN4	128.56
269	JMF DROP ON STEP FOR QUADBOX	BT4STEP	12.72
270	JMF Modula Joint Box 1 Kit (2 DROP ON WALL BRACKETS ONLY) FOR QUADBOX	BT4KITM	21.32
271	JMF Modular Joint Box 104 Kit complete with bearers for QUADBOX	JMFWB2	21.32
272	50mm/63mm Diameter Twinwall Duct Coupling	29104	1.04
273	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - BLACK	29113	42.26
274	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - BLUE	29039	42.53
275	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - GREEN	29094	42.53
276	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - ORANGE T/S	29090	42.53
277	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - PURPLE	29279	42.53
278	50mm (I.D) x 63mm (O.D.) x 50 Metre Twinwall Ducting Coil - YELLOW	29040	40.49
279	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - BLACK	29267	101.2
280	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - BLUE	29049	101.2
281	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - GREEN	29171	107.27
282	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - ORANGE S/L	29089	107.27
283	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - ORANGE T/S	29088	107.27
284	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - PURPLE	29280	107.27
285	94mm (I.D) x 110mm (O.D.) x 50 Metre Twinwall Ducting Coil - YELLOW	29050	107.27
286	137/160mm Dia x 50mtr Coil Twinwall Duct - Black Electric	29092	191.33
287	137/160mm Dia Twinwall Duct Coupling - Black	29099	5.04
288	32/37mm Diameter x 100 Metre Coil Smooth Black Electric Duct	POLY32	85.28
289	38/44mm Diameter x 100 Metre Coil Smooth Black Electric Duct	POLY38	100.57
290	Communications below 365mtr Roll Marker Tape, Green	MTC	15.88
291	CCTV 365mtr Roll Marker Tape, Green	MTCC	15.88
292	Electric 365mtr Roll Marker Tape, Yellow	MTE	15.88
293	Fibre Optic 365mtr Roll Marker Tape, Green	MTFO	15.88
294	Gas 365mtr Roll Marker Tape, Yellow	MTG	15.88
295	Sewer 365mtr Roll Marker Tape, Red	MTS	15.88
296	Street Lighting 365mtr Roll Marker Tape, Yellow	MTSL	15.88
297	Telephone 365mtr Roll Marker Tape, Green	MTT	15.88
298	Traffic Signal 365mtr Roll Marker Tape, Yellow	MTTS	15.88
299	Water 365mtr Roll Marker Tape, Blue	MTW	15.88
300	UK Power Networks Electric Tapetile, Red/Brown, *200mm* x 40mtr	TT200UKPN	61.93
301	4.5 Metre Wide x 100 Metre Roll Terram 1000 Geotextile - Non-Woven	GEO173	219.77
302	4.5Metre Wide x 100Metre Long Geotextile - Woven Black	GEO178	106.44
303	4.5 Mtr Wide x 100 Mtr Roll Mactex 1000 Non-Woven White Geotextile	GEO188	193.01
304	150mm Diameter x 6 Metre Plain Ended Black Twinwall Pipe 'BBA'	RD150X6PE/1	23.43
305	150mm Dia 'BBA' Twinwall Sealing Ring	SRD150	2
306	150mm Diameter Twinwall Coupling 'BBA' DN150/172	CRD150	3.32
307	150mm Diameter Twinwall D/S Bend - 30 Degree 'BBA'	BRD150X30	20.11
308	150mm Diameter Twinwall D/S Bend - 15 Degree 'BBA'	BRD150X15	20.11
309	150mm Diameter Twinwall D/S Bend - 45 Degree 'BBA'	BRD150X45	20.11
310	150mm Diameter Twinwall D/S Bend - 90 Degree 'BBA'	BRD150X87.5	20.11
311	150 x 150 Twinwall Socketed Junction - 45 Degree 'BBA'	JRD150100Y	33.21
312	150 x 150 Twinwall Socketed Junction - 90 Degree 'BBA'	JRD150150T	31.63
313	150-100mm Metrodrain Level Invert Reducer	ARD150100	28
314	225mm Diameter x 6 Metre Plain Ended Black Twinwall Pipe 'BBA'	RD225X6PE/1	49.28
315	225mm Dia 'BBA' Twinwall Sealing Ring	SRD225	3.73
316	225mm Diameter Twinwall Coupling 'BBA'	CRD225	5.55
317	225mm Diameter Twinwall D/S Bend - 11.25 Degree 'BBA'	BRD225X11.25/1	39.93
318	225mm Diameter Twinwall D/S Bend - 22.5 Degree 'BBA'	BRD225X22.5/1	39.93
319	225mm Diameter Twinwall D/S Bend - 45 Degree 'BBA'	BRD225X45/1	39.93
320	225mm Diameter Twinwall D/S Bend - 90 Degree 'BBA'	BRD225X90/1	39.93
321	225 x 150 Twinwall Socketed Junction - 45 Degree 'BBA'	JRD225150Y	60.04
322	225 x 150 Twinwall Socketed Junction - 90 Degree 'BBA'	JRD225150T/1	60.04
323	225 x225 Twinwall Socketed Junction - 45 Degree 'BBA'	JRD225225Y	91
324	225 x225 Twinwall Socketed Junction - 90 Degree 'BBA'	JRD225225T/1	91
325	225-150mm Metrodrain Level Invert Reducer	ARD225150	39.3
326	300mm Diameter x 6 Metre Plain Ended Black Twinwall Pipe 'BBA'	RD300X6PE/1	77.23
327	300mm Dia 'BBA' Twinwall Sealing Ring	SRD300	5.19
328	300mm(353) Dia METRO BBA Twinwall Coupling	CRD225	7.75
329	300mm Diameter Twinwall D/S Bend - 11.25 Degree 'BBA'	BRD300X11.25/1	71.86
330	300mm Diameter Twinwall D/S Bend - 22.5 Degree 'BBA'	BRD300X22.5/1	71.86
331	300mm Diameter Twinwall D/S Bend - 45 Degree 'BBA'	BRD300X45/1	71.86
332	300mm Diameter Twinwall D/S Bend - 90 Degree 'BBA'	BRD300X90/1	71.86
333	300 x 150 Twinwall Socketed Junction - 45 Degree 'BBA'	JRD300150Y	101.91
334	300 x 150 Twinwall Socketed Junction - 90 Degree 'BBA'	JRD300150T/1	101.91
335	300 x 300 Twinwall Socketed Junction - 90 Degree 'BBA'	JRD300300T/1	152.38
336	375mm Diameter x 6 Metre Plain Ended Black Twinwall Pipe 'BBA'	RD375X6PE/1	176.76
337	375mm Dia 'BBA' Twinwall Sealing Ring	SRD375	9.64
338	375mm Diameter Twinwall Coupling 'BBA'	CRD375	23.84
339	450mm Diameter x 6 Metre Plain End BBA Black Twinwall Pipe	RD450X6/1	219.42
340	450mm Dia 'BBA' Twinwall Sealing Ring	SRD450DS/1	12.8
341	450mm Diameter Twinwall Coupling 'BBA'	CRD450/1	32.61
342	600mm Diameter x 6 Metre Plain Ended Black Twinwall Pipe 'BBA'	RD600X6/1	341.32
343	600mm Dia 'BBA' Twinwall Sealing Ring	SRD600DS/1	24.93
344	600mm Diameter Twinwall Coupling 'BBA'	CRD600/1	54.86
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
Sheet1

 

[Joe4]

If you want to check column C to find the last row, then you need to change the column reference in the formula, i.e.
change this:

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

to this:

lastRow = Cells(Rows.Count,"C").End(xlUp).Row

 

[ash59]

If you want to check column C to find the last row, then you need to change the column reference in the formula, i.e.
change this:

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

to this:

lastRow = Cells(Rows.Count,"C").End(xlUp).Row

The vba reads as follows but the cursor stays in cell C1

Sheets("Sheet1").Select
    Range("C1").Select
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    ActiveWorkbook.Save

 

[Joe4]

The vba reads as follows but the cursor stays in cell C1

That is right. The code is not selecting anything. It is simply finding the last row in column C with data and storing it in a variable.
You can then do what you want with it (it usually is not necessary to select ranges in order to work with them).

But if you wanted to select the last cell in column C with data, you could then do something like:

Range("C" & lastRow).Select

or

Cells(lastRow, "C").Select

Or, if you wanted to capture the range from C1 down to the last row in a variable, you could do:

Dim rng as Range
Set rng = Range("C1:C" & lastRow)

Or, if you wanted to bypass that lastRow calculation altogether and select the last cell in column C with data, you could do it like this:

Cells(Rows.Count,"C").End(xlUp).Select

 

[ash59]

That is right. The code is not selecting anything. It is simply finding the last row in column C with data and storing it in a variable.
You can then do what you want with it (it usually is not necessary to select ranges in order to work with them).

But if you wanted to select the last cell in column C with data, you could then do something like:

Range("C" & lastRow).Select

or

Cells(lastRow, "C").Select

Or, if you wanted to capture the range from C1 down to the last row in a variable, you could do:

Dim rng as Range
Set rng = Range("C1:C" & lastRow)

Or, if you wanted to bypass that lastRow calculation altogether and select the last cell in column C with data, you could do it like this:

Cells(Rows.Count,"C").End(xlUp).Select

on the 3rd option, I am getting Method of "Range" of object "_global" failed notification

Range("C1").Select
    Dim rng As Range
    Set rng = Range("C1:C" & lastRow)
    ActiveWorkbook.Save