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:
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 !!!!:beerchug:

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

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
954
smozgur
smozgur
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
831
John_w
J
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
1K
epactheactor
E

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