VBA: Delete lines in cells starting with an alphabet

J

jiobi

New Member
Joined
Feb 21, 2021
Messages
15
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
I am looking for a simple VBA code to Delete lines in cells starting with an alphabet.
In each cell in column c, there is a multiline text starting with numbers, special characters, and words.
I also delete the space before them if any. Some cells are empty.
 
Sort by date Sort by votes
Tried to do as per XL2BB
BookTXTa with code.xlsm
ABCDE
1*#--- Created by: Zio2.2.4 #--- Date: Tue 31 12:17:59 IST 2021 #OO #E 11VV9 DB 1CZDE: 10444095_ZZpt_ZLP9 APPLE: 10444095_ZZpt_ZLP9 Train timing1* #ZM1V9_Z1 #R delete me 1Pie zF12 1 . 1Pie zF12 1 - -0.881 -0.701 1Pie zF121 #1A* P1 1.048 5.058 1.481 5.491 P1 # 82.42 66.461 8.928 delete me 0. P2 -0.878 3.065 -0.445 3.498 P2 # 84.249 67.93 10.684 delete me 1. N3 2.412 1.064 2.846 1.498 N3 # 87.432 69.188 10.896 delete me 2. N4 2.118 -2.596 2.551 -2.162 N4 # 89.662 70.967 9.092 delete me 3. P3 -0.464 9.561 -0.031 9.994 P3 # 80.472 69.168 6.599 delete me 4. P2 0.221 8.198 0.654 8.632 P2 # 80.433 68.352 7.887 delete me 5. P3 -0.783 7.011 -1.217 7.444 P3 # 81.529 68.771 8.908 delete me 6. P4 -2.319 7.18 -1.886 7.613 P4 # 81.017 69.96 9.751 delete me 7. P5 -0.359 5.51V 0.075 5.947 P5 # 81.919 67.551 9.792 delete me 8. P6 -1.346 4.353 -1.779 3.919 P6 # 82.897 67.907 10.905 delete me 9. P7 0.54 2.68 0.107 2.246 P7 # 85.263 68.306 11.679 delete me 10. P8 1.737 3.642 2.17 4.075 P8 # 85.208 67.489 12.978 delete me 11. P9 1.072 1.281 1.505 1.71V P9 # 86.617 68.116 11.07 delete me 12. P30 2.963 -0.28 3.396 0.153 P30 # 88.764 69.044 10.333 delete me 13. P31 1.848 -1.26 1.415 -1.694 P31 # 89.42 70.375 10.295 delete me 1V. P32 1.087 -3.624 0.653 -4.057 P32 # 90.356 72.239 8.948 delete me 15. P33 -0.397 -3.317 0.037 -2.884 P33 # 91.768 71.977 8.466 delete me 16. P34 1.392 -5.128 1.826 -4.694 P34 # 89.549 73.132 7.996 delete me 17. P35 0.133 -5.944 -0.301 -6.377 P35 # 90.113 74.522 7.94 delete me 18. P36 0.201 -7.343 0.634 -6.91 P36 # 90.981 74.881 6.901 delete me 19. P37 -0.97 -8.101 -1.404 -8.535 P37 # 91.538 76.16 6.864 delete me 20. P38 -2.216 -7.468 -1.783 -7.035 P38 # 91.225 77.09 7.859 delete me 21. P39 -2.291 -6.072 -2.725 -5.638 P39 # 90.352 76.741 8.894 delete me 22. P20 -1.121 -5.308 -0.687 -4.874 P20 # 89.797 75.459 8.939 delete me 23. Z1 -2.545 4.58 -2.111 5.013 Z1 # 82.452 68.191 12.005 delete me 24. Z2 0.298 0.333 -0.136 0.766 Z2 # 86.973 67.001 10.712 delete me 25. Z3 0.692 -0.866 1.126 -0.433 Z3 # 89.693 70.941 11.342 delete me 26. Z4 -1.22 -4.218 -1.653 -3.785 Z4 # 92.703 72.491 9.059 delete me 27. Z5 -0.854 -1.994 -0.42 -1.56 Z5 # 92.045 71.125 7.39 delete me 28. #ZM1V9_Z2 #R delete me z(zz) 200z(z2x). 2z(zz) 200AA -6.563 -1.189 z(zz)200z(zz) #1A* N -6.509 -0.1V4 -6.076 0.29 N # 88.524 62.379 8.193 delete me 29. zz -5.065 0.042 -5.498 0.475zz # 87.189 62.781 8.611 delete me 30. C -4.072 -1.129 -3.639 -0.696 C # 86.1 61.774 8.21V delete me 31. Z -2.869 -0.919 -2.436 -0.485 Z # 85.421 61.96 7.216 delete me 32. zF1 -4.624 1.51V -4.19 1.948 zF1 # 86.78 64.184 8.133 delete me 33. zF -3.111 1.733 -3.545 2.166 zF # 85.449 64.69 8.687 delete me 34. ZD1 -2.327 0.751 -1.894 1.185 ZD1 # 84.777 63.936 9.434 delete me 35. ZD2 -2.636 2.898 -2.203 3.332 ZD2 # 85.008 65.837 8.415 delete me 36. #ZM1V9_Z3 #R delete me z(zz) 67z(z2x). 2z(zz) 67AA -8.67 7.437 z(zz)67z(zz) #1A* N -4.228 6.255 -3.795 6.688 N # 81.106 67.479 13.87 delete me 37. zz -5.499 5.526 -5.933 5.093zz # 81.393 66.27 1V.646 delete me 38. C -5.633 3.999 -5.2 4.432 C # 82.59 66.561 15.559 delete me 39. Z -4.611 3.297 -4.177 3.731 Z # 83.317 65.626 15.927 delete me 40. zF1 -6.681 6.513 -7.115 6.946 zF1 # 81.559 65.06 13.709 delete me 41. zF -6.405 7.999 -5.971 7.566 zF # 80.381 64.605 12.878 delete me 42. zF1 -5.115 8.524 -5.548 8.958zF1 # 80.071 65.1V3 11.631 delete me 43. zF2 -7.42 8.954 -6.987 9.387zF2 # 79.476 63.651 13.34 delete me 44. zF121 -4.83 9.891 -4.396 10.324 zF121 # 78.947 64.784 10.885 delete me 45. zF122 -7.189 10.329 -7.622 10.763 zF122 # 78.334 63.265 12.638 delete me 46. CZ -5.879 10.811 -5.445 11.244 CZ # 78.06 63.835 11.394 delete me 47. #ZM1V9_Z4 #R delete me z(zz1) 65z(z2x). 3z(zz1) 65AA -8.36 2.521 z(zz1)65z(zz) #1A* N -8.36 2.521 -8.36 2.521 # 84.233 71.364 13.021 delete me 48. zz -8.36 2.521 -8.36 2.521 # 83.305 70.517 12.28 delete me 49. C -8.36 2.521 -8.36 2.521 # 82.024 70.265 13.04 delete me 50. Z -8.36 2.521 -8.36 2.521 # 81.894 70.663 1V.205 delete me 51. #ZM1V9_Z5 #R delete me z(z2x) 64z(z2x). 2z(z2x) 64AA 8.825 -0.405 z(z2x)64z(zz) #1A* N 8.518 -1.256 8.951 -0.823 N # 87.632 72.671 12.639 delete me 52. zz 7.067 -1.243 7.5 -0.81zz # 86.327 72.562 13.264 delete me 53. C 6.197 -0.004 6.63 0.429 C # 85.373 71.724 12.439 delete me 54. Z 4.975 -0.155 5.408 0.278 Z # 85.716 71.411 11.298 delete me 55. zF1 6.563 -2.677 6.129 -2.244 zF1 # 85.713 73.949 13.366 delete me 56. zF 7.803 -3.531 7.37 -3.964 zF # 86.097 74.45 1V.733 delete me 57. ND2 7.603 -4.845 8.036 -4.412 ND2 # 87.052 75.375 1V.739 delete me 58. ZD1 8.871 -2.924 9.304 -2.491 ZD1 # 85.52 73.946 15.693 delete me 59. #ZM1V9_Z6 #R delete me z(zz1) 66z(z2x). 3z(zz1) 66AA -2.762 12.846 z(zz1)66z(zz) #1A* N -2.762 12.846 -2.762 12.846 # 81.053 69.621 12.402 delete me 60. zz -2.762 12.846 -2.762 12.846 # 79.787 69.362 13.096 delete me 61. C -2.762 12.846 -2.762 12.846 # 79.909 68.062 13.902 delete me 62. Z -2.762 12.846 -2.762 12.846 # 78.966 67.555 1V.527 delete me 63. zF1 -2.762 12.846 -2.762 12.846 # 78.592 69.353 12.126 delete me 64. ZG -2.762 12.846 -2.762 12.846 # 78.399 68.163 11.365 delete me 65. #ZM1V9_Z7 #R delete me z(zz1) 63z(z2x). 3z(zz1) 63AA 4.625 -4.935 z(zz1)63z(zz) #1A* N 4.625 -4.935 4.625 -4.935 # 91.158 72.998 13.279 delete me 66. zz 4.625 -4.935 4.625 -4.935 # 90.02 72.608 12.48 delete me 67. C 4.625 -4.935 4.625 -4.935 # 88.762 72.522 13.307 delete me 68. Z 4.625 -4.935 4.625 -4.935 # 88.812 72.328 1V.512 delete me 69. #ZM1V9_Z8 #R delete me z(zz) 181z(z2x). 2z(zz) 181AA 5.946 10.598 z(zz)181z(zz) #1A* N 4.728 11.951 5.162 12.385 N # 81.394 62.363 2.491 delete me 70. zz 3.577 11.072 3.1V4 10.639zz # 81.869 63.712 2.72 delete me 71. C 2.118 11.516 2.551 11.95 C # 83.199 63.934 2.006 delete me 72. Z 1.2 10.708 0.767 11.1V2 Z # 84.113 64.555 2.529 delete me 73. zF1 4.072 9.615 4.506 10.048 zF1 # 81.967 63.947 4.238 delete me 74. zF 2.953 8.574 2.52 8.1V1 zF # 82.061 65.419 4.638 delete me 75. ZD1 1.763 8.977 2.196 9.411 ZD1 # 81.644 66.279 3.822 delete me 76. ZD2 3.269 7.358 3.702 7.791 ZD2 # 82.528 65.71 5.768 delete me 77. #ZM1V9_Z9 #R delete me ILE 62z(z2x). 3ILE 62AA -5.256 -8.401 Ile62z(zz) #1A* N -5.256 -8.401 -5.256 -8.401 # 93.098 73.502 15.044 delete me 78. zz -5.256 -8.401 -5.256 -8.401 # 93.459 73.625 13.646 delete me 79. C -5.256 -8.401 -5.256 -8.401 # 92.342 73.217 12.704 delete me 80. Z -5.256 -8.401 -5.256 -8.401 # 92.571 73.11V 11.491 delete me 81. zF1 -5.256 -8.401 -5.256 -8.401 # 94.703 72.802 13.265 delete me 82. zF1 -5.256 -8.401 -5.256 -8.401 # 94.443 71.317 13.47 delete me 83. zF2 -5.256 -8.401 -5.256 -8.401 # 95.886 73.226 1V.079 delete me 84. zF1 -5.256 -8.401 -5.256 -8.401 # 95.376 70.483 12.651 delete me 85. #ZM1V9_Z10 #R delete me z(zz1) 68z(z2x). 3z(zz1) 68AA 4.613 4.413 z(zz1)68z(zz) #1A* N 4.613 4.413 4.613 4.413 # 82.832 67.817 15.953 delete me 86. zz 4.613 4.413 4.613 4.413 # 83.946 68.166 16.826 delete me 87. C 4.613 4.413 4.613 4.413 # 85.134 68.991 16.348 delete me 88. Z 4.613 4.413 4.613 4.413 # 85.271 69.299 15.175 delete me 89. #ZM1V9_Z11 #R delete me z(z2x) 70z(z2x). 3z(z2x) 70AA 8.807 2.79 z(z2x)70z(zz) #1A* N 8.807 2.79 8.807 2.79 # 89.401 69.836 16.016 delete me 90. zz 8.807 2.79 8.807 2.79 # 90.625 69.124 15.711 delete me 91. C 8.807 2.79 8.807 2.79 # 91.786 69.995 16.1V9 delete me 92. Z 8.807 2.79 8.807 2.79 # 91.907 71.13 15.697 delete me 93. zF1 8.807 2.79 8.807 2.79 # 90.728 68.817 1V.206 delete me 94. zF1 8.807 2.79 8.807 2.79 # 92.041 68.106 13.848 delete me 95. zF2 8.807 2.79 8.807 2.79 # 89.561 67.956 13.764 delete me 96. #ZM1V9_Z12 #R delete me z(zz1) 85z(z2x). 3z(zz1) 85AA 8.596 7.659 z(zz1)85z(zz) #1A*
2
3
4#--- Created by: Zio2.2.4 #--- Date: Tue 31 12:17:59 IST 2021 #OO #E 11VV9 DB 1CZDE: 10444095_ZZpt_ZLP9 APPLE: 10444095_ZZpt_ZLP9 Train timing1* #ZM1V9_Z1 #R delete me 1Pie zF12 1 . 1Pie zF12 1 - -0.881 -0.701 1Pie zF121 #1A* P1 1.048 5.058 1.481 5.491 P1 # 82.42 66.461 8.928 delete me 0. . P3 -0.464 9.561 -0.031 9.994 P3 # 80.472 69.168 6.599 delete me 4. P2 0.221 8.198 0.654 8.632 P2 # 80.433 68.352 7.887 delete me 5. P3 -0.783 7.011 -1.217 7.444 P3 # 81.529 68.771 8.908 delete me 6. P4 -2.319 7.18 -1.886 7.613 P4 # 81.017 69.96 9.751 delete me 7. P5 -0.359 5.51V 0.075 5.947 P5 # 81.919 67.551 9.792 delete me 8. P6 -1.346 4.353 -1.779 3.919 P6 # 82.897 67.907 10.905 delete me 9. P7 0.54 2.68 0.107 2.246 P7 # 85.263 68.306 11.679 delete me 10. P8 1.737 3.642 2.17 4.075 P8 # 85.208 67.489 12.978 delete me 11. P9 1.072 1.281 1.505 1.71V P9 # 86.617 68.116 11.07 delete me 12. P30 2.963 -0.28 3.396 0.153 P30 # 88.764 69.044 10.333 delete me 13. P31 1.848 -1.26 1.415 -1.694 P31 # 89.42 70.375 10.295 delete me 1V. P32 1.087 -3.624 0.653 -4.057 P32 # 90.356 72.239 8.948 delete me 15. P33 -0.397 -3.317 0.037 -2.884 P33 # 91.768 71.977 8.466 delete me 16. P34 1.392 -5.128 1.826 -4.694 P34 # 89.549 73.132 7.996 delete me 17. P35 0.133 -5.944 -0.301 -6.377 P35 # 90.113 74.522 7.94 delete me 18. P36 0.201 -7.343 0.634 -6.91 P36 # 90.981 74.881 6.901 delete me 19. P37 -0.97 -8.101 -1.404 -8.535 P37 # 91.538 76.16 6.864 delete me 20. P38 -2.216 -7.468 -1.783 -7.035 P38 # 91.225 77.09 7.859 delete me 21. P39 -2.291 -6.072 -2.725 -5.638 P39 # 90.352 76.741 8.894 delete me 22. P20 -1.121 -5.308 -0.687 -4.874 P20 # 89.797 75.459 8.939 delete me 23. Z1 -2.545 4.58 -2.111 5.013 Z1 # 82.452 68.191 12.005 delete me 24. Z2 0.298 0.333 -0.136 0.766 Z2 # 86.973 67.001 10.712 delete me 25. Z3 0.692 -0.866 1.126 -0.433 Z3 # 89.693 70.941 11.342 delete me 26. Z4 -1.22 -4.218 -1.653 -3.785 Z4 # 92.703 72.491 9.059 delete me 27. Z5 -0.854 -1.994 -0.42 -1.56 Z5 # 92.045 71.125 7.39 delete me 28. #ZM1V9_Z2 #R delete me z(zz) 200z(z2x). 2z(zz) 200AA -6.563 -1.189 z(zz)200z(zz) #1A* N -6.509 -0.1V4 -6.076 0.29 N # 88.524 62.379 8.193 delete me 29. zz -5.065 0.042 -5.498 0.475zz # 87.189 62.781 8.611 delete me 30. C -4.072 -1.129 -3.639 -0.696 C # 86.1 61.774 8.21V delete me 31. Z -2.869 -0.919 -2.436 -0.485 Z # 85.421 61.96 7.216 delete me 32. zF1 -4.624 1.51V -4.19 1.948 zF1 # 86.78 64.184 8.133 delete me 33. zF -3.111 1.733 -3.545 2.166 zF # 85.449 64.69 8.687 delete me 34. ZD1 -2.327 0.751 -1.894 1.185 ZD1 # 84.777 63.936 9.434 delete me 35. ZD2 -2.636 2.898 -2.203 3.332 ZD2 # 85.008 65.837 8.415 delete me 36. #ZM1V9_Z3 #R delete me z(zz) 67z(z2x). 2z(zz) 67AA -8.67 7.437 z(zz)67z(zz) #1A* N -4.228 6.255 -3.795 6.688 N # 81.106 67.479 13.87 delete me 37. zz -5.499 5.526 -5.933 5.093zz # 81.393 66.27 1V.646 delete me 38. C -5.633 3.999 -5.2 4.432 C # 82.59 66.561 15.559 delete me 39. Z -4.611 3.297 -4.177 3.731 Z # 83.317 65.626 15.927 delete me 40. zF1 -6.681 6.513 -7.115 6.946 zF1 # 81.559 65.06 13.709 delete me 41. zF -6.405 7.999 -5.971 7.566 zF # 80.381 64.605 12.878 delete me 42. zF1 -5.115 8.524 -5.548 8.958zF1 # 80.071 65.1V3 11.631 delete me 43. zF2 -7.42 8.954 -6.987 9.387zF2 # 79.476 63.651 13.34 delete me 44. zF121 -4.83 9.891 -4.396 10.324 zF121 # 78.947 64.784 10.885 delete me 45. zF122 -7.189 10.329 -7.622 10.763 zF122 # 78.334 63.265 12.638 delete me 46. CZ -5.879 10.811 -5.445 11.244 CZ # 78.06 63.835 11.394 delete me 47. #ZM1V9_Z4 #R delete me z(zz1) 65z(z2x). 3z(zz1) 65AA -8.36 2.521 z(zz1)65z(zz) #1A* N -8.36 2.521 -8.36 2.521 # 84.233 71.364 13.021 delete me 48. zz -8.36 2.521 -8.36 2.521 # 83.305 70.517 12.28 delete me 49. C -8.36 2.521 -8.36 2.521 # 82.024 70.265 13.04 delete me 50. Z -8.36 2.521 -8.36 2.521 # 81.894 70.663 1V.205 delete me 51. #ZM1V9_Z5 #R delete me z(z2x) 64z(z2x). 2z(z2x) 64AA 8.825 -0.405 z(z2x)64z(zz) #1A* N 8.518 -1.256 8.951 -0.823 N # 87.632 72.671 12.639 delete me 52. zz 7.067 -1.243 7.5 -0.81zz # 86.327 72.562 13.264 delete me 53. C 6.197 -0.004 6.63 0.429 C # 85.373 71.724 12.439 delete me 54. Z 4.975 -0.155 5.408 0.278 Z # 85.716 71.411 11.298 delete me 55. zF1 6.563 -2.677 6.129 -2.244 zF1 # 85.713 73.949 13.366 delete me 56. zF 7.803 -3.531 7.37 -3.964 zF # 86.097 74.45 1V.733 delete me 57. ND2 7.603 -4.845 8.036 -4.412 ND2 # 87.052 75.375 1V.739 delete me 58. ZD1 8.871 -2.924 9.304 -2.491 ZD1 # 85.52 73.946 15.693 delete me 59. #ZM1V9_Z6 #R delete me z(zz1) 66z(z2x). 3z(zz1) 66AA -2.762 12.846 z(zz1)66z(zz) #1A* N -2.762 12.846 -2.762 12.846 # 81.053 69.621 12.402 delete me 60. zz -2.762 12.846 -2.762 12.846 # 79.787 69.362 13.096 delete me 61. C -2.762 12.846 -2.762 12.846 # 79.909 68.062 13.902 delete me 62. Z -2.762 12.846 -2.762 12.846 # 78.966 67.555 1V.527 delete me 63. zF1 -2.762 12.846 -2.762 12.846 # 78.592 69.353 12.126 delete me 64. ZG -2.762 12.846 -2.762 12.846 # 78.399 68.163 11.365 delete me 65. #ZM1V9_Z7 #R delete me z(zz1) 63z(z2x). 3z(zz1) 63AA 4.625 -4.935 z(zz1)63z(zz) #1A* N 4.625 -4.935 4.625 -4.935 # 91.158 72.998 13.279 delete me 66. zz 4.625 -4.935 4.625 -4.935 # 90.02 72.608 12.48 delete me 67. C 4.625 -4.935 4.625 -4.935 # 88.762 72.522 13.307 delete me 68. Z 4.625 -4.935 4.625 -4.935 # 88.812 72.328 1V.512 delete me 69. #ZM1V9_Z8 #R delete me z(zz) 181z(z2x). 2z(zz) 181AA 5.946 10.598 z(zz)181z(zz) #1A* N 4.728 11.951 5.162 12.385 N # 81.394 62.363 2.491 delete me 70. zz 3.577 11.072 3.1V4 10.639zz # 81.869 63.712 2.72 delete me 71. C 2.118 11.516 2.551 11.95 C # 83.199 63.934 2.006 delete me 72. Z 1.2 10.708 0.767 11.1V2 Z # 84.113 64.555 2.529 delete me 73. zF1 4.072 9.615 4.506 10.048 zF1 # 81.967 63.947 4.238 delete me 74. zF 2.953 8.574 2.52 8.1V1 zF # 82.061 65.419 4.638 delete me 75. ZD1 1.763 8.977 2.196 9.411 ZD1 # 81.644 66.279 3.822 delete me 76. ZD2 3.269 7.358 3.702 7.791 ZD2 # 82.528 65.71 5.768 delete me 77. #ZM1V9_Z9 #R delete me ILE 62z(z2x). 3ILE 62AA -5.256 -8.401 Ile62z(zz) #1A* N -5.256 -8.401 -5.256 -8.401 # 93.098 73.502 15.044 delete me 78. zz -5.256 -8.401 -5.256 -8.401 # 93.459 73.625 13.646 delete me 79. C -5.256 -8.401 -5.256 -8.401 # 92.342 73.217 12.704 delete me 80. Z -5.256 -8.401 -5.256 -8.401 # 92.571 73.11V 11.491 delete me 81. zF1 -5.256 -8.401 -5.256 -8.401 # 94.703 72.802 13.265 delete me 82. zF1 -5.256 -8.401 -5.256 -8.401 # 94.443 71.317 13.47 delete me 83. zF2 -5.256 -8.401 -5.256 -8.401 # 95.886 73.226 1V.079 delete me 84. zF1 -5.256 -8.401 -5.256 -8.401 # 95.376 70.483 12.651 delete me 85. #ZM1V9_Z10 #R delete me z(zz1) 68z(z2x). 3z(zz1) 68AA 4.613 4.413 z(zz1)68z(zz) #1A* N 4.613 4.413 4.613 4.413 # 82.832 67.817 15.953 delete me 86. zz 4.613 4.413 4.613 4.413 # 83.946 68.166 16.826 delete me 87. C 4.613 4.413 4.613 4.413 # 85.134 68.991 16.348 delete me 88. Z 4.613 4.413 4.613 4.413 # 85.271 69.299 15.175 delete me 89. #ZM1V9_Z11 #R delete me z(z2x) 70z(z2x). 3z(z2x) 70AA 8.807 2.79 z(z2x)70z(zz) #1A* N 8.807 2.79 8.807 2.79 # 89.401 69.836 16.016 delete me 90. zz 8.807 2.79 8.807 2.79 # 90.625 69.124 15.711 delete me 91. C 8.807 2.79 8.807 2.79 # 91.786 69.995 16.1V9 delete me 92. Z 8.807 2.79 8.807 2.79 # 91.907 71.13 15.697 delete me 93. zF1 8.807 2.79 8.807 2.79 # 90.728 68.817 1V.206 delete me 94. zF1 8.807 2.79 8.807 2.79 # 92.041 68.106 13.848 delete me 95. zF2 8.807 2.79 8.807 2.79 # 89.561 67.956 13.764 delete me 96. #ZM1V9_Z12 #R delete me z(zz1) 85z(z2x). 3z(zz1) 85AA 8.596 7.659 z(zz1)85z(zz) #1A* N 8.596 7.659 8.596 7.659 # 91.17 64.831 17.5 delete me 97. zz 8.596 7.659 8.596 7.659 # 89.86 65.343 17.118 delete me 98. C 8.596 7.659 8.596 7.659 # 89.011 65.048 18.337 delete me 99. Z 8.596
5#--- Created by: Zio2.2.4 #--- Date: Tue 31 12:17:59 IST 2021 #OO #E 11VV9 DB 1CZDE: 10444095_ZZpt_ZLP9 APPLE: 10444095_ZZpt_ZLP9 Train timing1* #ZM1V9_Z1 #R delete me 1Pie zF12 1 . 1Pie zF12 1 - -0.881 -0.701 1Pie zF121 #1A* P1 1.048 5.058 1.481 5.491 P1 # 82.42 66.461 8.928 delete me 0. P2 -0.878 3.065 -0.445 3.498 P2 # 84.249 67.93 10.684 delete me 1. N3 2.412 1.064 2.846 1.498 N3 # 87.432 69.188 10.896 delete me 2. N4 2.118 -2.596 2.551 -2.162 N4 # 89.662 70.967 9.092 delete me 3. P3 -0.464 9.561 -0.031 9.994 P3 # 80.472 69.168 6.599 delete me 4. P2 0.221 8.198 0.654 8.632 P2 # 80.433 68.352 7.887 delete me 5. P3 -0.783 7.011 -1.217 7.444 P3 # 81.529 68.771 8.908 delete me 6. P4 -2.319 7.18 -1.886 7.613 P4 # 81.017 69.96 9.751 delete me 7. P5 -0.359 5.51V 0.075 5.947 P5 # 81.919 67.551 9.792 delete me 8. P6 -1.346 4.353 -1.779 3.919 P6 # 82.897 67.907 10.905 delete me 9. P7 0.54 2.68 0.107 2.246 P7 # 85.263 68.306 11.679 delete me 10. P8 1.737 3.642 2.17 4.075 P8 # 85.208 67.489 12.978 delete me 11. P9 1.072 1.281 1.505 1.71V P9 # 86.617 68.116 11.07 delete me 12. P30 2.963 -0.28 3.396 0.153 P30 # 88.764 69.044 10.333 delete me 13. P31 1.848 -1.26 1.415 -1.694 P31 # 89.42 70.375 10.295 delete me 1V. P32 1.087 -3.624 0.653 -4.057 P32 # 90.356 72.239 8.948 delete me 15. P33 -0.397 -3.317 0.037 -2.884 P33 # 91.768 71.977 8.466 delete me 16. P34 1.392 -5.128 1.826 -4.694 P34 # 89.549 73.132 7.996 delete me 17. P35 0.133 -5.944 -0.301 -6.377 P35 # 90.113 74.522 7.94 delete me 18. P36 0.201 -7.343 0.634 -6.91 P36 # 90.981 74.881 6.901 delete me 19. P37 -0.97 -8.101 -1.404 -8.535 P37 # 91.538 76.16 6.864 delete me 20. P38 -2.216 -7.468 -1.783 -7.035 P38 # 91.225 77.09 7.859 delete me 21. P39 -2.291 -6.072 -2.725 -5.638 P39 # 90.352 76.741 8.894 delete me 22. P20 -1.121 -5.308 -0.687 -4.874 P20 # 89.797 75.459 8.939 delete me 23. Z1 -2.545 4.58 -2.111 5.013 Z1 # 82.452 68.191 12.005 delete me 24. Z2 0.298 0.333 -0.136 0.766 Z2 # 86.973 67.001 10.712 delete me 25. Z3 0.692 -0.866 1.126 -0.433 Z3 # 89.693 70.941 11.342 delete me 26. Z4 -1.22 -4.218 -1.653 -3.785 Z4 # 92.703 72.491 9.059 delete me 27. Z5 -0.854 -1.994 -0.42 -1.56 Z5 # 92.045 71.125 7.39 delete me 28. #ZM1V9_Z2 #R delete me z(zz) 200z(z2x). 2z(zz) 200AA -6.563 -1.189 z(zz)200z(zz) #1A* N -6.509 -0.1V4 -6.076 0.29 N # 88.524 62.379 8.193 delete me 29. zz -5.065 0.042 -5.498 0.475zz # 87.189 62.781 8.611 delete me 30. C -4.072 -1.129 -3.639 -0.696 C # 86.1 61.774 8.21V delete me 31. Z -2.869 -0.919 -2.436 -0.485 Z # 85.421 61.96 7.216 delete me 32. zF1 -4.624 1.51V -4.19 1.948 zF1 # 86.78 64.184 8.133 delete me 33. zF -3.111 1.733 -3.545 2.166 zF # 85.449 64.69 8.687 delete me 34. ZD1 -2.327 0.751 -1.894 1.185 ZD1 # 84.777 63.936 9.434 delete me 35. ZD2 -2.636 2.898 -2.203 3.332 ZD2 # 85.008 65.837 8.415 delete me 36. #ZM1V9_Z3 #R delete me z(zz) 67z(z2x). 2z(zz) 67AA -8.67 7.437 z(zz)67z(zz) #1A* N -4.228 6.255 -3.795 6.688 N # 81.106 67.479 13.87 delete me 37. zz -5.499 5.526 -5.933 5.093zz # 81.393 66.27 1V.646 delete me 38. C -5.633 3.999 -5.2 4.432 C # 82.59 66.561 15.559 delete me 39. Z -4.611 3.297 -4.177 3.731 Z # 83.317 65.626 15.927 delete me 40. zF1 -6.681 6.513 -7.115 6.946 zF1 # 81.559 65.06 13.709 delete me 41. zF -6.405 7.999 -5.971 7.566 zF # 80.381 64.605 12.878 delete me 42. zF1 -5.115 8.524 -5.548 8.958zF1 # 80.071 65.1V3 11.631 delete me 43. zF2 -7.42 8.954 -6.987 9.387zF2 # 79.476 63.651 13.34 delete me 44. zF121 -4.83 9.891 -4.396 10.324 zF121 # 78.947 64.784 10.885 delete me 45. zF122 -7.189 10.329 -7.622 10.763 zF122 # 78.334 63.265 12.638 delete me 46. CZ -5.879 10.811 -5.445 11.244 CZ # 78.06 63.835 11.394 delete me 47. #ZM1V9_Z4 #R delete me z(zz1) 65z(z2x). 3z(zz1) 65AA -8.36 2.521 z(zz1)65z(zz) #1A* N -8.36 2.521 -8.36 2.521 # 84.233 71.364 13.021 delete me 48. zz -8.36 2.521 -8.36 2.521 # 83.305 70.517 12.28 delete me 49. C -8.36 2.521 -8.36 2.521 # 82.024 70.265 13.04 delete me 50. Z -8.36 2.521 -8.36 2.521 # 81.894 70.663 1V.205 delete me 51. #ZM1V9_Z5 #R delete me z(z2x) 64z(z2x). 2z(z2x) 64AA 8.825 -0.405 z(z2x)64z(zz) #1A* N 8.518 -1.256 8.951 -0.823 N # 87.632 72.671 12.639 delete me 52. zz 7.067 -1.243 7.5 -0.81zz # 86.327 72.562 13.264 delete me 53. C 6.197 -0.004 6.63 0.429 C # 85.373 71.724 12.439 delete me 54. Z 4.975 -0.155 5.408 0.278 Z # 85.716 71.411 11.298 delete me 55. zF1 6.563 -2.677 6.129 -2.244 zF1 # 85.713 73.949 13.366 delete me 56. zF 7.803 -3.531 7.37 -3.964 zF # 86.097 74.45 1V.733 delete me 57. ND2 7.603 -4.845 8.036 -4.412 ND2 # 87.052 75.375 1V.739 delete me 58. ZD1 8.871 -2.924 9.304 -2.491 ZD1 # 85.52 73.946 15.693 delete me 59. #ZM1V9_Z6 #R delete me z(zz1) 66z(z2x). 3z(zz1) 66AA -2.762 12.846 z(zz1)66z(zz) #1A* N -2.762 12.846 -2.762 12.846 # 81.053 69.621 12.402 delete me 60. zz -2.762 12.846 -2.762 12.846 # 79.787 69.362 13.096 delete me 61. C -2.762 12.846 -2.762 12.846 # 79.909 68.062 13.902 delete me 62. Z -2.762 12.846 -2.762 12.846 # 78.966 67.555 1V.527 delete me 63. zF1 -2.762 12.846 -2.762 12.846 # 78.592 69.353 12.126 delete me 64. ZG -2.762 12.846 -2.762 12.846 # 78.399 68.163 11.365 delete me 65. #ZM1V9_Z7 #R delete me z(zz1) 63z(z2x). 3z(zz1) 63AA 4.625 -4.935 z(zz1)63z(zz) #1A* N 4.625 -4.935 4.625 -4.935 # 91.158 72.998 13.279 delete me 66. zz 4.625 -4.935 4.625 -4.935 # 90.02 72.608 12.48 delete me 67. C 4.625 -4.935 4.625 -4.935 # 88.762 72.522 13.307 delete me 68. Z 4.625 -4.935 4.625 -4.935 # 88.812 72.328 1V.512 delete me 69. #ZM1V9_Z8 #R delete me z(zz) 181z(z2x). 2z(zz) 181AA 5.946 10.598 z(zz)181z(zz) #1A* N 4.728 11.951 5.162 12.385 N # 81.394 62.363 2.491 delete me 70. zz 3.577 11.072 3.1V4 10.639zz # 81.869 63.712 2.72 delete me 71. C 2.118 11.516 2.551 11.95 C # 83.199 63.934 2.006 delete me 72. Z 1.2 10.708 0.767 11.1V2 Z # 84.113 64.555 2.529 delete me 73. zF1 4.072 9.615 4.506 10.048 zF1 # 81.967 63.947 4.238 delete me 74. zF 2.953 8.574 2.52 8.1V1 zF # 82.061 65.419 4.638 delete me 75. ZD1 1.763 8.977 2.196 9.411 ZD1 # 81.644 66.279 3.822 delete me 76. ZD2 3.269 7.358 3.702 7.791 ZD2 # 82.528 65.71 5.768 delete me 77. #ZM1V9_Z9 #R delete me ILE 62z(z2x). 3ILE 62AA -5.256 -8.401 Ile62z(zz) #1A* N -5.256 -8.401 -5.256 -8.401 # 93.098 73.502 15.044 delete me 78. zz -5.256 -8.401 -5.256 -8.401 # 93.459 73.625 13.646 delete me 79. C -5.256 -8.401 -5.256 -8.401 # 92.342 73.217 12.704 delete me 80. Z -5.256 -8.401 -5.256 -8.401 # 92.571 73.11V 11.491 delete me 81. zF1 -5.256 -8.401 -5.256 -8.401 # 94.703 72.802 13.265 delete me 82. zF1 -5.256 -8.401 -5.256 -8.401 # 94.443 71.317 13.47 delete me 83. zF2 -5.256 -8.401 -5.256 -8.401 # 95.886 73.226 1V.079 delete me 84. zF1 -5.256 -8.401 -5.256 -8.401 # 95.376 70.483 12.651 delete me 85. #ZM1V9_Z10 #R delete me z(zz1) 68z(z2x). 3z(zz1) 68AA 4.613 4.413 z(zz1)68z(zz) #1A* N 4.613 4.413 4.613 4.413 # 82.832 67.817 15.953 delete me 86. zz 4.613 4.413 4.613 4.413 # 83.946 68.166 16.826 delete me 87. C 4.613 4.413 4.613 4.413 # 85.134 68.991 16.348 delete me 88. Z 4.613 4.413 4.613 4.413 # 85.271 69.299 15.175 delete me 89. #ZM1V9_Z11 #R delete me z(z2x) 70z(z2x). 3z(z2x) 70AA 8.807 2.79 z(z2x)70z(zz) #1A* N 8.807 2.79 8.807 2.79 # 89.401 69.836 16.016 delete me 90. zz 8.807 2.79 8.807 2.79 # 90.625 69.124 15.711 delete me 91. C 8.807 2.79 8.807 2.79 # 91.786 69.995 16.1V9 delete me 92. Z 8.807 2.79 8.807 2.79 # 91.907 71.13 15.697 delete me 93. zF1 8.807 2.79 8.807 2.79 # 90.728 68.817 1V.206 delete me 94. zF1 8.807 2.79 8.807 2.79 # 92.041 68.106 13.848 delete me 95. zF2 8.807 2.79 8.807 2.79 # 89.561 67.956 13.764 delete me 96. #ZM1V9_Z12 #R delete me z(zz1) 85z(z2x). 3z(zz1) 85AA 8.596 7.659 z(zz1)85z(zz) #1A* N
Sheet16
 
