1) Please consider posting the data in a table or attach a file. It’s a PITA to recreate the data set!
Very good point. I made this function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Public Function MakeHTMLTable(rInput As Range, Optional bHeaders As Boolean = True) As String Dim rRow As Range Dim rCell As Range Dim sReturn As String sReturn = " < Table > """ If bHeaders Then sReturn = sReturn & "<tr><td> </td>" For Each rCell In rInput.Rows(1).Cells sReturn = sReturn & " < td > " & Chr$(rCell.Column + 64) & "</td>" Next rCell sReturn = sReturn & "</tr>" End If For Each rRow In rInput.Rows sReturn = sReturn & " < tr > """ If bHeaders Then sReturn = sReturn & " < td > " & rRow.Row & "</td>" End If For Each rCell In rRow.Cells sReturn = sReturn & " < td > " & rCell.Text & "</td>" Next rCell sReturn = sReturn & "</tr>" & vbNewLine Next rRow sReturn = sReturn & "</table>" MakeHTMLTable = sReturn End Function |
With this data
this in the Immediate Window
produces this
| C | D | |
| 3 | Name | Number |
| 4 | Bob | one |
| 5 | Jim | two |
| 6 | bob | three |
| 7 | bob | four |
And this in the Immediate Window
produces this
| Name | Number |
| Bob | one |
| Jim | two |
| bob | three |
| bob | four |
Nothing fancy, but it gets the job done. Well almost. I got a little lazy converting my column numbers to letters. If I need to go beyond column Z, I’ll need to rewrite the code to use one of these.
Dick –
Small improvements:
Change sReturn=<table> to sReturn = <table border=1 rules=all cellpadding=”5?>
In both IF bHeaders statements change your <td> to <th align=”center”> and your </td> to </th>
In For each rCell in rRow.Cells add an IF
IF IsNumeric(rcell.value) then
sReturn = sReturn & “<td align = “right”>” & rCell.Text & “”
Else etc.
Thank you for this.
…mrt©¿©¬
Those are curly quotes above.
…mrt
And they shouldn’t be, and the parser ate the final <td> in the If IsNumeric part.
Dick –
This may have occurred to you:
Dim DataObj As New MSForms.DataObject”
…
DataObj.SetText sReturn
DataObj.PutInClipboard
…mrt
Michael
If you wrap your code in “code” tags it should work:
replace [ with < and ] with >
[code lang=”html”]
–>
[…] a comment.Download StatsHere are the download stats for the addins on the site. (table courtesy of DDoE)File NameDownloads Since […]
Hi,
Thank you very much for the code.
I adapted and improved the code, using BB code tags, to make a table in forums.
See posts 102 to 107 at
http://www.excelforum.com/showthread.php?t=949916&p=3387493&viewfull=1#post3387493
Thanks again
Marcelo