VBA/Macro question

  • Thread starter Thread starter OriginalStealth
  • Start date Start date
O

OriginalStealth

My spreadsheet contains the following data:

Contract Name Distributor Name


How can I loop through the cells and find Contract Name, unmerge the cell
and have it left justify, then move to the next occourance. Contract Name
will always be in cell 1 and be underlined and bolded.
 
Hi,

not enought info for us to help you. What's in the merged cells, where are
they located, are then consecutive. The the merged cells contain the Contact
Name and Distributer Name. For example, you ask to have "it" left justified.
What is it? All text is left justified by default. When you unmerge the
cells manually what is happening?
 
OK. When I open the spreadsheet, Contact Name is A11 but has been merged
with A11,B11 & C11 for no apparant reason and it is centered. This document
is several pages long so this exist throughout the document. I have to
unmerge the cells and left justify Contact Name. When I unmerge manaully I
have to resize the cell to display the whole text. I'm having to do this
about 30 times in this document. Then do the same for Distributor Name.
Only Distributor Name is in I11 and also has been merged with other cells.
Contact Name and Distributor Name will always appear on the same row
together. I was trying to get this in a macro and add it to the excel menu
because I get these documents every week and sometime they vary in page
length.
 
Hi,

I'm at work, so I won't write a macro until I get home. However, depending
on what is happening in the rest of the spreadsheet you could do the
following:
1. Select the entire speadsheet or at least all the columns which include
merged cells. You can select non-contiguous columns by highlighting through
the column letters of the first set, holding down the Ctrl key and
highlighting the second set.
2. Click the Merge and Center button on the Formatting toolbar - all cells
will be unmerged and uncentered.
3. With the sheet or range still selected double click between any of the
column letters that are highlighted, when you see a 2-headed black arrow.
For example on the line between the letters B and C. All selected columns
will be best fit.
 
Shane,

Thanks for your help. I forgot to mention that all this has to start after
row 10. Rows 1 thru 10 will always have logos and report header stuff.
 
Hi,

You can still do the manual method I suggested, Just highlight manually from
row 11 down in the appropriate columns, using the Ctrl key to select
non-touching ranges if necessary. You may still be able to use the best fit
technique I suggested, if not we need to know why because we couldn't code it
if you can't do it manually.
 
Back
Top