Figuring out optimal schedules

[Sal Paradise]

I have a list of dates and what each hour would get in revenue as follows:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">17</td><td style="text-align: right;;">18</td><td style="text-align: right;;">19</td><td style="text-align: right;;">20</td><td style="text-align: right;;">21</td><td style="text-align: right;;">22</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2007/1/1</td><td style="text-align: right;;">-$21.99</td><td style="text-align: right;;">-$22.28</td><td style="text-align: right;;">-$24.20</td><td style="text-align: right;;">-$25.20</td><td style="text-align: right;;">-$25.13</td><td style="text-align: right;;">-$24.16</td><td style="text-align: right;;">-$22.51</td><td style="text-align: right;;">-$21.76</td><td style="text-align: right;;">-$21.23</td><td style="text-align: right;;">-$20.42</td><td style="text-align: right;;">-$19.67</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.39</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.75</td><td style="text-align: right;;">-$19.87</td><td style="text-align: right;;">-$19.47</td><td style="text-align: right;;">-$14.58</td><td style="text-align: right;;">-$13.31</td><td style="text-align: right;;">-$14.60</td><td style="text-align: right;;">-$15.81</td><td style="text-align: right;;">-$16.93</td><td style="text-align: right;;">-$18.58</td><td style="text-align: right;;">-$19.82</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2007/1/2</td><td style="text-align: right;;">-$18.66</td><td style="text-align: right;;">-$19.19</td><td style="text-align: right;;">-$19.63</td><td style="text-align: right;;">-$19.73</td><td style="text-align: right;;">-$19.44</td><td style="text-align: right;;">-$17.93</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$9.03</td><td style="text-align: right;;">-$7.68</td><td style="text-align: right;;">-$5.25</td><td style="text-align: right;;">-$3.34</td><td style="text-align: right;;">-$6.83</td><td style="text-align: right;;">-$9.50</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$14.02</td><td style="text-align: right;;">-$14.67</td><td style="text-align: right;;">-$14.30</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$26.92</td><td style="text-align: right;;">$12.56</td><td style="text-align: right;;">$7.12</td><td style="text-align: right;;">-$1.56</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$14.02</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2007/1/3</td><td style="text-align: right;;">-$15.79</td><td style="text-align: right;;">-$16.54</td><td style="text-align: right;;">-$16.91</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$16.84</td><td style="text-align: right;;">-$14.69</td><td style="text-align: right;;">-$11.63</td><td style="text-align: right;;">-$2.76</td><td style="text-align: right;;">-$2.10</td><td style="text-align: right;;">-$5.39</td><td style="text-align: right;;">-$5.19</td><td style="text-align: right;;">-$9.40</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$13.54</td><td style="text-align: right;;">-$14.53</td><td style="text-align: right;;">-$14.96</td><td style="text-align: right;;">-$14.72</td><td style="text-align: right;;">$2.16</td><td style="text-align: right;;">$12.48</td><td style="text-align: right;;">$1.56</td><td style="text-align: right;;">-$5.17</td><td style="text-align: right;;">-$7.09</td><td style="text-align: right;;">-$14.55</td><td style="text-align: right;;">-$16.03</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2007/1/4</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$18.43</td><td style="text-align: right;;">-$18.91</td><td style="text-align: right;;">-$19.07</td><td style="text-align: right;;">-$18.61</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$13.01</td><td style="text-align: right;;">-$4.21</td><td style="text-align: right;;">-$4.59</td><td style="text-align: right;;">-$8.14</td><td style="text-align: right;;">-$8.56</td><td style="text-align: right;;">-$10.71</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$14.14</td><td style="text-align: right;;">-$14.91</td><td style="text-align: right;;">-$15.39</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$1.65</td><td style="text-align: right;;">$10.45</td><td style="text-align: right;;">-$0.57</td><td style="text-align: right;;">-$6.95</td><td style="text-align: right;;">-$10.58</td><td style="text-align: right;;">-$16.11</td><td style="text-align: right;;">-$16.74</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2007/1/5</td><td style="text-align: right;;">-$18.15</td><td style="text-align: right;;">-$18.92</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.42</td><td style="text-align: right;;">-$18.27</td><td style="text-align: right;;">-$14.38</td><td style="text-align: right;;">-$7.80</td><td style="text-align: right;;">-$8.06</td><td style="text-align: right;;">-$10.27</td><td style="text-align: right;;">-$10.33</td><td style="text-align: right;;">-$10.97</td><td style="text-align: right;;">-$12.89</td><td style="text-align: right;;">-$13.48</td><td style="text-align: right;;">-$14.19</td><td style="text-align: right;;">-$14.63</td><td style="text-align: right;;">-$14.43</td><td style="text-align: right;;">-$7.40</td><td style="text-align: right;;">-$1.32</td><td style="text-align: right;;">-$6.14</td><td style="text-align: right;;">-$10.10</td><td style="text-align: right;;">-$13.70</td><td style="text-align: right;;">-$16.17</td><td style="text-align: right;;">-$17.03</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2007/1/6</td><td style="text-align: right;;">-$18.85</td><td style="text-align: right;;">-$19.16</td><td style="text-align: right;;">-$19.71</td><td style="text-align: right;;">-$20.41</td><td style="text-align: right;;">-$20.59</td><td style="text-align: right;;">-$20.11</td><td style="text-align: right;;">-$19.03</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$13.64</td><td style="text-align: right;;">-$12.69</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$13.56</td><td style="text-align: right;;">-$13.89</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$15.78</td><td style="text-align: right;;">-$16.37</td><td style="text-align: right;;">-$15.41</td><td style="text-align: right;;">-$6.45</td><td style="text-align: right;;">-$4.93</td><td style="text-align: right;;">-$8.22</td><td style="text-align: right;;">-$12.29</td><td style="text-align: right;;">-$13.40</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$18.10</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2007/1/7</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$19.20</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.23</td><td style="text-align: right;;">-$17.99</td><td style="text-align: right;;">-$16.39</td><td style="text-align: right;;">-$14.54</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$13.98</td><td style="text-align: right;;">-$13.95</td><td style="text-align: right;;">-$14.46</td><td style="text-align: right;;">-$14.92</td><td style="text-align: right;;">-$15.36</td><td style="text-align: right;;">-$15.91</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$1.58</td><td style="text-align: right;;">$5.88</td><td style="text-align: right;;">$4.58</td><td style="text-align: right;;">-$3.12</td><td style="text-align: right;;">-$9.62</td><td style="text-align: right;;">-$13.90</td><td style="text-align: right;;">-$18.85</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">2007/1/8</td><td style="text-align: right;;">-$18.50</td><td style="text-align: right;;">-$19.49</td><td style="text-align: right;;">-$19.94</td><td style="text-align: right;;">-$20.04</td><td style="text-align: right;;">-$19.52</td><td style="text-align: right;;">-$17.01</td><td style="text-align: right;;">-$10.56</td><td style="text-align: right;;">$6.69</td><td style="text-align: right;;">$6.16</td><td style="text-align: right;;">$3.96</td><td style="text-align: right;;">$2.96</td><td style="text-align: right;;">$2.53</td><td style="text-align: right;;">$0.67</td><td style="text-align: right;;">-$5.20</td><td style="text-align: right;;">-$7.79</td><td style="text-align: right;;">-$9.83</td><td style="text-align: right;;">-$8.74</td><td style="text-align: right;;">$14.30</td><td style="text-align: right;;">$33.61</td><td style="text-align: right;;">$22.69</td><td style="text-align: right;;">$10.34</td><td style="text-align: right;;">$0.77</td><td style="text-align: right;;">-$11.88</td><td style="text-align: right;;">-$16.30</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">2007/1/10</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$16.30</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$16.28</td><td style="text-align: right;;">-$16.09</td><td style="text-align: right;;">-$13.42</td><td style="text-align: right;;">-$0.24</td><td style="text-align: right;;">$15.66</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$5.82</td><td style="text-align: right;;">$1.15</td><td style="text-align: right;;">-$2.61</td><td style="text-align: right;;">-$6.62</td><td style="text-align: right;;">-$11.36</td><td style="text-align: right;;">-$13.87</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$14.77</td><td style="text-align: right;;">$3.47</td><td style="text-align: right;;">$27.72</td><td style="text-align: right;;">$13.26</td><td style="text-align: right;;">$3.52</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;;">-$13.57</td><td style="text-align: right;;">-$12.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2007/1/11</td><td style="text-align: right;;">-$12.72</td><td style="text-align: right;;">-$12.70</td><td style="text-align: right;;">-$13.10</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$10.83</td><td style="text-align: right;;">$8.47</td><td style="text-align: right;;">$27.11</td><td style="text-align: right;;">$20.18</td><td style="text-align: right;;">$13.98</td><td style="text-align: right;;">$9.22</td><td style="text-align: right;;">$5.00</td><td style="text-align: right;;">$0.15</td><td style="text-align: right;;">-$3.11</td><td style="text-align: right;;">-$8.60</td><td style="text-align: right;;">-$10.75</td><td style="text-align: right;;">-$11.04</td><td style="text-align: right;;">$11.87</td><td style="text-align: right;;">$34.52</td><td style="text-align: right;;">$19.74</td><td style="text-align: right;;">$13.07</td><td style="text-align: right;;">$3.38</td><td style="text-align: right;;">-$9.55</td><td style="text-align: right;;">-$14.88</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2007/1/12</td><td style="text-align: right;;">-$14.42</td><td style="text-align: right;;">-$15.60</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$15.32</td><td style="text-align: right;;">-$15.30</td><td style="text-align: right;;">-$13.30</td><td style="text-align: right;;">$3.88</td><td style="text-align: right;;">$18.76</td><td style="text-align: right;;">$12.43</td><td style="text-align: right;;">$7.30</td><td style="text-align: right;;">$2.85</td><td style="text-align: right;;">-$1.37</td><td style="text-align: right;;">-$5.12</td><td style="text-align: right;;">-$8.15</td><td style="text-align: right;;">-$11.07</td><td style="text-align: right;;">-$12.53</td><td style="text-align: right;;">-$11.73</td><td style="text-align: right;;">$4.21</td><td style="text-align: right;;">$22.51</td><td style="text-align: right;;">$12.80</td><td style="text-align: right;;">$2.51</td><td style="text-align: right;;">-$2.92</td><td style="text-align: right;;">-$13.05</td><td style="text-align: right;;">-$14.36</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">2007/1/13</td><td style="text-align: right;;">-$16.78</td><td style="text-align: right;;">-$17.02</td><td style="text-align: right;;">-$18.30</td><td style="text-align: right;;">-$18.99</td><td style="text-align: right;;">-$18.96</td><td style="text-align: right;;">-$18.31</td><td style="text-align: right;;">-$16.45</td><td style="text-align: right;;">-$14.73</td><td style="text-align: right;;">-$10.54</td><td style="text-align: right;;">-$8.03</td><td style="text-align: right;;">-$7.87</td><td style="text-align: right;;">-$9.58</td><td style="text-align: right;;">-$11.28</td><td style="text-align: right;;">-$12.30</td><td style="text-align: right;;">-$13.92</td><td style="text-align: right;;">-$14.88</td><td style="text-align: right;;">-$13.85</td><td style="text-align: right;;">-$3.02</td><td style="text-align: right;;">$7.88</td><td style="text-align: right;;">-$3.06</td><td style="text-align: right;;">-$8.26</td><td style="text-align: right;;">-$10.53</td><td style="text-align: right;;">-$13.65</td><td style="text-align: right;;">-$15.98</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">2007/1/14</td><td style="text-align: right;;">-$16.94</td><td style="text-align: right;;">-$17.49</td><td style="text-align: right;;">-$18.60</td><td style="text-align: right;;">-$19.10</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.22</td><td style="text-align: right;;">-$18.65</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$16.21</td><td style="text-align: right;;">-$14.52</td><td style="text-align: right;;">-$14.08</td><td style="text-align: right;;">-$14.07</td><td style="text-align: right;;">-$14.93</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$15.51</td><td style="text-align: right;;">-$15.76</td><td style="text-align: right;;">-$14.00</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;;">$9.89</td><td style="text-align: right;;">$8.29</td><td style="text-align: right;;">$1.97</td><td style="text-align: right;;">-$6.55</td><td style="text-align: right;;">-$11.60</td><td style="text-align: right;;">-$15.96</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2007/1/15</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$17.00</td><td style="text-align: right;;">-$17.72</td><td style="text-align: right;;">-$18.02</td><td style="text-align: right;;">-$17.48</td><td style="text-align: right;;">-$16.31</td><td style="text-align: right;;">-$8.35</td><td style="text-align: right;;">-$1.10</td><td style="text-align: right;;">$1.98</td><td style="text-align: right;;">$6.29</td><td style="text-align: right;;">$8.09</td><td style="text-align: right;;">$6.47</td><td style="text-align: right;;">$1.39</td><td style="text-align: right;;">-$3.17</td><td style="text-align: right;;">-$7.37</td><td style="text-align: right;;">-$8.52</td><td style="text-align: right;;">-$7.04</td><td style="text-align: right;;">$12.81</td><td style="text-align: right;;">$39.27</td><td style="text-align: right;;">$26.56</td><td style="text-align: right;;">$13.34</td><td style="text-align: right;;">$5.88</td><td style="text-align: right;;">-$8.94</td><td style="text-align: right;;">-$10.94</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2007/1/16</td><td style="text-align: right;;">-$12.73</td><td style="text-align: right;;">-$14.40</td><td style="text-align: right;;">-$15.15</td><td style="text-align: right;;">-$15.24</td><td style="text-align: right;;">-$15.19</td><td style="text-align: right;;">-$11.16</td><td style="text-align: right;;">$5.19</td><td style="text-align: right;;">$22.62</td><td style="text-align: right;;">$21.09</td><td style="text-align: right;;">$17.01</td><td style="text-align: right;;">$18.36</td><td style="text-align: right;;">$16.65</td><td style="text-align: right;;">$11.58</td><td style="text-align: right;;">$5.02</td><td style="text-align: right;;">-$1.31</td><td style="text-align: right;;">-$3.89</td><td style="text-align: right;;">-$1.01</td><td style="text-align: right;;">$20.76</td><td style="text-align: right;;">$51.17</td><td style="text-align: right;;">$41.38</td><td style="text-align: right;;">$30.23</td><td style="text-align: right;;">$17.81</td><td style="text-align: right;;">-$1.28</td><td style="text-align: right;;">-$3.87</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">2007/1/17</td><td style="text-align: right;;">$10.65</td><td style="text-align: right;;">$0.30</td><td style="text-align: right;;">-$3.75</td><td style="text-align: right;;">-$3.76</td><td style="text-align: right;;">-$3.68</td><td style="text-align: right;;">$14.52</td><td style="text-align: right;;">$43.79</td><td style="text-align: right;;">$51.48</td><td style="text-align: right;;">$36.40</td><td style="text-align: right;;">$30.79</td><td style="text-align: right;;">$32.70</td><td style="text-align: right;;">$27.73</td><td style="text-align: right;;">$17.40</td><td style="text-align: right;;">$9.27</td><td style="text-align: right;;">$2.80</td><td style="text-align: right;;">$0.11</td><td style="text-align: right;;">$1.90</td><td style="text-align: right;;">$27.69</td><td style="text-align: right;;">$55.91</td><td style="text-align: right;;">$46.38</td><td style="text-align: right;;">$33.33</td><td style="text-align: right;;">$24.35</td><td style="text-align: right;;">$6.11</td><td style="text-align: right;;">$2.42</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Test</p><br /><br />

