+ Reply to Thread
Results 1 to 19 of 19

Preserving a cell format while making One Column

  1. #1
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Preserving a cell format while making One Column

    Hi

    I found a code on vast fields of internet to make my date into one column.

    How can I tweak it to preserve text formating in cells (Currently only formating of A1 cell is preserved)

    Many thanks.


    P.S. you can find the code below.
    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    I am very sorry for bringing the post up, but probably someone who could help have missed it.

    Thanks

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Preserving a cell format while making One Column

    You could record the desired format of your column with the macro recorder to get your code.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    It is a formatting issue.
    You can try this one, but it may not work
    Please Login or Register  to view this content.
    The other option would to change the format of the source(Raw data) before you run the code, so the code will take the formatting of the source sheet.

  5. #5
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Oeldere and AB33, thanks for the responses.

    Quote Originally Posted by oeldere View Post
    You could record the desired format of your column with the macro recorder to get your code.
    I need only specific rows (ex. column - name) to be formatted.
    The other option would to change the format of the source(Raw data) before you run the code, so the code will take the formatting of the source sheet.
    I tried, but it only takes the formating of the first A1 cell, after that everything else is plain. Selection.NumberFormat = "@" did not help as well.

    Just in case I've attached the pictures of how I want to them to look like.
    before.png
    after.png

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    You may need to trim your data as there appears be spaces.

  7. #7
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    You may need to trim your data as there appears be spaces.
    These spaces are there intentionally, as a way to format the report with only text at hand.

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    It is difficult to see what is going in Picture. Please attach your excel book sample?

  9. #9
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    It is difficult to see what is going in Picture. Please attach your excel book sample?
    Here is the file
    please help.xlsm

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    Okay!
    First question is what is the purpose of the code? You have 44 rows and a single column in sheet Known Companies Report and the desired result is the same, except for few formatting of bold.
    Why do you need to loop the columns as you seem to have a single column?

  11. #11
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    Okay!
    First question is what is the purpose of the code? You have 44 rows and a single column in sheet Known Companies Report and the desired result is the same, except for few formatting of bold.
    Why do you need to loop the columns as you seem to have a single column?
    Actual data is in Know Companies sheet. There will be more columns => it will be very inconvenient to read. My boss desires a nice printable and easy readable report. That is why I want to keep source formatting

  12. #12
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    Please do not reply with quote, just a reply will do.
    The code loops though the rows for every column and copies if the row is not empty, but as you have selection, I do not know what columns are you selecting. Could you tell what columns in sheet Known Companies, columns A-H do you want to select. Is it all columns?

  13. #13
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Columns B to G

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    In sheet Known Companies, you have 11 rows and 8 columns. How did you get the desired result? The desired result output seems to be from sheet Known Companies Report.

  15. #15
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    In sheet Known Companies, you have 11 rows and 8 columns. How did you get the desired result? The desired result output seems to be from sheet Known Companies Report.
    Known Companies = Data
    Known Companies Report = Data from above but in one column
    Desired = How I want Known Companies Report to be looking like

  16. #16
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    Your code does not produce your desired result. It loops down instead of across. I have amended it. Since you want to bring over the formatting of each row, I found it easy to use a similar but different code. The array code you have does not copy the formatting of the source sheet. I could have include a line to copy the formatting but every single row has a different formatting.
    Try the attached.
    Attached Files Attached Files
    Last edited by AB33; 08-18-2013 at 12:04 PM.

  17. #17
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    Your code does not produce your desired result. It loops down instead of across. I have amended it. Since you want to bring over the formatting of each row, I found it easy to use a similar but different code. The array code you have does not copy the formatting of the source sheet. I could have include a line to copy the formatting but every single row has a different formatting.
    Try the attached.
    Thanks!

    It seems to work, but takes quite a long time to do so. Since they are part of a longer algorithm it is important, how can I improve it? Am I calling tem in right order (First columnstocol then MakeOneColumn11)

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Preserving a cell format while making One Column

    If your intention is to get the desired result as shown on the sample, the code you have works the opposite direction, though, you get the same number of rows. You need to loop through rows for each column.
    I know there is a speed issue with the code I have provided you, but I really do not know how to get the formatting from the source sheet with an array loop. I have tried to copy the formatting of the source sheet but does not seem to work.
    You can write another code for just copying the formatting, but there may be an issue with the speed of the formatting code and this defeats the speed you are trying to achieve.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-18-2013
    Location
    Oxford
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Preserving a cell format while making One Column

    Quote Originally Posted by AB33 View Post
    If your intention is to get the desired result as shown on the sample, the code you have works the opposite direction, though, you get the same number of rows. You need to loop through rows for each column.
    I know there is a speed issue with the code I have provided you, but I really do not know how to get the formatting from the source sheet with an array loop. I have tried to copy the formatting of the source sheet but does not seem to work.
    You can write another code for just copying the formatting, but there may be an issue with the speed of the formatting code and this defeats the speed you are trying to achieve.
    Wow, that works great!

    Thank you, mate!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 01-28-2013, 04:35 PM
  2. Preserving Cell Height and column width
    By shirl in forum Excel General
    Replies: 0
    Last Post: 03-29-2006, 12:00 PM
  3. preserving column width format in excel pivot tables
    By Colin Foster in forum Excel General
    Replies: 0
    Last Post: 02-09-2006, 10:25 AM
  4. preserving cell format
    By bob777 in forum Excel General
    Replies: 2
    Last Post: 11-04-2005, 08:53 AM
  5. [SOLVED] preserving cell format between linked documents
    By lyriamoonriver in forum Excel General
    Replies: 1
    Last Post: 05-17-2005, 04:06 PM

Tags for this Thread

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