Text alignment in a range of cells using macro .

[amlan009]

Dear All ,


Thread Edited -I have also posted this query at http://www.ozgrid.com/forum/editpost...tid=593288...i have done so to increase the chances of getting answered



Also - I note that my earlier thread for this post got deleted due to the server upgradation issue ,but i am really in need of a solution to this so here's posting it again ,


Please see the attached file .....note the column D of input file and Column D of embed output file .

You will note that column D in input file has text aligned in a certain position .I want to do it like in output sheet ....basically in input sheet in each cell of column D ,unnecesary spaces after text makes it look ugly and this happened because the input file is product of pdf to excel conversion .....
In output sheet ,i try and do the first 4 entries correctly with perfect alignment and remove the odd spaces .....i mark it in green color and for the rest which still needs alignment ,i mark it red color .......

Can someone advise on how to do this ....will appreciate a macro but even if ready made excel function can be used ,wont mind...i tried wrap,left alignment ,justify etc ....nothing worked .....

Best would be a macro which can read any cell which contains data like this in column D and aligns it like in output sheet for each entry ,


Thanks in advance ,


Humble Regards ,


Amlan Dutta

[Palmetto]

Please read the Forum Rules.

Cross-posting without a link is a violation of the rule.
Cross posted at Ozgrid

[amlan009]

i was unaware of this rule and in future will provide a link to posts where i raise my query in more than one forums......

I will edit my post and include the link and take note of this point in future ,


My apologies ,

Humble Regard's,


Amlan Dutta

[jaslake]

Hi Amlan

See if this code approaches what you're trying to accomplish. Let me know of issues.

Please Login or Register  to view this content.

[amlan009]

Dear Jaslake ,


Thanks for the help .....i will check and revrt on your code shortly but like i informed in the other forum ,late last nite around 12.00m ,

In the meantime for everyone who has been following this thread ,it was solved by at Ozgrid by converted ,the code for everyone's benefit goes like this ,

Please Login or Register  to view this content.

I attach the input file and output file with macro assigned to button's so lest anyone decides to try out ,he/she can see it work ,

Jaslake ,sorry that i couldn't post this earlier but yesterday i was unable to log into here ,i think that this was cause of server upgradation issues (I tried umpteen times ),


Lest i forget ,thanks a tonnnnnnnnnnnneeeeeeeeeeeeeeeeeeeeee for your valuable advise and help ,it's deeeply deeeply appreciated ,


I will close this thread as solved once i get confirmed that your line of code works as well ,sinxe you have taken effort to solve this ,it means equally much to me ,every solution is a new story and offers so much to learn ,i value your efforts like a sincere student and will get back with output results it gives at the latest ,



The link for the post at ozgrid is http://www.ozgrid.com/forum/showthre...d=1#post593343,

Thanks and Humble Regards ,


Amlan Dutta

[jaslake]

Hi Amlan

The code you referenced WILL remove Chr(10) and replace it with " ".
I believe the code I suggested will remove ALL non-printable characters. Try it, you may like it.

[amlan009]

Dear John ,


Your code works magnificiently (Input and output file attached ),i think ,it takes lesser time but i will install a timer in the code to record exactly that for study purpose ,

Meantime ,here's the input sheet with both your 's and converted (ozgrid solution ) in the attached file ....i have given both of you seperate buttons so that yourl can see how wonderfully both your code performs in different yet effective and perfect manner's,

From the way i look at it ,i will study the 2 approaches to this problem ,but thank you gentlemen so so so much


Thanks a tonne ,


Humble Regard's,


Amlan Dutta

[amlan009]

Dear John ,


John ,what does non printable characters mean .....

Also regards ,char(10) does it indicate space in VB ,when you say that char(10) is replaced by " " (null),how does it help because logically " " is also a space right or am i missing something ?would you mind explaining the logic a little please ?


Thanks in advance

Humble Regards ,


Amlan Dutta

[jaslake]

Hi Amlan

See this link for a discussion of non-printable characters. http://office.microsoft.com/en-ca/ex...005209014.aspx
Basically they're characters you can't see and are typically embedded in cells that are imported from non-excel applications. There's also this link you may wish to pursue http://www.cpearson.com/excel/CellView.aspx I've not personally used it but you may find it useful.