The operation, however, should be consecutive, so I can't just say "schedule for whenever the result is positive". What I would like to do is to write a formula that determines what the optimal schedule is given that constraint.

For instance, on January 10th, from 7-10 I am making a profit, but that profit is less than the loss I would hit from 11-16, so I shouldn't run from 7-10. Instead I should just run from 17-20, because that has a higher profit than the 7-10 chunk.

Is there an easy way to do this with formulas or VBA? Excel 2003 for reference.

 

[lrobbo314]

Plus you can speed it up a little by taking our the r.selects.

I was using those for testing purposes.

Here's the final code.

Function aFact(x As Single)
Dim Total As Long
    Do Until x = 0
        Total = Total + x
        x = x - 1
    Loop
aFact = Total
End Function

Sub MXL()
Application.ScreenUpdating = False
Dim r As Range, c1 As Range, c2 As Range, c0 As Range
Dim iRow As Single, Tot As Single, Ctr1 As Single, Ctr2 As Single, i As Single, y As Single, z As Single, temp As Single
Dim iMax As Single
Dim arr() As Single
iRow = 3
Do Until iRow = 39
Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, 2)
        Set r = r.Resize(1, Tot)
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
iMax = WorksheetFunction.Max(arr())

Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, 2)
        Set r = r.Resize(1, Tot)
            If i = 0 Then
                If WorksheetFunction.Sum(r) = iMax Then
                        r.Interior.ColorIndex = 6
                        GoTo n
                    End If
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    If WorksheetFunction.Sum(r) = iMax Then
                        r.Interior.ColorIndex = 6
                        GoTo n
                    End If
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
n:
    iRow = iRow + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[Sal Paradise]

Thank you very much. Does a great job.

 

[lrobbo314]

Glad that i could help