+ Reply to Thread
Page 1 of 2 1 2
Results 1 to 200 of 243

How to post a range - headers and data?

  1. #1
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    How to post a range - headers and data?

    Hi,

    How to post a range of my worksheet keeping the alignment of the cells (rows/columns)?

    I tried using IE10: select range; put borders; copy; paste in the forum reply page.

    But it didn't work as expected - the cells were not aligned with headers and the spacing was messed up.

    I tried also Go Advanced > Table but i could see only html tags...

    Am i missing something or is always preferable attach a file?

    Thanks in advance for any help.

    Marcelo
    Marcelo Branco

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to post a range - headers and data?

    The easiest way is to use [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags. You might have to make slight adjustments.

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Ben,

    Thank you for the tip.

    I'll try the CODE tags.

    MArcelo

  4. #4
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Just testing

    Please Login or Register  to view this content.
    It seems ok

    Thanks again, Ben

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Let me test a couple of samples...

    Please Login or Register  to view this content.
    If I make that 1st column wider:

    Please Login or Register  to view this content.
    As you can see it only looks OK when the data is of a relatively uniform width.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Hi Biff,

    Nice to "see" you

    Yes, I understand what you are saying. It seems that not always is possible to post a nice and understandable table using copy and paste.

    Do you know how to do tables like these?
    http://www.excelforum.com/excel-form...ple-cells.html

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Unfortunately, I don't know how to make those kinds of tables. Wished I did.

    They use html table tags.

    EDIT: Correction. They use the BB code tags.

    At that link, on the top post, click the "Reply With Quote" button and you'll see the code used to produce the tables.

    I don't understand it at all!

    If anyone that reads this post knows how to make those kinds of tables how about posting a little tutorial for us?
    Last edited by Tony Valko; 08-25-2013 at 11:14 AM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Editing that thread to see what they did gives info that to me, make it seem easier to just post a sample W/B lol...
    (I may end up editing/posting a cpl of times to show the code)

    [table="width: 200, class: grid, align: left" "]"
    [tr]
    [td]cat[/td]
    [td]pet store 1[/td]
    [/tr]
    [tr]
    [td]dog[/td]
    [td]pet store 1[/td]
    [/tr]

    and that was just for the 1st 2 rows!!
    Last edited by FDibbins; 08-24-2013 at 05:44 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to post a range - headers and data?

    tr = table row
    td = table data (cell)

    the [td][/td] 's must be wrapped by [tr][/tr] in all cases. Note that the data to be displayed must be within [td][/td] tags (e.g.) [td]Ben[/td]

    here's one with two rows, 3 columns each
    Please Login or Register  to view this content.
    results in:



    here's one with three rows, row one has tow columns, row two has only one and row three has five:


    Please Login or Register  to view this content.
    resulting in:
    Last edited by protonLeah; 08-24-2013 at 06:04 PM.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Thanks Ben - like I said - almost easier to post a sample workbook

  11. #11
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    TESTING

    < Table > <tr><td> </td><td>A</td><td>B</td></tr> < tr > <td>1</td><td>Name</td><td>Scores</td></tr>
    < tr > <td>2</td><td>John</td><td>30</td></tr>
    < tr > <td>3</td><td>Mary</td><td>32</td></tr>
    < tr > <td>4</td><td>Mike</td><td>34</td></tr>
    < tr > <td>5</td><td>William</td><td>36</td></tr>
    </table>

    I found this in the internet
    http://dailydoseofexcel.com/archives...-from-a-range/

    But...no success...
    Last edited by mlcb; 08-24-2013 at 06:46 PM.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Entia non sunt multiplicanda sine necessitate

  13. #13
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Name Scores
    John 30
    Mary 32
    Mike 34
    William 36

    With some adjustment in the code...

    Success!

    New code

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    I don't know why the code is not appearing correctly

    I used CODE tags, but something is wrong (???)

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    your code looked fine to me?

  16. #16
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by FDibbins View Post
    your code looked fine to me?
    Can you see the entire code? I cannot (???) ---> using IE10

    I can see only using Reply With Quote. Is it a normal behavior?

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Let me try Marcelo's code...

    A B C D
    1 Name Region Sales
    2 Pat North 100
    3 Put East 200
    4 Pit South 300
    5 Pot West 400

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Hmmm...

    I messed up and made the range A1:D5 which is why that empty column is in there.

    Here it is again with the correct range A1:C5...

    A B C
    1 Name Region Sales
    2 Pat North 100
    3 Put East 200
    4 Pit South 300
    5 Pot West 400

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    This looks very promising.

    Nice job, Marcelo!

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    Can you see the entire code? I cannot (???) ---> using IE10

    I can see only using Reply With Quote. Is it a normal behavior?
    No, I can't see it either in IE9 but I was able to get all of it doing the "Reply With Quote" trick.

  21. #21
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Some more testing...

    Pat North 100
    Put East 200
    Pit South 300
    Pot West 400

  22. #22
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    More testing...

    Name Date In Out
    Pat 8/24/2013 7:05 AM 3:55 PM
    Put 8/24/2013 6:48 AM 3:05 PM
    Pit 8/24/2013 7:00 AM 3:30 PM
    Pot 8/24/2013 7:08 AM 3:17 PM


    Name Date In Out
    Pat 8/24/2013 7:05 AM 3:55 PM
    Put 8/24/2013 6:48 AM 3:05 PM
    Pit 8/24/2013 7:00 AM 3:30 PM
    Pot 8/24/2013 7:08 AM 3:17 PM

  23. #23
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    This looks very promising.

    Nice job, Marcelo!
    Wow... it works!!!

    Thank you, Tony

    M.

  24. #24
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Some more testing...

    Name Date In Out
    Pat 8/24/2013 7:05 AM 3:55 PM
    Put 8/24/2013 6:48 AM 3:05 PM
    Pit 8/24/2013 7:00 AM 3:30 PM
    Pot 8/24/2013 7:08 AM 3:17 PM

  25. #25
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Testing using Font colors

    A B
    1 Name Scores
    2 John 30
    3 Mary 32
    4 Mike 34
    5 William 36

  26. #26
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Follows the code

    Use Reply with Quote to see the entire code

    Please Login or Register  to view this content.

  27. #27
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    a b
    1 9 59
    2 10 37
    3 8 76
    4 83 95
    5 49 6

  28. #28
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    a b
    1 9 59
    2 10 37
    3 8 76
    4 83 95
    5 49 6

  29. #29
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Wonder why the column headers are showing in lowercase?

  30. #30
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    A B
    1 Data Data
    2 Data Data
    3 Data Data
    4 Data Data
    5 Data Data


  31. #31
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    a b c
    1 1 9 59
    2 2 10 37
    3 3 8 76
    4 4 83 95
    5 5 49 6

    I'll check the code

  32. #32
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Blah Blah

    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

  33. #33
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    a b c
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

    The code generates letters in uppercase, but If the table is first thing in the post, for some reason beyond my imagination, the column letters appear in lowercase.


  34. #34
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Data Range
    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6


    I just changed the last code line to
    RangeToHTML = "Data Range" & vbNewLine & sReturn

  35. #35
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    CORRECTION

    The previous codes are not setting correctly the Font colors

    New code

    HTML Code: 

  36. #36
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony,

    Do you mind, if you have time, to do a test?

    1. Create a Module in your Personal Workbook and put the Sub and the Function below in the new module
    (You must add a Reference to Microsoft Forms 2.0 Object Library)

    HTML Code: 
    2. Add a new command (Macros) in the QAT, linking to the Sub CopyRngToHTML in your Personal Workbook.

    Then to post a table all you have to do is:
    Select the Range
    Click in the new icon (QAT)
    Paste in the Forum Reply page.

    It worked for me. Please, check if it works to you too.

    M.

  37. #37
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    More testing (user defines table width via Input Box)

    Data Range
    A B
    1 Name Scores
    2 John 30
    3 Mary 32
    4 Mike 34
    5 William 36

  38. #38
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    Tony,

    Do you mind, if you have time, to do a test?
    Here goes...

    Data Range
    A B
    1 Name Status
    2 Pit Pass
    3 Pat Fail
    4 Pot Fail
    5 Put Pass

    EDIT: Tested in Excel 2010
    Last edited by Tony Valko; 08-26-2013 at 09:37 AM.

  39. #39
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    More testing (user defines table width via Input Box)
    Yeah, I like having that option.

    That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:

    [Table="width: 500, class: grid"]

  40. #40
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    Yeah, I like having that option.

    That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:

    [Table="width: 500, class: grid"]

    Testing new version - user defines width=300

    Data Range
    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6


    New code to Personal Workbook

    HTML Code: 

    Next improvement: I think (not tested yet) the code doesn't work with double or triple letters columns (AA or AAA), but I can envisage an easy solution

  41. #41
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Test with input box...

    Data Range
    A B
    1 Name Score
    2 Biff 71
    3 Tom 77
    4 Paul 82
    5 Hoppy 88

  42. #42
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Data Range
    A B
    1 Name Score
    2 Biff 71
    3 Tom 77
    4 Paul 82
    5 Hoppy 88

  43. #43
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Can you set the justification according to cell contents?

    HTML5 apparently doesn't accept align=right|left|center

  44. #44
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Data Range
    A B C
    1
    3
    2
    4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

    I aligned only the first row ---> center.
    I did it manually but I think is possible to include in the code (to all rows)
    Next improvement...

  45. #45
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    I meant alignment by cell, Marcelo -- text left, numbers right, errors and Booleans center.

  46. #46
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    EDIT:Hadn't seen #45

    shg

    Columns and Rows and Booleans---> Center
    Numbers ---> Right
    Text ---> Left

    Is it?

    Requires some IFs to check cell contents...I'll try soon

    M.

  47. #47
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Requires some IFs to check cell contents
    Marcelo,

    VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError

  48. #48
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    shg

    What do you suggest if the cell has a number formatted as text? Something like
    '1000

    I was thinking in IsNumeric, but I think it will result True for numbers as Numbers and also to numbers as Text

    Any suggestion?

    M.

  49. #49
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    Marcelo,

    VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError
    What VarType returns for '1000? vbDouble or vbString?

    M.

  50. #50
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    I checked: returns 8 ---> Text

    M.

  51. #51
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    ....^....1
    Last edited by shg; 08-26-2013 at 01:56 PM.

  52. #52
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    I would do what Excel does by default -- left-align numeric strings, like all other text.

  53. #53
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Tested in Excel 2002...

    Data Range
    A B C
    1 Date Debit Credit
    2 8/1/2013 21.00
    3 8/2/2013 51.00
    4 8/5/2013 80.00 82.00
    5 8/8/2013 12.00
    6 8/8/2013 55.00 52.00
    7 8/15/2013 65.00 44.00
    8 8/16/2013 35.00 61.00
    9 8/16/2013 91.00
    10 8/26/2013 69.00 35.00

  54. #54
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Re: How to post a range - headers and data?

    I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.
    Not all forums are the same - seek and you shall find

  55. #55
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    I would do what Excel does by default -- left-align numeric strings, like all other text.
    Ok, i'll try tonight in my next version (i have to work a little bit on my job or ...... FIRED )



    New version dealing with 2/3 letters columns (added a new function)

    Data Range
    Z AA AB
    1 Name Score Rank
    2 Anthony 34 2
    3 John 30 5
    4 Mary 33 3
    5 Mike 38 1
    6 Robert 32 4

    HTML Code: 

  56. #56
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Just did a copy/paste back to Excel test to make sure no html "junk" shows up in the empty cells.

    Looks good!

  57. #57
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    oops i copied the old Sub

    This is the correct version

    HTML Code: 

  58. #58
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Simon Lloyd View Post
    I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.
    I tried in Excel 2007
    The resulting table code:

    <table border=0 cellpadding=0 cellspacing=0 width=192 style='border-collapse:
    collapse;table-layout:fixed;width:144pt'>
    <col width=64 span=3 style='width:48pt'>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 width=64 style='height:15.0pt;width:48pt'>Name</td>
    <td class=xl159881 width=64 style='width:48pt'>Score</td>
    <td class=xl159881 width=64 style='width:48pt'>Rank</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Anthony</td>
    <td class=xl159881 align=right>34</td>
    <td class=xl159881 align=right>2</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl649881 style='height:15.0pt'>John</td>
    <td class=xl649881 align=right>30</td>
    <td class=xl649881 align=right>5</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Mary</td>
    <td class=xl159881 align=right>33</td>
    <td class=xl159881 align=right>3</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl639881 style='height:15.0pt'>Mike</td>
    <td class=xl639881 align=right>38</td>
    <td class=xl639881 align=right>1</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Robert</td>
    <td class=xl159881 align=right>32</td>
    <td class=xl159881 align=right>4</td>
    </tr>
    <![if supportMisalignedColumns]>
    <tr height=0 style='display:none'>
    <td width=64 style='width:48pt'></td>
    <td width=64 style='width:48pt'></td>
    <td width=64 style='width:48pt'></td>
    </tr>
    <![endif]>
    </table>

    Am i missing something?

  59. #59
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    This is already a great tool and I will be using it extensively (have used it several times today already).

    Hats off to Marcelo for the work he has done on this!

  60. #60
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    This is already a great tool and I will be using it extensively (have used it several times today already).

    Hats off to Marcelo for the work he has done on this!
    Tony,

    Thank you very much for your kind words.

    I am very happy for creating a tool, i think, will be very useful for many members.

    But, as shg asked for a more sophisticated version, i.e., aligning according cells contents, here it goes:
    Numbers --> Right
    Text --> Left
    Boolean --> Center


    Data Range
    A
    B
    C
    D
    1
    Name
    Scores
    V/F
    Date
    2
    John
    30
    VERDADEIRO
    01/01/2013
    3
    Mary
    33
    FALSO
    02/01/2013
    4
    Mike
    34
    FALSO
    03/01/2013
    5
    William
    36
    VERDADEIRO
    04/01/2013

    Portuguese -- English
    VERDADEIRO ---> TRUE
    FALSO ---> FALSE

    Code to Personal Workbook

    HTML Code: 
    That's it

    M.

  61. #61
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Test
    Numbers as Text (red)
    Numbers as Numbers (blue)

    Data Range
    N
    O
    1
    Invoice#
    Value
    2
    0001
    100
    3
    0002
    110
    4
    0003
    120

  62. #62
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Test with dates (fake/real)

    Data Range
    AD
    AE
    1
    Date (text)
    Real Dates
    2
    jun-01
    jun-01
    3
    jun-02
    jun-02
    4
    jun-03
    jun-03

  63. #63
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Is possible to center the headers (first data row) if any. Would require another question to the user.

    Your data have headers?

    It could be done through a Message Box - very easy but maybe boring (2 questions...).

    Or, more sophisticated: by a mini-UserForm (asking about table width and data headers) created on-the-fly and deleted when the routine ends.

    I do not think it's worth it. Too much work for little gain ... (any volunteer?)

    M.

  64. #64
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Personally, alignment is not that important to me.

    In the Excel files I find it easier to read if everything (dates, numbers, text, Booleans, errors) is aligned right.

  65. #65
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Maybe important for help some guys trying to MATCH/VLOOKUP numbers against data range containing fake numbers or dates (text).

    If they use this tool, would be very easy to identify the problem.

  66. #66
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to post a range - headers and data?

    In that case you'd better ask them to post a sample workbook....



  67. #67
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Yes, the facility of attach a workbook is probably the best option.

    But as i usually do tests in the same workbook for different threads, and also as some forums don't allow file uploading, i think this simple tool can be helpful in some cases.

  68. #68
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    When you're done tweaking the code and have arrived at the final version I suggest that you (or someone that wants to do it) write a tutorial on how to apply this tool. The tutorial title could be something like: "How to post sample data in a reply".

    For example, some folks may not know how to set the reference to the MS Forms 2.0 Object Library. They may not know how/where to insert a new module. Etc., etc., etc.

    Then, I would request to the mods/admins that this tutorial be "pinned" to the top of the Tips /Tutorials forum so that it will always be easy to locate.

  69. #69
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony,

    I think that would be best suited to someone who has English as his native language

    Some ideas:

    - Useful links

    Getting Started with VBA
    http://msdn.microsoft.com/en-us/library/ee814737 (v = office.14). aspx

    How to Create and update your Personal Workbook
    http://office.microsoft.com/en-001/e...102174076.aspx


    - Tips

    How to add a new Module to your Personal Workbook
    Alt + F11 to open the VBA Editor
    On the left panel click in VBAProject (Personal.xlsb)
    Go to Insert menu and pick Module

    How to add a Reference to Microsoft Forms 2.0 Object Library
    Alt + F11 to open the VBA Editor
    Go to Tools menu pick References
    Press the Search button
    type in box
    FM20.dll
    press the button Open


    Follows the most recent and updated version

    HTML Code: 
    Hope that someone can do the job.

    Regards,

    Marcelo

  70. #70
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Another useful link

    How to Add a Macro to the Quick Access Toolbar
    http://www.dummies.com/how-to/conten...ck-access.html

  71. #71
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    One last test...


    Data Range
    A B C D
    2 100 300 8
    3 7
    4 200
    5 2
    6 300
    7 8
    8 400
    9 9
    10 500
    11 6
    12

  72. #72
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Please, disregard
    Last edited by mlcb; 08-27-2013 at 04:47 PM.

  73. #73
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony,

    You don't like the version with data cell alignment. Ok, but I think you can use this version with column letters and row numbers centered.



    Data Range
    A
    B
    C
    D
    2
    100 300 8
    3
    7
    4
    200
    5
    2
    6
    300
    7
    8
    8
    400
    9
    9
    10
    500
    11
    6


    HTML Code: 

  74. #74
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.

    I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"

    I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?
    Last edited by shg; 08-27-2013 at 05:13 PM.

  75. #75
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.

    I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"

    I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?
    shg,

    Feel free to add any feature and do improvements and post it.

    I would appreciate very much!

    About your question related to auto-size: my knowledge about HTML is almost none. I only know the basic of the first versions, i.e., I know nothing!

    Questions:
    1. i tried many ways (to no avail) to set the background color of the cells. Could you envisage a solution?

    2. Is there a VBA limit to the size of the string variable sReturn used inside the Function?

  76. #76
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.

    2. As big as you have memory for. (I just generated a 134MB string)
    Last edited by shg; 08-27-2013 at 08:17 PM.

  77. #77
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    the code doesn't work with double or triple letters columns (AA or AAA),
    Please Login or Register  to view this content.
    E.g., =ColLtr(337973)

  78. #78
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.
    E.g., =ColLtr(337973)
    I was talking about my first version. The present version works fine with 2/3 letters columns using this function

    HTML Code: 

    Data Range
    ZZ
    AAA
    AAB
    1
    Name
    Scores
    Rank
    2
    John
    30
    4
    3
    Mary
    33
    3
    4
    Mike
    34
    2
    5
    William
    36
    1

  79. #79
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.
    Like Apollo 13 the only thing I can say is:

    Houston, we have a problem!


    2. As big as you have memory for. (I just generated a 134MB string)
    Good news!

    M.

  80. #80
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Finally I spotted the thread from where I got the conversion function -: Number to Column Letter(s) - and copied to my Personal Workbook.

    http://www.mrexcel.com/forum/excel-q...er-letter.html

    Thank you VoG!

  81. #81
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.
    shg,

    I am (we are) NOT using HTML. We are using BB Codes that is very limited when compared to HTML.

    See
    http://www.bbcode.org/

    At a forum specific page (not at the forum open page) , at right bottom , you can find a box Forum Permissions and a link: BB code is On
    http://www.excelforum.com/misc.php?do=bbcode

    or

    http://www.mrexcel.com/forum/misc.php?do=bbcode

    Therefore it means that we cannot think in sophisticated features.

    M.
    Last edited by mlcb; 08-28-2013 at 06:19 AM.

  82. #82
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    Tony,

    You don't like the version with data cell alignment. Ok, but I think you can use this version with column letters and row numbers centered.


    HTML Code: 
    When I try that code I get a Compile Error: Sub or Function not defined.

    In this line of code:

    HTML Code: 
    ColLetters is highlighted as the offending item.
    Last edited by Tony Valko; 08-28-2013 at 09:50 AM.

  83. #83
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post

    Therefore it means that we cannot think in sophisticated features.
    The more computing I do the more I find that to be true.

    Seems as though almost nothing goes perfectly as planned! There's always some little glitch that you have to deal with (if you CAN deal with it at all).

  84. #84
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    When I try that code I get a Compile Error: Sub or Function not defined.

    In this line of code:

    HTML Code: 
    ColLetters is highlighted as the offending item.
    Add this function to the Module - so the main function can deal with 2/3 letters columns.

    HTML Code: 

  85. #85
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Data Range
    A
    B
    C
    D
    E
    1
    ya ba da ba do
    2
    81 53 5 24 70
    3
    81 77 71 34 89
    4
    20 58 42 25 72
    5
    63 50 12 71 31




    Data Range
    ZY
    ZZ
    AAA
    AAB
    AAC
    1
    ya ba da ba do
    2
    81 53 5 24 70
    3
    81 77 71 34 89
    4
    20 58 42 25 72
    5
    63 50 12 71 31
    Last edited by Tony Valko; 08-28-2013 at 02:43 PM.

  86. #86
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    That fixed it.

    Looks good!

  87. #87
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony,

    Two great improvements suggested by Rory (Mr. Excel Forum) and implemented

    1. Table autosize
    No need of the InputBox asking table widht

    2. No need to add a new Reference
    Uses late binding




    Data Range
    A
    B
    C
    D
    E
    1
    Name
    Dept
    Date
    Score
    Rank
    2
    Anthony
    Dept1
    28/08/2013
    30
    6
    3
    John
    Dept2
    28/08/2013
    32
    4
    4
    Mark
    Dept3
    28/08/2013
    31
    5
    5
    Mary
    Dept4
    28/08/2013
    34
    2
    6
    Michael
    Dept5
    28/08/2013
    35
    1
    7
    Robert
    Dept6
    28/08/2013
    33
    3


    New code
    HTML Code: 

  88. #88
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony and shg,

    Now i think we have a workable tool that could be posted to the community.

    Providing the links about Personal Workbook and How to Add a command (macro) to QAT, I think that even an average user can easily install and use it.

    I'm a new guy in this forum, so maybe you can do the suggestion to the mods.

    New improvements, provided by shg, could be added at any moment.

    Is it ok to you two?

    M.

  89. #89
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Testing the latest version...

    I added a little tweak to make the column and row headers appear in bold.

    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1.00
    U

  90. #90
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Using a smaller font...


    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1.00
    U

  91. #91
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Looks very good to me.

    I've already used it in several posts the last couple of days.


  92. #92
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    I forgot align errors = center

    Need a small adjustment in Select Case

    HTML Code: 

  93. #93
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Testing the latest version...

    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U

    Using a smaller font...


    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U


    Looks good!

  94. #94
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    One more time...

    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U



    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U

  95. #95
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Test with text with spaces

    Data Range
    A
    B
    1
    Product Number
    Part Number
    2
    Product 1
    Part 10
    3
    Product 1
    Part 11
    4
    Product 1
    Part 12
    5
    Product 2
    Part 13
    6
    Product 2
    Part 14

  96. #96
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.

    I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"

    I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?
    To set Font = Calibri 9 maybe this (not exactly what you want because i'm using size = 1, but quite similar)

    HTML Code: 
    Test

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Name
    Scores
    Rank
    V/F
    Date
    Errors
    2
    John
    30
    4
    VERDADEIRO
    01/01/2013
    #DIV/0!
    3
    Mary
    33
    3
    FALSO
    02/01/2013
    #N/D
    4
    Mike
    34
    2
    FALSO
    03/01/2013
    #NÚM!
    5
    William
    36
    1
    VERDADEIRO
    04/01/2013
    #VALOR!

  97. #97
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Curious. In my registry, I see {3D62E9A1-D243-11D2-B561-00A0C92E6848}

  98. #98
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to post a range - headers and data?

    that is the igfxcfg.DataObject class rather than the Forms one
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  99. #99
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    One last test...


    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U

  100. #100
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to post a range - headers and data?

    What's the finalized version of the code?

  101. #101
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by protonLeah View Post
    What's the finalized version of the code?
    This is the most recent and updated version

    HTML Code: 
    Tony Valko added red color to column letters and row numbers what is not contemplated in the code above.

    M.

  102. #102
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Complementing the previous post

    Installation
    Create a module in Personal Workbook and paste the code above
    Create a new command (macro) in QAT pointing to Sub CopyRngToHTML

    Usage
    Select the desired range
    Click in the new link of QAT
    Paste in the forum reply page

    Remarks
    1. If wished, is possible to set the font size after the code is executed to, for example, size = 1 inserting manually the following BB codes in the forum reply page:

    ['size=1] without the ' , before the tag ['table] without the '

    and

    [/size] after the tag [/table]


    2. Is possible to set a default font size, for example size =2, with a slight modification in Sub RngToHTML in the code line strTable = ....


    HTML Code: 

    3. The same idea is applicable to font type, for example,
    [font=Arial Black}
    ......
    ......
    ....
    [/font].


    Example with Font=Arial Black and size=2

    Data Range
    A
    B
    C
    D
    1
    Name
    Scores
    Rank
    Date
    2
    John
    30
    4
    01/01/2013
    3
    Mary
    33
    3
    02/01/2013
    4
    Mike
    34
    2
    03/01/2013
    5
    William
    36
    1
    04/01/2013
    Last edited by mlcb; 08-30-2013 at 02:36 AM.

  103. #103
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    I'm adding a link to post #102 in my signature.

    Great job, Marcelo!

  104. #104
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Nice work, Marcelo.

  105. #105
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    I'm adding a link to post #102 in my signature.

    Great job, Marcelo!
    Tony,

    Thank you very much for your kind words, stimulus and help.

    Marcelo

  106. #106
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    Nice work, Marcelo.
    shg,

    The same for you. Thank you very much.

    Your interest and support suggesting and providing an easy solution (rcell.Value2) to align the cells according with its values, were fundamental.

    Marcelo

  107. #107
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Besides Tony and shg, I have to mention:

    Article from where I took the basic code:
    http://dailydoseofexcel.com/archives...-from-a-range/

    Contributors (Mr. Excel Forum)
    Jon Von der Heyden - ideas and stimulus
    Rory - important improvements
    Peter (Vog) - function to convert column numbers for letters

    Thank you all!

    Marcelo

  108. #108
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    There's a first cut at an add-in posted at https://app.box.com/s/soezox25h3w0q5s4rcyl

    See the Add-Ins tab for the menu item.

  109. #109
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    testing (with Biffs really great help and instructions)...

    Data Range
    A
    1
    1
    2
    aa
    3
    2
    4
    bb
    5
    3
    6
    cc

    awesome, thanks Biff!!!!

  110. #110
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    There's an update posted at the same link with a 'Forum tools' tab on the ribbon, compliments of romperstomper/Rory
    Last edited by shg; 09-03-2013 at 11:48 PM.

  111. #111
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    For who are not using the add-in and still have an interest.

    Follows a new version based in the code provided by shg in the add-in.

    This new version keeps the original horizontal alignment if any was chosen. Otherwise follows Excel's default alignment (Text left, Numbers right, Errors and Booleans centered).

    Example with column headings and first column (Name) centralized


    A
    B
    C
    D
    E
    1
    Name
    Scores
    Rank
    Date
    Errors
    2
    John
    30
    4
    01/01/2013
    #DIV/0!
    3
    Mary
    33
    3
    02/01/2013
    #N/D
    4
    Mike
    34
    2
    03/01/2013
    #NÚM!
    5
    William
    36
    1
    04/01/2013
    #VALOR!



    New code

    HTML Code: 

  112. #112
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Maybe i dont know what Im talking about (probably, considering my low VBA knowledge), but it looks like you left off the 1st part...
    Please Login or Register  to view this content.

    When I try to run the latest code, I get an error message saying "sub or function not defiled"
    This part gets hitghlighted in yellow...
    Please Login or Register  to view this content.
    and this part seems to be what it doesnt like?
    Please Login or Register  to view this content.

  113. #113
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to post a range - headers and data?

    Hi FDibbins,

    I did not test the latest posted, but looking at what you posted, you might want to fix this...

    Please Login or Register  to view this content.
    Not sure how much "P"s cost these days but they can't be all that expensive

    And for the other part in your post, look in post #101, you need...

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 09-06-2013 at 06:55 PM.
    HTH
    Regards, Jeff

  114. #114
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    All of the code is available for inspection/modification in the add-in.

  115. #115
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    "P"'s are really expensive here so I didnt add it in case it got damaged (actually, it just got left off on the copy/paste)

    As far as the rest of the cose is concerned, I just copy/pasted from here

  116. #116
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to post a range - headers and data?

    shg, the add-in is great, thanks for all those who have helped make this work...

  117. #117
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Quote Originally Posted by shg View Post
    All of the code is available for inspection/modification in the add-in.
    OK stupid question...where is the add-in?

  118. #118
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to post a range - headers and data?

    Look at post #108

  119. #119
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by jeffreybrown View Post
    shg, the add-in is great, thanks for all those who have helped make this work...
    +1

    Now all we need to do is to get folks to actually use it!

  120. #120
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Thanks Jeff, I cant access sites like that from work though

  121. #121
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Nice work and one request from my end. if possible please keep the addin in a separate thread instead of having it in a long going discussion area.. If possible stick that thread on top because it's going to help the users of this forum.

    Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  122. #122
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by :) Sixthsense :) View Post
    Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?
    Hi Sixthsense,

    I tried many, many..., ways to set the bgcolor and, as far as i know, it's not possible with the current version of BB Code.

  123. #123
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    No issues... just wanted to convey it. Your current version is more than enough for us....

    Thanks a lot

  124. #124
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by :) Sixthsense :) View Post
    No issues... just wanted to convey it. Your current version is more than enough for us....

    Thanks a lot
    Thank you very much for your kind words.

  125. #125
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    You're Welcome

    One more suggestion it is possible to ignore the hidden columns?

    Because in the below table the L:Q is the hidden columns which is coming in visible while converstion...


    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    3
    y y y y y
    3
    4
    y y y y y
    2
    5
    y y y y
    1

  126. #126
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    An update...

    When I use Alt+; or Ctrl+G>>Special>>Visible Cells Only then using the addin is ignoring the hidden columns but unfortunately it is moving the data after the hidden columns to a new table like the below...

    E
    F
    G
    H
    I
    J
    K
    3
    y y y y y
    4
    y y y y y
    5
    y y y y
    3
    3
    4
    2
    5
    1

  127. #127
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Testing


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    R
    1
    21/ago
    28/ago
    04/set
    11/set
    18/set
    25/set
    2
    Adams
    Shawn
    New
    y
    y
    y
    2
    3
    Return
    y
    y
    y
    1
    4
    Adams
    Maribeth
    New
    y
    y
    y
    y
    1
    5
    Return
    y
    y
    y
    3

  128. #128
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Testing with Row 3 = hidden and columns L:Q hidden


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    R
    1
    21/ago
    28/ago
    04/set
    11/set
    18/set
    25/set
    2
    Adams
    Shawn
    New
    y
    y
    y
    2
    4
    Adams
    Maribeth
    New
    y
    y
    y
    y
    1
    5
    Return
    y
    y
    y
    3

  129. #129
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    BTW nice formula in
    http://www.excelforum.com/excel-form...her-blank.html

    This is my version (but I was late...)

    R2
    =IFERROR(COUNTIF(INDEX($F2:$Q2,LOOKUP(2,1/(($F2:$Q2="")*(COLUMN($A2:$L2)<MAX(($F2:$Q2="Y")*COLUMN($A2:$L2)))=1),COLUMN($A2:$L2))):$Q2,"y"),COUNTIF($F2:$Q2,"y"))

  130. #130
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Sixthsense,

    Follows a test version to deal with rows/columns hidden

    HTML Code: 

  131. #131
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Thanks for it...

    You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.

    I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?

    If so then request the same to implement in the Addin....

    Edit: You posted the revised code already and thanks for it...

  132. #132
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by :) Sixthsense :) View Post
    Thanks for it...

    You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.

    I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?

    If so then request the same to implement in the Addin....

    Edit: You posted the revised code already and thanks for it...
    This is NOT the code of the add-in. I was just testing a new code.

  133. #133
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    This is NOT the code of the add-in. I was just testing a new code.
    Oh! Ok.. since I have not gone through the code of that addin and assumed that this is the fine tuned code!

  134. #134
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to post a range - headers and data?

    In order to show the code correctly in this thread I had to replace a [ 5 times by a {
    Please replace the indicated { by [ in your VBA code.

    You might use:

    HTML Code: 
    HTML Code: 
    Last edited by snb; 09-08-2013 at 07:13 AM.

  135. #135
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Test code from post #130...

    Columns D and E are supposed to be hidden.


    A
    B
    C
    F
    G
    H
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    9/5/2007
    -0.00025
    Some Here
    TRUE
    #VALUE!
    3
    5/14/2004
    1.00E+100
    More
    4
    3/4/2000
    1
    FALSE
    #REF!
    5
    4/6/2007
    0
    Ice Cream
    TRUE

  136. #136
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Made the row/column headers red...


    A
    B
    C
    F
    G
    H
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    9/5/2007
    -0.00025
    Some Here
    TRUE
    #VALUE!
    3
    5/14/2004
    1.00E+100
    More
    4
    3/4/2000
    1
    FALSE
    #REF!
    5
    4/6/2007
    0
    Ice Cream
    TRUE

  137. #137
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    I think the default font size =2.

    In this line of code:

    HTML Code: 
    Is it necessary to set the size to 2?

    Here's the result after removing the size tags:


    A
    B
    C
    F
    G
    H
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    9/5/2007
    -0.00025
    Some Here
    TRUE
    #VALUE!
    3
    5/14/2004
    1.00E+100
    More
    4
    3/4/2000
    1
    FALSE
    #REF!
    5
    4/6/2007
    0
    Ice Cream
    TRUE

  138. #138
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by snb View Post
    In order to show the code correctly in this thread I had to replace a [ 5 times by a {
    Please replace the indicated { by [ in your VBA code.
    Try HTML tags instead of CODE tags as below

    HTML Code: 

  139. #139
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    I think the default font size =2.

    In this line of code:

    HTML Code: 
    Is it necessary to set the size to 2?
    Tony,

    Not sure, but I think in other forums, or using different browsers, the default is not 2.

    I remember many times, especially in Mr Excel Forum, the size being set to 3.

    So, just in case, I prefer to put in the code.

    M.

  140. #140
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to post a range - headers and data?

    Revised edition:
    HTML Code: 
    HTML Code: 

  141. #141
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    OK, that sounds like a good idea!

  142. #142
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    OK strange.

    At work, I have win 7 and 2007, and the add-in works fine there
    At home, I have win 8 and 2007, and it worked fine here for a while.

    Just installed 2010 (still have 2007) and now when I try to run the add-ing, on excel side, it all still seems fine, but when I copy here, all I get is 2 blank lil squares, like this (IF it will show)...
    ￿￿

    ANyone got any suggestions/ideas?

  143. #143
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Whether you installed the addin in both 2007 & 2010 separately?

  144. #144
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    OK this is from 2010

    A
    1
    aa
    2
    bb
    3
    cc


    and this is from 2007...
    M
    5
    95
    6
    53
    7
    37
    8
    20
    9
    25
    10
    67

  145. #145
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    OK very strange, it was not doing that before, and I had closed 2007 completely, tried again - nothing. then removed the add-in (even from the personal folder), put it back, and it still wouldnt work

  146. #146
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    What I suggest is Keep Two Copies of that Addin.

    Name 1 copy like For2007 and another copy like For2010.

    Seems to be senseless approach but this is what my sense asking me to suggest.

    Open your 2007 Excel and Press Alt+T+I (Not in VBA mode) and see whether previously installed addin resides there. If not then Click Browse button and locate the For2007 addin and give ok…

    Please repeat the above method for 2010 and install the For2010 Addin.

    Now check whether this problem still continues…

  147. #147
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    I've seen reports of sporadic issues when using the MSForms.DataObject in Win8. I think that's what you're running into.
    Remember what the dormouse said
    Feed your head

  148. #148
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    @ Six, thanks for the input, I have done that (cant rename the actual macro that appears when you add the addin to the ribbon though)
    2007 gives this...
    ￿￿
    (again grrr)
    2010 gives me this...
    ￿￿
    sooo back where we started from LOL

    I think Rory may be right, I dont have this problem on Win 7 with 2007, just at home with Win 8 and 2007/2010

  149. #149
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    @ FDibbins,

    Thanks for testing even though the suggestion is not valid and giving the feedback

    I am running with XP and not encountered this problem

  150. #150
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to post a range - headers and data?

    does seem like an acknowledged bug: http://social.msdn.microsoft.com/For...d?forum=isvvba
    there are a couple links to api code fixes in one of the later posts

  151. #151
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Thanks for the link Joseph, although I would have nooo idea what to do with that. (1 of the last posts said that the fix didnt work for them, but it's still wayyy above my pay-grade lol)

  152. #152
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to post a range - headers and data?

    the only issue there was that the poster didn't know how to alter the code for 64bit Office-it's not that hard ;-)

  153. #153
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    flying a plane isnt hard either - if you know how lol

  154. #154
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: How to post a range - headers and data?

    Been trying to figure this out for AGES...thanks shg...Post #108

    A
    B
    C
    D
    1
    name prices prices
    2
    A0059
    41.5
    41.5
    3
    C0103
    14.7
    14.7
    4
    E0530
    11.9
    11.9
    5
    E0530
    23.3
    23.3
    6
    G7004
    14.9
    14.9
    7
    C0103
    19.6
    19.6
    8
    C0103
    38.7
    38.7
    9
    A0059
    30.3
    30.3
    10
    E0530
    33.1
    33.1
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  155. #155
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Align everything to the right...

    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U

  156. #156
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now

    Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?

    Also, I mentioned in an earlier post on this thread (no idea where, this already has 155+ posts and 11 pages - hence my request) that with Win8, the code does not always work, sometimes just get 2 small squares instead of what I copied. I was told it was a W8 problem, wondering if anyone else had the same issue, and if anyone could fix it?

  157. #157
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Quote Originally Posted by FDibbins View Post
    This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now

    Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?
    I agree.

    However, which "latest" code should be the code?

    Each "latest" code did something a bit different from the previous "latest" code and not everyone would want to use the last "latest" code. For example, the code I use is slightly different from any of the versions in the thread.

    Haven't seen Marcelo on the site in a while.

  158. #158
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Hello Friends

    Lately I've been here a few times.

    This is the code (Personal Workbook) I'm currently using

    Please Login or Register  to view this content.

    There is also the Add-In provided ​​by shg

    Link
    https://app.box.com/s/soezox25h3w0q5s4rcyl

    Usage
    See the Add-Ins tab for the menu item.


    All the best

    Marcelo
    Last edited by arlu1201; 04-25-2014 at 08:56 AM.

  159. #159
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    Perhaps it just needs a settings form with the most used options in it, so the same add-in will work for most anyone. The code is unlocked anyway I think, so it can always be tweaked if someone has a particular need.

  160. #160
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    My code in #158 is truncated

    New attempt

    HTML Code: 
    M.

  161. #161
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Posting fake email addresses and having the forum NOT create links:



    Data Range

  162. #162
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Success!

  163. #163
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    I realize this is an ancient thread tht probably should not be revived. If so, and I should start a new thread, that will be fine. I noticed a link in Biff's signature re how to post dta (something to that effect.)

    Because I blindly assume Headers argument would be a range, I had error when using this formula. Turned out, function was not running at all.
    =RngToBBCode(D2:E7,D1:E1)

    Note, if this needs to be deleted, please either explain to me steps necessary, or feel free to delete it (assuming you have the power to do so, like I would assume a Forum Moderator would be able to.)

    Okay, I see what I did wrong. I finally figured out that 2nd argument was a boolean while I was trying to use it as a range. Apparently, if you want headers, you include the line that would be your header in the range you select. Duh!

    Revised formula should be =RngToBBCode(D2:E7,1)
    Or true instead of 1. I seem to still be having problems with a Beginning and Ending " but that would be easy enough to ignore or manually delete.
    "
    D
    E
    1
    Before
    After
    2
    2.5
    2.50-
    3
    2.5
    2.50-
    4
    5
    5.00-
    5
    10
    10.00-
    6
    2.5
    2.50-
    7
    10
    10.00-
    "

    Note: when I used the code from about 2 posts ago, was having problems with table displaying the words [COLOR} with formatting and [/COLOR] displaying in the table rather than actually displaying the text in color.

    I attempted to remove references to Color in the code and was apparently mostly successful based on my tests.
    Last edited by klvaughnsd; 05-09-2014 at 01:48 PM. Reason: Deleting previoous attempts of tables

  164. #164
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    If you're using Excel 2002 as your profile indicates...

    Copy the code in post #160.

    Paste it into a general module.

    Then, to use it...

    Let's assume you want to post the range A1:C10 as a table into a forum reply.

    Select the range A1:C10
    Goto the menu Tools>Macro>Macros
    Select the macro name: CopyRngToBBCode
    Click the Run button

    The macro will generate the bb code and it will be copied to your clipboard.

    In Excel Forum, just paste the code into your reply. It might look something like this**:

    Data Range
    A
    B
    C
    1
    Header1
    Header2
    Header3
    2
    Data
    Data
    Data
    3
    Data
    Data
    Data
    4
    Data
    Data
    Data
    5
    Data
    Data
    Data
    6
    Data
    Data
    Data
    7
    Data
    Data
    Data
    8
    Data
    Data
    Data
    9
    Data
    Data
    Data
    10
    Data
    Data
    Data


    ** the code I use is slightly different as I have modified it to suit my own preferences.

  165. #165
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    I did use code from post 160, but was using the UDF. Let's see what happens with macro:


    D
    E
    1
    Before
    After
    2
    2.5
    2.50-
    3
    2.5
    2.50-
    4
    5
    5.00-
    5
    10
    10.00-
    6
    2.5
    2.50-
    7
    10
    10.00-


    Cool. That looks nice. Thanks very much!

  166. #166
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Looks good!

    Thanks for the feedback!

  167. #167
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to post a range - headers and data?

    Made my own variant:
    If the selection is 1 cell it will be expanded to the currentregion, otherwise the selection will be converted into a HTML table.

    Please Login or Register  to view this content.
    The function can't be posted here because it's been evaluated as html.
    Attached Files Attached Files
    Last edited by snb; 05-10-2014 at 08:41 AM.

  168. #168
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    Quote Originally Posted by snb View Post
    Made my own variant:
    If the selection is 1 cell it will be expanded to the currentregion, otherwise the selection will be converted into a HTML table.

    Please Login or Register  to view this content.
    The function can't be posted here because it's been evaluated as html.

    This gives me a small excuse to ask about this line:
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    I don't need to fully understand it, but would appreciate at least a short explanation of what's going on with this line of code.

    I apologize if it's already been explained in this thread. I read the first couple pages of this thread, then skipped to the (previously) last page of the thread

  169. #169
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to post a range - headers and data?

    see also #160

    it's creating a new instance of the dataobject, based on the reference Msforms 2.0

  170. #170
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    Quote Originally Posted by snb View Post
    see also #160

    it's creating a new instance of the dataobject, based on the reference Msforms 2.0
    Yes, I got it from post #160 which is where I first became curious as to what it meant. Cool. Thanks for the info. The macro containing it works nice.

  171. #171
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Testing font color...

    Data Range
    A
    B
    1
    1
    8
    2
    2
    2
    3
    8
    7
    4
    3
    3
    5
    8
    5
    6
    8
    4
    7
    4
    4
    8
    5
    6
    9
    8
    9
    10
    6
    5

  172. #172
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Testing font color...

    Data Range
    A
    B
    1
    1
    8
    2
    2
    2
    3
    8
    7
    4
    3
    3
    5
    8
    5
    6
    8
    4
    7
    4
    4
    8
    5
    6
    9
    8
    9
    10
    6
    5

  173. #173
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    One last test of font color...

    Data Range
    A
    B
    1
    1
    8
    2
    2
    2
    3
    8
    7
    4
    3
    3
    5
    8
    5
    6
    8
    4
    7
    4
    4
    8
    5
    6
    9
    8
    9
    10
    6
    5

  174. #174
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Testing font color...

    Data Range
    A
    B
    1
    61
    15
    2
    78
    67
    3
    1
    36
    4
    94
    72
    5
    22
    49
    6
    91
    63
    7
    69
    35
    8
    27
    71
    9
    46
    63
    10
    60
    30

  175. #175
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Hay Tony, was that the last after-last last test?

  176. #176
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    There might be more!

    I'm trying to come up with the best color combinations where the row/column headers standout and I can highlight specific pieces of the data so they also standout.

  177. #177
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    I know I have said this before in this thread (somwhere in the 12 pages???), but what is the latest itteration of this now, and where/how can it be found?

    This really really helps a LOT when posting answers, and it would be great to keep it updated with the latest version

    Also, when running this (the version I have) on W8 and 2007 or 2010, sometimes it just pastes 2 small squares instead of the table - any fix for that?
    Last edited by FDibbins; 09-22-2014 at 07:25 PM. Reason: fixed reaqlly bad spelling

  178. #178
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to post a range - headers and data?

    Without going through the entire thread (again) I can't tell you which is the latest update.

    I can post the code I use if you want me to.

    As for the problem you mentioned...I've not experienced anything like that but I use Win7.

  179. #179
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?


  180. #180
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Yup, I only have that on W8, not W7 (both running 2007)

    I started at post # 142 on page 10

  181. #181
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    @Ford,

    I have a version here which doesn't use the DataObject (it uses API calls) and so shouldn't suffer from the 'two squares' issue on Win 8. It also has tools for posting using HTML which aren't much use here, but you can always simply take the clipboard code and use it in Marcelo's add-in instead of the DataObject code.

  182. #182
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to post a range - headers and data?

    A test. Pray for me.

    Please Login or Register  to view this content.
    OK; that works...sort of. Cannot find those BB code tags.
    Help?
    Last edited by FlameRetired; 09-02-2014 at 10:20 PM.

  183. #183
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to post a range - headers and data?

    New test:

    A
    B
    C
    D
    1
    Jupiter Wyoming
    -16
    2
    Saturn Alabama
    20
    3
    Uranus Alaska
    -10
    4
    Neptune Arizona
    -8
    5
    Pluto Arkansas
    15
    6
    Mercury California
    15
    7
    Venus Colorado
    5
    8
    Earth Connecticut
    -14
    9
    Mars Delaware
    4
    10
    Jupiter Florida
    6
    11
    Saturn Georgia
    -5
    12
    Uranus Hawaii
    -7


    That worked except for the colors. I keep trying.

  184. #184
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to post a range - headers and data?

    One more time:

    A
    B
    C
    D
    1
    Jupiter Wyoming
    -16
    2
    Saturn Alabama
    20
    3
    Uranus Alaska
    -10
    4
    Neptune Arizona
    -8
    5
    Pluto Arkansas
    15
    6
    Mercury California
    15
    7
    Venus Colorado
    5
    8
    Earth Connecticut
    -14
    9
    Mars Delaware
    4
    10
    Jupiter Florida
    6
    11
    Saturn Georgia
    -5
    12
    Uranus Hawaii
    -7


    That's great!! Thank you Marcelo and all. I will use this.
    FR
    Last edited by FlameRetired; 09-03-2014 at 09:39 AM.

  185. #185
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to post a range - headers and data?

    'Nother test......
    E
    F
    G
    H
    I
    J
    9
    10
    Customer
    Product Qty
    11
    Corks
    Bottles
    Barrels
    12
    Contoso, Ltd.
    14
    9
    3
    13
    Coho Winery
    2
    11
    15
    14
    15
    16
    Price
    Weight (lbs)
    Product
    17
    $200
    4
    Corks (500/box)
    18
    $250
    42
    Bottles (case)
    19
    $425
    115
    Barrel
    20
    MMULT
    21
    Customer
    Sales
    Total weight
    22
    Contoso, Ltd.
    $6,325
    779
    #N/A
    23
    Coho Winery
    $9,525
    2195
    #N/A
    24
    #N/A
    #N/A
    #N/A
    25

  186. #186
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    You testing the tests? lol

    @ Rory, thanks for that, I had not noticed your post before. I an on W7 right now, will take a look when I get home (W8)

  187. #187
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    deleted duplicate post
    Last edited by FDibbins; 09-22-2014 at 07:49 PM.

  188. #188
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Version 0.5 adds options to suppress the header and grid:

    Statement
    Statement
    Statement
    Statement
    Remark
    Marker
    Operator
    Call Else Loop Sub Rem Any AddressOf
    Case ElseIf LSet Type As And
    Close End Next Unlock ByRef Eqv
    Const EndIf On Wend ByVal Imp


    The add-in is at https://app.box.com/s/soezox25h3w0q5s4rcyl

  189. #189
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    Is anyone having problems with the add-in in Excel 2013?

  190. #190
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to post a range - headers and data?

    I think the issue is Windows 8, not XL 2013, as cropped up in posts 142, 147 and with the version posted by romperstomper in post 181. That one works fine in Win 8.

  191. #191
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    to confirm, I have used Rompers suggestion from post 181 in Win8 and (now) win 8.1 at home, and it does work great, still using win 7 at work here though

    My apologies Rory, I thought I had thanked you for this before, but looking back, apparently I did not - so thank you bunches for that

  192. #192
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    You're welcome, Ford.

  193. #193
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to post a range - headers and data?

    I think that is an apalling bug.

  194. #194
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    What would you expect from an appalling OS?

  195. #195
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Quote Originally Posted by romperstomper View Post
    I have a version here
    Hi Rory,

    I am using your addin for posting the table for OP's and it's working fine and thanks for it

    One small missing piece is that it's showing the Normal formula and Array Formula in the same way... without covering the formula with curly braces {} for array formulas...

    Here is the thread as an example

    http://www.excelforum.com/excel-form...-of-pairs.html

    If you implement it then it will be very much useful to me and OP as well

  196. #196
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    I'll see what I can do when I have a moment, Sixth.

  197. #197
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    OK, should be done now and I have updated the version on my site.

  198. #198
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to post a range - headers and data?

    Quote Originally Posted by romperstomper View Post
    OK, should be done now and I have updated the version on my site.
    Thanks for implementing the requested feature

    I will give a try and let you know

  199. #199
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to post a range - headers and data?

    Just as an FYI, I have created a Mac-friendly version of the BB Code add-in. You can get it at the bottom of the page here: http://excelmatters.com/excel-forums/

  200. #200
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to post a range - headers and data?

    Quote Originally Posted by romperstomper View Post
    You're welcome, Ford.
    I have noticed with what I got from your post 181, that sometimes, it seems to remember the file it was last used in (if I have a few different files open), then when I try and use it again, it jumps back to that file, and wont accept the range from the current file. I end up having to close the file I used it previously in, and then it works fine again.

    Also, instead of actually showing colors, it shows their names instead (I think its only background colors, not font colors...
    B
    C
    D
    E
    F
    6
    [BGCOLOR=#FFFF00]1[/BGCOLOR]
    [BGCOLOR=#FFFF00]2[/BGCOLOR]
    [BGCOLOR=#FFFF00]3[/BGCOLOR]
    [BGCOLOR=#FFFF00]4[/BGCOLOR]
    [BGCOLOR=#FFFF00]5[/BGCOLOR]
    7
    [BGCOLOR=#FFFF00]102[/BGCOLOR]
    [BGCOLOR=#FFFF00]202[/BGCOLOR]
    [BGCOLOR=#FFFF00]302[/BGCOLOR]
    [BGCOLOR=#FFFF00]402[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    8
    [BGCOLOR=#FFFF00]104[/BGCOLOR]
    [BGCOLOR=#FFFF00]204[/BGCOLOR]
    [BGCOLOR=#FFFF00]304[/BGCOLOR]
    [BGCOLOR=#FFFF00]404[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    9
    [BGCOLOR=#FFFF00]106[/BGCOLOR]
    [BGCOLOR=#FFFF00]206[/BGCOLOR]
    [BGCOLOR=#FFFF00]306[/BGCOLOR]
    [BGCOLOR=#FFFF00]406[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    10
    [BGCOLOR=#FFFF00]108[/BGCOLOR]
    [BGCOLOR=#FFFF00]208[/BGCOLOR]
    [BGCOLOR=#FFFF00]308[/BGCOLOR]
    [BGCOLOR=#FFFF00]408[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    11
    [BGCOLOR=#FFFF00]114[/BGCOLOR]
    [BGCOLOR=#FFFF00]214[/BGCOLOR]
    [BGCOLOR=#FFFF00]314[/BGCOLOR]
    [BGCOLOR=#FFFF00]414[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    12
    [BGCOLOR=#FFFF00]116[/BGCOLOR]
    [BGCOLOR=#FFFF00]216[/BGCOLOR]
    [BGCOLOR=#FFFF00]316[/BGCOLOR]
    [BGCOLOR=#FFFF00]416[/BGCOLOR]
    [BGCOLOR=#FFFF00]#N/A[/BGCOLOR]
    13
    [BGCOLOR=#FFFF00][/BGCOLOR] [BGCOLOR=#FFFF00][/BGCOLOR] [BGCOLOR=#FFFF00][/BGCOLOR] [BGCOLOR=#FFFF00][/BGCOLOR] [BGCOLOR=#FFFF00][/BGCOLOR]


    Otherwise, this thing works great!!

+ Reply to Thread
Page 1 of 2 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dependent drop down lists- dynamic data range- excluding Headers
    By strud in forum Excel General
    Replies: 3
    Last Post: 05-28-2013, 04:10 AM
  2. [SOLVED] INDIRECT will not post Text string to Data Validation for Named Range on another Workbook
    By carleaston in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2012, 05:08 PM
  3. ClearContents all rows except headers in a dynamic range
    By jdfjab in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2012, 02:24 PM
  4. Replies: 3
    Last Post: 09-30-2010, 12:30 PM
  5. Creating Headers within a date range
    By orivera in forum Excel General
    Replies: 1
    Last Post: 07-25-2008, 07:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1