Macro to delete cells

[edwardj3]

Hi,

Can you suggest any VBA code which will delete the cells with no data entries. I also want it to remove the 24 x hourly periods plus the date cell which is 24 merged cells. I hope you can copy and paste sp you can see the formatting.

Period 1 could have a min of 3 days of data (6 empty days to be deleted) and a maximum of 9 days of data (no data to be deleted.

As is

Period 1	Web Transactions	Metric ID	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	08/11/2017	09/11/2017	10/11/2017	01/01/2016
24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	24-1	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24
Service1	1.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.6	1.9	1.5	1.5	1.6	1.5	1.6	1.4	2.2	3.8	0.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	3.2	2.2	2.0	1.6	1.6	1.6	1.7	1.7	1.6	12.4	1.1	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.3	0.0	0.0	0.0	0.0	0.0	3.2	1.9	1.6	1.5	1.5	1.3	1.4	1.6	1.5	1.7	1.2	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.7	1.7	1.4	1.4	1.4	1.4	1.4	1.5	1.5	1.5	3.3	0.0	0.0	0.0	0.0	0.0
Service2	2.000	2.1	3.9	40.8	2.9	2.5	1.8	7.5	2.9	1.9	2.2	2.3	2.0	2.1	2.2	2.3	2.6	2.9	1.3	1.8	2.3	4.5	2.2	3.7	3.4	1.9	2.4	4.1	3.5	5.2	6.3	7.0	3.1	1.2	1.2	1.2	1.4	1.3	1.3	1.5	1.6	1.8	1.7	2.0	2.3	2.1	2.0	2.7	2.5	2.4	3.6	5.8	4.7	3.0	3.6	6.3	3.8	1.0	1.0	1.4	1.2	1.1	1.0	1.3	1.5	1.9	1.8	2.0	0.8	1.9	2.1	4.4	1.9	2.3	3.1	5.5	5.5	2.9	2.1	6.9	2.2	0.9	1.0	1.1	1.2	0.9	1.1	1.3	1.5	1.7	2.4	1.9	1.7	2.2	2.3	3.6	2.4	2.1	1.9	2.3	11.6	5.1	2.6	4.7	2.6	1.0	0.8	0.9	1.1	1.4	1.4	1.4	1.7	1.7	2.9	2.0	2.3	2.6	3.0	1.7	1.3
Service3	3.000	2.4	3.1	25.9	3.0	2.3	2.2	7.6	3.8	1.5	1.7	1.7	2.2	1.5	2.4	2.2	1.4	1.9	2.1	2.3	2.0	3.6	1.6	4.7	2.7	3.4	4.3	3.6	5.2	5.8	3.6	6.1	2.8	1.7	1.2	2.0	2.2	2.2	2.6	1.9	2.6	2.4	2.6	1.8	1.7	2.1	2.2	3.2	3.1	3.2	5.2	3.5	7.6	4.2	5.4	3.7	7.5	1.5	1.5	1.7	1.6	1.8	1.6	1.4	1.6	2.3	37.6	2.3	2.3	2.2	2.2	6.3	2.2	2.5	6.4	4.0	8.7	6.5	2.6	4.6	3.4	1.6	1.7	1.9	2.1	1.6	1.3	1.5	2.4	2.4	1.8	2.3	2.0	2.6	2.7	2.7	2.4	2.6	4.0	3.2	8.7	7.2	3.4	5.5	3.9	1.8	1.5	1.7	1.5	1.5	2.2	2.3	1.8	2.3	2.1	2.2	2.6	2.0	2.3	3.2	2.4
Service4	4.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.6	1.2	0.5	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.6	0.7	0.6	0.9	0.7	0.9	1.0	0.8	1.0	0.6	0.0	1.2	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	44.2	0.9	0.7	0.9	0.5	1.0	0.9	0.5	1.3	0.5	0.8	0.0	1.3	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.7	1.0	0.9	1.0	1.1	1.0	1.2	1.0	0.6	0.7	1.0	0.0	0.0	0.0	0.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.4	0.0	0.5	0.8	0.9	0.8	1.1	0.9	0.8	0.8	0.3	0.9	0.4	0.0	0.0	0.6	0.0	0.0
Service5	5.000	0.0	0.0	0.0	6.9	0.0	0.0	0.0	3.6	0.0	2.8	2.1	2.5	2.1	2.1	2.0	2.1	2.3	1.8	2.2	1.9	2.3	2.5	2.8	2.2	2.4	0.0	1.6	2.8	0.0	2.9	7.8	2.8	2.0	2.0	2.2	2.2	2.6	2.7	2.8	3.0	2.5	1.5	2.3	2.0	3.5	2.9	21.5	2.7	0.0	3.4	0.0	0.0	0.0	3.7	9.3	12.1	2.3	2.4	2.3	2.6	2.2	2.9	2.4	2.9	2.4	2.5	2.5	2.0	2.1	2.2	4.3	2.5	3.1	10.3	0.0	7.0	5.2	2.6	17.4	2.1	1.9	2.2	2.2	2.3	2.6	2.4	2.3	2.9	2.3	2.0	2.3	1.9	3.0	2.1	3.0	2.6	2.8	2.6	0.0	0.0	0.0	2.7	11.5	3.2	2.2	1.9	2.0	2.2	2.5	2.3	2.3	2.4	2.0	2.7	1.8	2.3	2.7	2.0	2.7	2.7
Service6	6.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.9	1.3	1.4	1.5	1.5	1.7	1.7	1.6	1.8	1.6	1.3	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.9	1.4	1.6	1.6	1.7	1.6	1.6	1.5	1.9	1.7	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.4	1.5	1.5	1.6	1.7	1.6	1.5	1.9	1.9	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.9	1.5	1.3	1.5	1.5	1.5	1.6	1.5	1.7	1.7	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Service7	7.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.4	0.0	0.0	0.8	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.6	5.7	2.9	11.1	1.8	2.6	1.7	0.8	0.9	0.7	0.8	0.8	0.9	0.8	0.9	1.0	1.0	1.1	1.2	1.8	1.0	1.6	1.5	0.0	2.6	9.2	6.1	2.7	2.0	9.9	5.1	0.7	0.8	0.7	0.7	0.7	0.8	0.7	0.9	1.1	1.2	1.2	1.1	1.3	0.8	3.4	0.6	1.1	1.4	6.0	10.6	2.9	1.0	2.6	1.2	0.8	0.8	0.7	0.7	0.7	0.7	0.7	0.9	1.0	0.9	1.3	1.4	1.6	1.2	4.3	2.0	1.4	1.6	2.2	6.5	1.8	1.4	3.4	2.6	0.8	0.7	0.8	0.7	0.8	0.8	0.7	1.0	1.1	1.3	1.2	1.4	1.6	0.0	11.5	0.9
Service8	8.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.6	0.9	0.7	0.8	0.8	0.9	0.7	0.5	0.7	0.6	0.9	0.0	0.4	0.0	1.5	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.8	0.8	0.8	0.7	0.7	0.7	0.7	0.7	0.6	0.7	0.7	0.6	0.8	3.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.7	0.8	0.9	0.8	0.7	0.7	0.7	0.8	0.7	1.2	0.8	0.6	0.9	0.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.3	0.9	0.8	0.8	0.7	0.8	0.7	0.8	0.8	0.7	0.7	0.6	0.7	0.9	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	8.3	1.4	1.1	0.8	0.8	0.7	0.7	0.8	0.7	0.8	0.8	0.8	0.9	0.8	1.0	1.2	1.5	0.0
Service9	9.000	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.8	0.7	0.6	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.4	0.4	0.0	0.7	0.5	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	5.6	0.6	0.5	0.5	0.8	0.8	0.8	0.6	0.5	0.5	0.5	0.5	0.5	0.5	0.8	2.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	19.9	0.0	1.0	0.5	0.6	0.5	0.5	0.5	0.5	0.6	0.5	0.9	0.6	0.5	0.7	0.7	0.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	7.7	1.1	0.8	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.5	0.5	0.5	0.5	0.6	0.5	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	2.7	0.8	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.5	0.5	1.2	0.4	0.9	0.0

<tbody>
</tbody>

To be

Period 1	Web Transactions	Metric ID	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018
###	###	###	###	###	###	06-Jul	###	###	###	###	###	###	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	###	####	####	###	###	###	###	###	###	###	###	###	###	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	##	###	###	###	###	###	###	###	###	###	###	###	###	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	###	###	###	###	###	###	###	###	###	###	###	###	###	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	###	###	###	###	###	###	###	###	###	###	###	###	###	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24
Service1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2.6	1.9	1.5	1.5	1.6	1.5	1.6	1.4	2.2	3.8	0.8	0	0	0	0	0	0	0	0	0	0	0	0	3.2	2.2	2	1.6	1.6	1.6	1.7	1.7	1.6	12.4	1.1	0	0	0	0	0	0	0	2.3	0	0	0	0	0	3.2	1.9	1.6	1.5	1.5	1.3	1.4	1.6	1.5	1.7	1.2	0	0	0	0	0	0	0	0	0	0	0	0	0	1.7	1.7	1.4	1.4	1.4	1.4	1.4	1.5	1.5	1.5	3.3	0	0	0	0	0
Service2	2	2.1	3.9	40.8	2.9	2.5	1.8	7.5	2.9	1.9	2.2	2.3	2	2.1	2.2	2.3	2.6	2.9	1.3	1.8	2.3	4.5	2.2	3.7	3.4	1.9	2.4	4.1	3.5	5.2	6.3	7	3.1	1.2	1.2	1.2	1.4	1.3	1.3	1.5	1.6	1.8	1.7	2	2.3	2.1	2	2.7	2.5	2.4	3.6	5.8	4.7	3	3.6	6.3	3.8	1	1	1.4	1.2	1.1	1	1.3	1.5	1.9	1.8	2	0.8	1.9	2.1	4.4	1.9	2.3	3.1	5.5	5.5	2.9	2.1	6.9	2.2	0.9	1	1.1	1.2	0.9	1.1	1.3	1.5	1.7	2.4	1.9	1.7	2.2	2.3	3.6	2.4	2.1	1.9	2.3	11.6	5.1	2.6	4.7	2.6	1	0.8	0.9	1.1	1.4	1.4	1.4	1.7	1.7	2.9	2	2.3	2.6	3	1.7	1.3
Service3	3	2.4	3.1	25.9	3	2.3	2.2	7.6	3.8	1.5	1.7	1.7	2.2	1.5	2.4	2.2	1.4	1.9	2.1	2.3	2	3.6	1.6	4.7	2.7	3.4	4.3	3.6	5.2	5.8	3.6	6.1	2.8	1.7	1.2	2	2.2	2.2	2.6	1.9	2.6	2.4	2.6	1.8	1.7	2.1	2.2	3.2	3.1	3.2	5.2	3.5	7.6	4.2	5.4	3.7	7.5	1.5	1.5	1.7	1.6	1.8	1.6	1.4	1.6	2.3	37.6	2.3	2.3	2.2	2.2	6.3	2.2	2.5	6.4	4	8.7	6.5	2.6	4.6	3.4	1.6	1.7	1.9	2.1	1.6	1.3	1.5	2.4	2.4	1.8	2.3	2	2.6	2.7	2.7	2.4	2.6	4	3.2	8.7	7.2	3.4	5.5	3.9	1.8	1.5	1.7	1.5	1.5	2.2	2.3	1.8	2.3	2.1	2.2	2.6	2	2.3	3.2	2.4
Service4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.6	1.2	0.5	0	0	0	0	0	0	1	0	0	0	0	0	0	0	2.6	0.7	0.6	0.9	0.7	0.9	1	0.8	1	0.6	0	1.2	0	0	0	0	0	0	0	0	0	0	0	0	44.2	0.9	0.7	0.9	0.5	1	0.9	0.5	1.3	0.5	0.8	0	1.3	0	0	0	0	0	0	0	0	0	0	0	0	0.7	1	0.9	1	1.1	1	1.2	1	0.6	0.7	1	0	0	0	0.8	0	0	0	0	0	0	0	0.4	0	0.5	0.8	0.9	0.8	1.1	0.9	0.8	0.8	0.3	0.9	0.4	0	0	0.6	0	0
Service5	5	0	0	0	6.9	0	0	0	3.6	0	2.8	2.1	2.5	2.1	2.1	2	2.1	2.3	1.8	2.2	1.9	2.3	2.5	2.8	2.2	2.4	0	1.6	2.8	0	2.9	7.8	2.8	2	2	2.2	2.2	2.6	2.7	2.8	3	2.5	1.5	2.3	2	3.5	2.9	21.5	2.7	0	3.4	0	0	0	3.7	9.3	12.1	2.3	2.4	2.3	2.6	2.2	2.9	2.4	2.9	2.4	2.5	2.5	2	2.1	2.2	4.3	2.5	3.1	10.3	0	7	5.2	2.6	17.4	2.1	1.9	2.2	2.2	2.3	2.6	2.4	2.3	2.9	2.3	2	2.3	1.9	3	2.1	3	2.6	2.8	2.6	0	0	0	2.7	11.5	3.2	2.2	1.9	2	2.2	2.5	2.3	2.3	2.4	2	2.7	1.8	2.3	2.7	2	2.7	2.7
Service6	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1.9	1.3	1.4	1.5	1.5	1.7	1.7	1.6	1.8	1.6	1.3	0	0	0	0	0	0	0	0	0	0	0	0	0	1.9	1.4	1.6	1.6	1.7	1.6	1.6	1.5	1.9	1.7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1.4	1.5	1.5	1.6	1.7	1.6	1.5	1.9	1.9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1.9	1.5	1.3	1.5	1.5	1.5	1.6	1.5	1.7	1.7	0	0	0	0	0	0	0
Service7	7	0	0	0	0	0	0	0	0	0	1.4	0	0	0.8	0	1	0	0	0	0	0	0	0	0	0	0	1.6	5.7	2.9	11.1	1.8	2.6	1.7	0.8	0.9	0.7	0.8	0.8	0.9	0.8	0.9	1	1	1.1	1.2	1.8	1	1.6	1.5	0	2.6	9.2	6.1	2.7	2	9.9	5.1	0.7	0.8	0.7	0.7	0.7	0.8	0.7	0.9	1.1	1.2	1.2	1.1	1.3	0.8	3.4	0.6	1.1	1.4	6	10.6	2.9	1	2.6	1.2	0.8	0.8	0.7	0.7	0.7	0.7	0.7	0.9	1	0.9	1.3	1.4	1.6	1.2	4.3	2	1.4	1.6	2.2	6.5	1.8	1.4	3.4	2.6	0.8	0.7	0.8	0.7	0.8	0.8	0.7	1	1.1	1.3	1.2	1.4	1.6	0	11.5	0.9
Service8	8	0	0	0	0	0	0	0	0	1.6	0.9	0.7	0.8	0.8	0.9	0.7	0.5	0.7	0.6	0.9	0	0.4	0	1.5	0	0	0	0	0	0	0	0	1	0.8	0.8	0.8	0.7	0.7	0.7	0.7	0.7	0.6	0.7	0.7	0.6	0.8	3	0	0	0	0	0	0	0	0	0	0	1.7	0.8	0.9	0.8	0.7	0.7	0.7	0.8	0.7	1.2	0.8	0.6	0.9	0.8	0	0	0	0	0	0	0	0	0	2.3	0.9	0.8	0.8	0.7	0.8	0.7	0.8	0.8	0.7	0.7	0.6	0.7	0.9	0	0	0	0	0	0	0	0	0	8.3	1.4	1.1	0.8	0.8	0.7	0.7	0.8	0.7	0.8	0.8	0.8	0.9	0.8	1	1.2	1.5	0
Service9	9	0	0	0	0	0	0	0	2.8	0.7	0.6	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.4	0.4	0	0.7	0.5	0	0	0	0	0	0	0	0	5.6	0.6	0.5	0.5	0.8	0.8	0.8	0.6	0.5	0.5	0.5	0.5	0.5	0.5	0.8	2.8	0	0	0	0	0	0	0	0	19.9	0	1	0.5	0.6	0.5	0.5	0.5	0.5	0.6	0.5	0.9	0.6	0.5	0.7	0.7	0.8	0	0	0	0	0	0	0	7.7	1.1	0.8	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.5	0.5	0.5	0.5	0.6	0.5	0	0	0	0	0	0	0	0	0	2.7	0.8	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.6	0.5	0.5	1.2	0.4	0.9	0

<tbody>
</tbody>

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1039659-vba-delete-cells.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note: If you are trying to test out different posting tools, please use the "Test Here" forum for that purpose, and then you can post back to your original thread when you have it worked out.