|
Inserting Blank Rows Macro?
Hello group :)
I am trying to find a function or macro that will insert a blank row in Excel whenever certain data changes in column A. In the example below, column A shows geographical delivery routes within a city. The city is designated by the first 4 characters in the number. The last two numbers are the delivery route itself. So in the example, the first 8 records begin with 1019 meaning Jacksonville. The last two numbers are the delivery route. The delivery routes in the next city, Tampa, all begin with 1023, Daytona's begin with 1025, and so on. Is there a function I can use to insert a blank row wherever the first 4 numbers change in column A? Then to complicate things even further, can this function also count records in each city and show a total in the A column where the blank row was created? Lets say right under the Jacksonville routes, the 1019's? I would be very happy if I could just insert a blank row but the total by city would be awesome too. Any help is enormously appreciated. Here is a short example: A B C 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101968 Start 11/8/2004 101969 Start 11/8/2004 101969 Start 11/8/2004 101970 Start 11/8/2004 102321 Start 11/8/2004 102323 Start 11/8/2004 102335 Start 11/8/2004 102335 Start 11/8/2004 102345 Start 11/8/2004 102347 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102371 Start 11/8/2004 102371 Start 11/8/2004 102517 Start 11/8/2004 102531 Start 11/8/2004 102534 Start 11/8/2004 102535 Start 11/8/2004 102543 Start 11/8/2004 102543 Start 11/8/2004 102579 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102712 Start 11/8/2004 102712 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 Thank you Michael Saffer |
LOL - Take a look at Data / Subtotals. This will insert a blank line when
the data changes in whatevere column yous pecify and either give you a sum or count of the data in the field(s) you specify, which i think sounds very much like what you want. ;-) Regards Ken................... "Michael Saffer" wrote: Hello group :) I am trying to find a function or macro that will insert a blank row in Excel whenever certain data changes in column A. In the example below, column A shows geographical delivery routes within a city. The city is designated by the first 4 characters in the number. The last two numbers are the delivery route itself. So in the example, the first 8 records begin with 1019 meaning Jacksonville. The last two numbers are the delivery route. The delivery routes in the next city, Tampa, all begin with 1023, Daytona's begin with 1025, and so on. Is there a function I can use to insert a blank row wherever the first 4 numbers change in column A? Then to complicate things even further, can this function also count records in each city and show a total in the A column where the blank row was created? Lets say right under the Jacksonville routes, the 1019's? I would be very happy if I could just insert a blank row but the total by city would be awesome too. Any help is enormously appreciated. Here is a short example: A B C 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101968 Start 11/8/2004 101969 Start 11/8/2004 101969 Start 11/8/2004 101970 Start 11/8/2004 102321 Start 11/8/2004 102323 Start 11/8/2004 102335 Start 11/8/2004 102335 Start 11/8/2004 102345 Start 11/8/2004 102347 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102371 Start 11/8/2004 102371 Start 11/8/2004 102517 Start 11/8/2004 102531 Start 11/8/2004 102534 Start 11/8/2004 102535 Start 11/8/2004 102543 Start 11/8/2004 102543 Start 11/8/2004 102579 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102712 Start 11/8/2004 102712 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 Thank you Michael Saffer |
Hey Ken,
Thank you very much. I remember your name. You have helped me in the past. Regards! Michael Saffer "Ken Wright" wrote in message ... LOL - Take a look at Data / Subtotals. This will insert a blank line when the data changes in whatevere column yous pecify and either give you a sum or count of the data in the field(s) you specify, which i think sounds very much like what you want. ;-) Regards Ken................... "Michael Saffer" wrote: Hello group :) I am trying to find a function or macro that will insert a blank row in Excel whenever certain data changes in column A. In the example below, column A shows geographical delivery routes within a city. The city is designated by the first 4 characters in the number. The last two numbers are the delivery route itself. So in the example, the first 8 records begin with 1019 meaning Jacksonville. The last two numbers are the delivery route. The delivery routes in the next city, Tampa, all begin with 1023, Daytona's begin with 1025, and so on. Is there a function I can use to insert a blank row wherever the first 4 numbers change in column A? Then to complicate things even further, can this function also count records in each city and show a total in the A column where the blank row was created? Lets say right under the Jacksonville routes, the 1019's? I would be very happy if I could just insert a blank row but the total by city would be awesome too. Any help is enormously appreciated. Here is a short example: A B C 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101966 Start 11/8/2004 101968 Start 11/8/2004 101969 Start 11/8/2004 101969 Start 11/8/2004 101970 Start 11/8/2004 102321 Start 11/8/2004 102323 Start 11/8/2004 102335 Start 11/8/2004 102335 Start 11/8/2004 102345 Start 11/8/2004 102347 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102360 Start 11/8/2004 102371 Start 11/8/2004 102371 Start 11/8/2004 102517 Start 11/8/2004 102531 Start 11/8/2004 102534 Start 11/8/2004 102535 Start 11/8/2004 102543 Start 11/8/2004 102543 Start 11/8/2004 102579 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102702 Start 11/8/2004 102712 Start 11/8/2004 102712 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 102714 Start 11/8/2004 Thank you Michael Saffer |
| All times are GMT +1. The time now is 05:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com