range selection using VBA

[Andreas1]

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

 

[Andreas1]

can anyone help me on this please???

 

[wigi]

Hello

Is it each time the same sequence of persons in column A?
What are the contents of the date cells? Are these real dates or just plain text?

 

[Andreas1]

Hello Wigi,

this is just an example of an export when I retrieve data. In column A the range of persons is not the same each time.
the contents of the cells are numbers

the trick in this export is that each table refer to a month (you see the month of each table in column A) so what I want is for each table of data, to isolate only the respective column of data (data in red font in the example)

Thank you very much for your reply, hope you can help me to sort this out!

 

[wigi]

Okay. But I really need the type of contents of cells like Jun 10, Jul 10, ...

What cells are you using on sheet 1?

 

[Andreas1]

I am not sure I understand your question... data in tables are numbers and in column A I will have text... All cells have 'general' formating (this is how these data are extracted from the software i use)

Hope I answered your question...

(needless to say 1.000.000 thanks for looking into it)

 

[wigi]

Hello

This will give you already the basics, and more. You need to name the second sheet Sheet2, or change it in the code (once) if it's named idfferently.

Sub reformatTable()

    Dim ar As Range, lColumn As Long, lRow As Long

    With Sheets("Sheet2")

        .UsedRange.ClearContents
        Range("B1", Range("B1").End(xlToRight)).Copy .Range("B1")

        For Each ar In Columns(2).SpecialCells(2, 1).Areas
            lColumn = .Rows(1).Find(ar.Cells(1).Offset(-1, -1), , xlValues, xlWhole).Column
            lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Cells(lRow, 1).Resize(ar.Rows.Count) = ar.Offset(, -1).Value
            .Cells(lRow, lColumn).Resize(ar.Rows.Count) = ar.Value
        Next

    End With

End Sub

The layout will not be what you need right now, but it will give you ideas to continue yourself.

 

[Andreas1]

the macro seems not working properly...

I got an error msg saying:
"run time error 91. Object variable or with block variable not set"

any ideas how to fix this?

 

[wigi]

Hello,

Here is an updated version of the code:

Sub reformatTable()

    Dim ar As Range, lColumn As Long, lRow As Long

    With Sheets("Sheet2")

        .UsedRange.ClearContents
        Range("B1", Range("B1").End(xlToRight)).Copy .Range("B1")

        For Each ar In Columns(2).SpecialCells(2, 1).Areas
            lColumn = .Rows(1).Find(ar.Cells(1).Offset(-1, -1), , xlValues, xlWhole).Column
            lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Cells(lRow, 1).Resize(ar.Rows.Count) = ar.Offset(, -1).Value
            .Cells(lRow, lColumn).Resize(ar.Rows.Count) = ar.Offset(, lColumn - 2).Value
        Next

    End With

End Sub

The persons will still be duplicated, there is a copy action block per block.

 

[wigi]

Well okay, I also wrote the rest of the code. The output is now the way you stipulated it.

Sub reformatTable()

    Dim lRow As Long, lColumn As Long, rngCell As Range, lNextRow As Long, sDay As String

    With Sheets("Sheet2")
        
        'clear this sheet
        .UsedRange.ClearContents
        
        'copy the titles
        Range("B1", Range("B1").End(xlToRight)).Copy .Range("B1")
        
        'copy the data in a cumulative way
        For lRow = 2 To Range("A" & .Rows.Count).End(xlUp).Row
            If Len(Range("B" & lRow).Text) Then
                'transfer the person to sheet 2
                Set rngCell = .Columns(1).Find(Range("A" & lRow).Text, , xlValues, xlWhole)
                If rngCell Is Nothing Then
                    lNextRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row
                Else
                    lNextRow = rngCell.Row
                End If
                .Range("A" & lNextRow).Value = Range("A" & lRow).Text
                .Cells(lNextRow, lColumn) = Range("A" & lRow).Offset(, lColumn - 1).Value
            Else
                sDay = Range("A" & lRow).Text
                lColumn = .Rows(1).Find(sDay, , xlValues, xlWhole).Column
            End If
        Next

    End With

End Sub

Wigi