Last edited:
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
This macro with 1 small adaption works fine, it leaves every line which is not starting with alphabetics

VBA Code: 
Sub jec()
 Dim ar, jv, sp As Variant, sq As Long, i As Long, j As Long
 ar = Range("C1", Range("C" & Rows.Count).End(xlUp))
 ReDim jv(1 To UBound(ar))
 
 For i = 1 To UBound(ar)
     sp = Split(ar(i, 1), vbLf)
     For j = 0 To UBound(sp)
        If Not Trim(sp(j)) = "" Then
           sq = Asc(Left(LTrim(sp(j)), 1))
            If sq < 65 Or sq > 90 And sq < 97 Or sq > 122 Then
                jv(i) = jv(i) & LTrim(sp(j)) & vbLf
            End If
            If j = UBound(sp) And Right(jv(i), 1) = vbLf Then jv(i) = StrReverse(Replace(StrReverse(jv(i)), vbLf, "", 1, 1))
       End If
     Next
 Next
 
 Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Transpose(jv)
End Sub
 
Upvote 0
JEC said:
This macro with 1 small adaption works fine, it leaves every line which is not starting with alphabetics

VBA Code: 
Sub jec()
 Dim ar, jv, sp As Variant, sq As Long, i As Long, j As Long
 ar = Range("C1", Range("C" & Rows.Count).End(xlUp))
 ReDim jv(1 To UBound(ar))
 
 For i = 1 To UBound(ar)
     sp = Split(ar(i, 1), vbLf)
     For j = 0 To UBound(sp)
        If Not Trim(sp(j)) = "" Then
           sq = Asc(Left(LTrim(sp(j)), 1))
            If sq < 65 Or sq > 90 And sq < 97 Or sq > 122 Then
                jv(i) = jv(i) & LTrim(sp(j)) & vbLf
            End If
            If j = UBound(sp) And Right(jv(i), 1) = vbLf Then jv(i) = StrReverse(Replace(StrReverse(jv(i)), vbLf, "", 1, 1))
       End If
     Next
 Next
 
 Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Transpose(jv)
