range selection using VBA

Andreas1

New Member
Joined
Oct 2, 2011
Messages
17
I am novice in VBA and I need your help!!!!

In sheet1 I have a list of data (row/col length is variable each time)
(see below example)

<table style="width: 452px; height: 415px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:112pt" height="17" width="149">Sales report</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:25.5pt" height="34"> <td class="xl24" style="height:25.5pt;width:112pt" height="34" width="149">table produced</td><td class="xl25" style="width:48pt" width="64">Jun 10</td> <td class="xl25" style="width:48pt" width="64">Jul 10</td> <td class="xl25" style="width:48pt" width="64">Aug 10</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Jun 10</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas</td> <td class="xl26">100</td> <td class="xl27">100</td> <td class="xl27">100</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl26">25</td> <td class="xl27">12</td> <td class="xl27">56</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl26">25</td> <td class="xl27">23</td> <td class="xl27">67</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl26">25</td> <td class="xl27">34</td> <td class="xl27">78</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl26">25</td> <td class="xl27">45</td> <td class="xl27">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">[empty line]
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Jul 10
Andreas
</td> <td>
100
</td> <td>
100
</td> <td>
100
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl27">13</td> <td class="xl26">30</td> <td class="xl27">98</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl27">24</td> <td class="xl26">20</td> <td class="xl27">87</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl27">35</td> <td class="xl26">15
</td> <td class="xl27">76</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl27">46</td> <td class="xl26">35</td> <td class="xl27">65</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">[empty line]</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Aug 10
Andreas
</td> <td>
100
</td> <td>
100
</td> <td>
100
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl27">32</td> <td class="xl27">98</td> <td class="xl26">10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl27">54</td> <td class="xl27">74</td> <td class="xl26">10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl27">65</td> <td class="xl27">25</td> <td class="xl26">70</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl27">76</td> <td class="xl27">43</td> <td class="xl26">10</td> </tr> </tbody></table>[empty line]

As you see in each set of data there is a empty line. What I want is to gather data in a new spreadsheet (coule be sheet2) so to have one table with data of the respective months (data with red fonts).

<table border="0" cellpadding="0" cellspacing="0"><tbody><tr height="17"><tr style="height:25.5pt" height="34"><td class="xl24" style="height:25.5pt;width:112pt" height="34" width="149">
</td><td class="xl25" style="width:48pt" width="64">
</td><td class="xl25" style="width:48pt" width="64">
</td><td class="xl25" style="width:48pt" width="64">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">table produced
</td><td>Jun 10
</td><td>Jul 10
</td><td>Aug 10
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas</td><td class="xl26">100</td><td class="xl27">100</td><td class="xl27">100</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 1</td><td class="xl26">25</td><td class="xl27">30
</td><td class="xl27">10</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 2</td><td class="xl26">25</td><td class="xl27">20</td><td class="xl27">10</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 3</td><td class="xl26">25</td><td class="xl27">15</td><td class="xl27">70</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas4</td><td class="xl26">25</td><td class="xl27">35</td><td class="xl27">10</td></tr></tr></tbody></table>
I want the macro to be dynamic as I may have more columns / rows but the idea is the same...

Looking forward hearing any ideas!!
Thank you in advance
 
thank you very much wigi!!! sorry for the missleading example...it would be easier if I could send the example in the first place

Thanks once more!!!
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
sorry for the missleading example.

No worries, in Athens there are much more important things to worry about, sadly enough :-(
Hope life is not too much impacted for you.
 
Upvote 0

Forum statistics

Threads
1,224,518
Messages
6,179,256
Members
452,901
Latest member
LisaGo

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