VBA to delete cells

edwardj3 · Jan 19, 2018

Hi,

I update a spreadsheet to record hourly average service response time through a 24hr period. Once the data is entered this can leave a number of days without any data entries. In the example below this would be from 24-1 of 8/11/2017. I run a macro which does a lot of formatting that had to be done manually. Is there any code available I can add which will delete the merged date cells, the hourly periods and the all cells that do not have any data? The date is in a merged cell which goes above the 24 hourly periods. I hope that you can copy the data below (As is) and it will show the formatting.

Please note that Period 1 could have a min of 3 days of data and up to 9 days of data.

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

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

VBA to delete cells

edwardj3

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

VBA to delete cells

edwardj3

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock