Selecting consecutive rows (varying) following user defined text..

T

tbucket74

New Member
Joined
Nov 5, 2008
Messages
7
OK...this is my first post here; thanks in advance to all that may assist with my question...here's my dilemma; I have a report that I import into Excel into Column A; beginning at Row 1. I have no need to delimit this as I just want to copy non-blank "consecutive" cells to another worksheet within the same workbook. Just some background on the report as the data may look a little strange. I work for a casino and am developing a means for our Network Engineer to determine available Hexidecimal addresses on slot lines. So what I want to create is a macro that will:

1. prompt him for a slot line number
2. determine if the slot line is valid
3. if slot line is not valid then message indicating so
4. if slot line is valid then go find the info and copy to another sheet for display to the user

Here's a sample of the report after it is imported (column\rows inred):
A
<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0 x:str><COLGROUP><COL style="WIDTH: 507pt; mso-width-source: userset; mso-width-alt: 24722" width=676><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 507pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=676 height=17 x:str=" LINE 1 ">1 LINE 1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="0F 3D 3E 3F 4D 4E 4F 50 51 52 53 54 55 56 57 58 ">3 0F 3D 3E 3F 4D 4E 4F 50 51 52 53 54 55 56 57 58 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="59 5A 5B 5C 5D 5E 62 63 64 65 66 67 68 69 6A 6B ">4 59 5A 5B 5C 5D 5E 62 63 64 65 66 67 68 69 6A 6B </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="6C 6D 6E 6F 70 71 72 73 74 75 76 77 78 79 7A 7B ">5 6C 6D 6E 6F 70 71 72 73 74 75 76 77 78 79 7A 7B </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="7C 7D 7E 7F 80 81 82 83 84 85 86 87 88 89 8A 8B ">6 7C 7D 7E 7F 80 81 82 83 84 85 86 87 88 89 8A 8B </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="9C 9D 9E 9F A0 AD AE AF B0 C1 C2 C3 C4 C5 C6 C7 ">7 9C 9D 9E 9F A0 AD AE AF B0 C1 C2 C3 C4 C5 C6 C7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 ">8 C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 ">9 D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 ">10 E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="F8 F9 FA FB FC FD FE ">11 F8 F9 FA FB FC FD FE </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" 135 TOTAL FREE SPACES ON LINE 1 ">13 135 TOTAL FREE SPACES ON LINE 1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" LINE 2 ">16 LINE 2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>17</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="05 06 07 08 09 0A 0B 0C 0D 0E 0F 10 11 12 13 14 ">18 05 06 07 08 09 0A 0B 0C 0D 0E 0F 10 11 12 13 14 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="15 16 17 18 19 1A 1B 1C 1D 1E 1F 20 21 22 23 24 ">19 15 16 17 18 19 1A 1B 1C 1D 1E 1F 20 21 22 23 24 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="25 26 27 28 29 2A 2B 2C 2D 2E 2F 30 31 32 33 34 ">20 25 26 27 28 29 2A 2B 2C 2D 2E 2F 30 31 32 33 34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="35 36 37 38 39 3A 3B 3C 3D 71 72 73 74 75 76 77 ">21 35 36 37 38 39 3A 3B 3C 3D 71 72 73 74 75 76 77 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="78 79 7A 7B 7C 7D 7E 7F 80 81 82 83 84 85 86 87 ">22 78 79 7A 7B 7C 7D 7E 7F 80 81 82 83 84 85 86 87 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="88 89 8A 8B 8C 8D 8E 8F 90 91 92 93 94 95 96 97 ">23 88 89 8A 8B 8C 8D 8E 8F 90 91 92 93 94 95 96 97 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 A7 ">24 98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 A7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="A8 A9 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 ">25 A8 A9 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="B8 B9 BA BB BC BD BE BF C0 C1 C2 C3 C4 C5 C6 C7 ">26 B8 B9 BA BB BC BD BE BF C0 C1 C2 C3 C4 C5 C6 C7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 ">27 C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 ">28 D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 ">29 E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="F8 F9 FA FB FC FD FE ">30 F8 F9 FA FB FC FD FE </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>31</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" 199 TOTAL FREE SPACES ON LINE 2 ">32 199 TOTAL FREE SPACES ON LINE 2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>34</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" LINE 3 ">35 LINE 3 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>36</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="0C 0D 0E 0F 10 11 12 13 38 39 3A 3B 3C 3D 45 46 ">37 0C 0D 0E 0F 10 11 12 13 38 39 3A 3B 3C 3D 45 46 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="47 48 49 4A 4B 4C 4D 4E 4F 58 59 5A 5B 5C 5D 5E ">38 47 48 49 4A 4B 4C 4D 4E 4F 58 59 5A 5B 5C 5D 5E </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="5F 71 72 73 74 75 76 77 78 79 7A 7B 7C 7D 7E 7F ">39 5F 71 72 73 74 75 76 77 78 79 7A 7B 7C 7D 7E 7F </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="80 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 ">40 80 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="97 98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 ">41 97 98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="A7 A8 A9 AA AB AC AD AE AF B0 B1 BA BB BC BD C2 ">42 A7 A8 A9 AA AB AC AD AE AF B0 B1 BA BB BC BD C2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF D0 D3 D4 ">43 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF D0 D3 D4 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="D5 D6 D7 D8 D9 DA DB DC DD DE DF E0 E3 E4 E6 E7 ">44 D5 D6 D7 D8 D9 DA DB DC DD DE DF E0 E3 E4 E6 E7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 ">45 E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="F8 F9 FA FB FC FD FE ">46 F8 F9 FA FB FC FD FE </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>47</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" 151 TOTAL FREE SPACES ON LINE 3 ">48 151 TOTAL FREE SPACES ON LINE 3 </TD></TR></TBODY></TABLE>

