+ Reply to Thread
Page 1 of 2 1 2 LastLast
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
    11,840

    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 Guru 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 Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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
    11,840

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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 Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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,647

    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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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 Guru 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
    11,840

    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 Guru 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 Guru 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 Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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 Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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
    Excel 2016
    Posts
    10,178

    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 Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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
    Excel 2016
    Posts
    10,178

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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
    Excel 2016
    Posts
    10,178

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

    Look at post #108

  119. #119
    Forum Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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,766

    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...?

  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,766

    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,766

    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,766

    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,766

    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,766

    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,647

    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 Guru 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 Guru 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 Guru 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,647

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

    Revised edition:
    HTML Code: 
    HTML Code: 

  141. #141
    Forum Guru 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 2000/3/7/10/13/16/365
    Posts
    52,004

    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,766

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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,766

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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,766

    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 2000/3/7/10/13/16/365
    Posts
    52,004

    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 2000/3/7/10/13/16/365
    Posts
    52,004

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

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

  154. #154
    Forum Guru 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