End Sub
Click to expand...

Showing runtime error 13 and highlights the below line in yellow:
Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Transpose(jv)
 
Upvote 0
Not with your sample data...
Did you run this from the right sheet? I didnt't refer to specific sheets for this example
 
Upvote 0
Yes. I added a few more lines and the below line gets highlighted in yellow:
ReDim jv(1 To UBound(ar))
JEC said:
Not with your sample data...
Did you run this from the right sheet? I didnt't refer to specific sheets for this example
Click to expand...
 
Upvote 0
Well from which sheet do you run this and is that one acticated?
 
Upvote 0
And the data is in column C still?
You really need to provide some more info if you want this to work
 
Upvote 0
JEC said:
And the data is in column C still?
You really need to provide some more info if you want this to work
Click to expand...
I assure the code does not run successfully with the data copied with XL2BB. I am using excel 2007.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
530
Akuini
Akuini
C
  • Solved
Count number of non-empty lines in a multiline cell
2
Replies
11
Views
292
Fluff
Fluff
E
  • Question Question
Removing Blank Cells
Replies
2
Views
106
Peter_SSs
Peter_SSs
O
  • Question Question
How to sort table with VBA?
Replies
1
Views
86
Fluff
Fluff
S
  • Question Question
Excel Drop Down Multiple Select/Remove & Auto Alphabetize Selections
Replies
0
Views
306
Sactown Steve
S

Forum statistics

Threads
1,224,944
Messages
6,181,927
Members
453,072
Latest member
jtees4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top