So; if the user chooses 2 (for LINE 2); what I want selected and copied to another sheet is this:
A
<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" LINE 2 ">1 LINE 2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="05 06 07 08 09 0A 0B 0C 0D 0E 0F 10 11 12 13 14 ">3 05 06 07 08 09 0A 0B 0C 0D 0E 0F 10 11 12 13 14 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="15 16 17 18 19 1A 1B 1C 1D 1E 1F 20 21 22 23 24 ">4 15 16 17 18 19 1A 1B 1C 1D 1E 1F 20 21 22 23 24 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="25 26 27 28 29 2A 2B 2C 2D 2E 2F 30 31 32 33 34 ">5 25 26 27 28 29 2A 2B 2C 2D 2E 2F 30 31 32 33 34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="35 36 37 38 39 3A 3B 3C 3D 71 72 73 74 75 76 77 ">6 35 36 37 38 39 3A 3B 3C 3D 71 72 73 74 75 76 77 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="78 79 7A 7B 7C 7D 7E 7F 80 81 82 83 84 85 86 87 ">7 78 79 7A 7B 7C 7D 7E 7F 80 81 82 83 84 85 86 87 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="88 89 8A 8B 8C 8D 8E 8F 90 91 92 93 94 95 96 97 ">8 88 89 8A 8B 8C 8D 8E 8F 90 91 92 93 94 95 96 97 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 A7 ">9 98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 A7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="A8 A9 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 ">10 A8 A9 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="B8 B9 BA BB BC BD BE BF C0 C1 C2 C3 C4 C5 C6 C7 ">11 B8 B9 BA BB BC BD BE BF C0 C1 C2 C3 C4 C5 C6 C7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 ">12 C8 C9 CA CB CC CD CE CF D0 D1 D2 D3 D4 D5 D6 D7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 ">13 D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 E5 E6 E7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 ">14 E8 E9 EA EB EC ED EE EF F0 F1 F2 F3 F4 F5 F6 F7 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="F8 F9 FA FB FC FD FE ">15 F8 F9 FA FB FC FD FE </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>16</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" 199 TOTAL FREE SPACES ON LINE 2 ">17 199 TOTAL FREE SPACES ON LINE 2</TD></TR></TBODY></TABLE>

Also; keep in mind that the number of rows following the text "LINE ?" (where ? represents the line chosen); may vary depending on how many open addresses there are. Also; and finally; you may have a Line that has no addresses left over; such as this:

A
<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0 x:str><COLGROUP><COL style="WIDTH: 507pt; mso-width-source: userset; mso-width-alt: 24722" width=676><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 507pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=676 height=17 x:str=" LINE 56 ">1 LINE 56 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=" 0 TOTAL FREE SPACES ON LINE 56 ">4 0 TOTAL FREE SPACES ON LINE 56

Notice that there are no lines with Hexidecimal values after the text LINE 56. I would want this to be copied as the rest; so in this case 4 rows get copied.

Thanks a bunch
Giuliano



</TD></TR></TBODY></TABLE>
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
try
Code: 
Sub test()
Dim r As Range, c As Range, LineNumber, LastR As Range
LineNumber = Application.InputBox("Enter Line Number", type:=1)
If LineNumber = False Then Exit Sub
Set r = Columns(1).Find("LINE " & LineNumber,,,xlWhole)
If r Is Nothing Then
    MsgBox "Invalid number"
    Exit Sub
End If
Set c = Columns(1).Find("LINE*",r,,xlWhole)
Set LastR = Range("a" & Rows.Count).End(xlUp))
If c.Row > r.Row Then Set LastR = c.Offset(,-1)
Range(r, LastR).Copy Sheets("sheet2").Range("a" & Rows.Count).End(xlUp)(2)
Application.CutCopyMode = False
End Sub
 
Upvote 0
Thanks JINDON; I will give it a try. Thanks so much for your time in assisting with my problem.
Regards
Giuliano
 
Upvote 0
Thanks JINDON; just a few tweaks here and there and it worked as intended. Your an ACE !!!!(y)

Super thank you..

Regards
Giuliano
 
Upvote 0
2 typo
1)
Rich (BB code): 
Set LastR = Range("a" & Rows.Count).End(xlUp))

2)
Rich (BB code): 
If c.Row > r.Row Then Set LastR = c.Offset(-1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
212
John_w
J
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
825
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
592
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,874
Messages
6,122,036
Members
449,062
Latest member
mike575

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