DEAR ALL

I have a sheet full of names, dates and particulars. Over a period of time
some parts of the sheet is copied and dragged down, some cells erased etc
using 'clear contents' in VB .

Invariably some of the cell loses its format and few will be aligned center
and some left and some right. Since the sheet is protected, can anyone
suggest what is the best way to keep the format that was made for each column
regardless of the shifting and deleting data.

VB is not a solution bcos I dont intend to sit and write alignment code for
100 columns and hence pls help - is there a way out?

brgds/captgnvr

Assuming you applied your alignment formatting to the entire column and not
just to a selection of cells, then odds are nothing you have done has
affected the alignment format of the last cell in each column. If that is
the case, then this macro should realign the entries in your cells to
whatever the alignment format for the column it's in is...

Sub RealignColumns()
Dim C As Range
For Each C In Columns
C.HorizontalAlignment = Cells(Rows.Count, C.Column).HorizontalAlignment
Next
End Sub

Rick


"CAPTGNVR" <(E-Mail Removed)> wrote in message
news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
> DEAR ALL
>
> I have a sheet full of names, dates and particulars. Over a period of
> time
> some parts of the sheet is copied and dragged down, some cells erased
> etc
> using 'clear contents' in VB .
>
> Invariably some of the cell loses its format and few will be aligned
> center
> and some left and some right. Since the sheet is protected, can anyone
> suggest what is the best way to keep the format that was made for each
> column
> regardless of the shifting and deleting data.
>
> VB is not a solution bcos I dont intend to sit and write alignment code
> for
> 100 columns and hence pls help - is there a way out?
>
> brgds/captgnvr

Here is one thought...

Manual process...

1. Unprotect your sheet
2. Find one row that meets your requirement for all your formats and
highlight the entire row
3. Right click and select COPY
4. Highlight all the other rows that need fixing
5. Right click and select PASTE SPECIAL, select FORMATS, and then OK

Walaaa...

Let me know if this helps out...

Mark


"CAPTGNVR" <(E-Mail Removed)> wrote in message
news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
> DEAR ALL
>
> I have a sheet full of names, dates and particulars. Over a period of
> time
> some parts of the sheet is copied and dragged down, some cells erased
> etc
> using 'clear contents' in VB .
>
> Invariably some of the cell loses its format and few will be aligned
> center
> and some left and some right. Since the sheet is protected, can anyone
> suggest what is the best way to keep the format that was made for each
> column
> regardless of the shifting and deleting data.
>
> VB is not a solution bcos I dont intend to sit and write alignment code
> for
> 100 columns and hence pls help - is there a way out?
>
> brgds/captgnvr

You could even set up the Macro Recorder to capture these steps if this
became a frequent issue...

Mark Ivey

"Mark Ivey" <(E-Mail Removed)> wrote in message
news:4F4E380C-FAAB-4B60-8417-(E-Mail Removed)...
> Here is one thought...
>
> Manual process...
>
> 1. Unprotect your sheet
> 2. Find one row that meets your requirement for all your formats and
> highlight the entire row
> 3. Right click and select COPY
> 4. Highlight all the other rows that need fixing
> 5. Right click and select PASTE SPECIAL, select FORMATS, and then OK
>
> Walaaa...
>
> Let me know if this helps out...
>
> Mark
>
>
> "CAPTGNVR" <(E-Mail Removed)> wrote in message
> news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
>> DEAR ALL
>>
>> I have a sheet full of names, dates and particulars. Over a period of
>> time
>> some parts of the sheet is copied and dragged down, some cells erased
>> etc
>> using 'clear contents' in VB .
>>
>> Invariably some of the cell loses its format and few will be aligned
>> center
>> and some left and some right. Since the sheet is protected, can anyone
>> suggest what is the best way to keep the format that was made for each
>> column
>> regardless of the shifting and deleting data.
>>
>> VB is not a solution bcos I dont intend to sit and write alignment code
>> for
>> 100 columns and hence pls help - is there a way out?
>>
>> brgds/captgnvr
>

I just read Mark's posting and realized I forgot the sheet is protected.
Here is a modification of my code to handle that...

Sub RealignColumns()
Dim C As Range
With Worksheets("Sheet1")
.Unprotect
For Each C In .Columns
C.HorizontalAlignment = .Cells(Rows.Count, C.Column). _
HorizontalAlignment
Next
.Protect
End With
End Sub

Note I assumed Sheet1 is the worksheet we are doing this to... change that
as needed. Also, look at the help files for the Protect and Unprotect method
as you may have to account for passwords and/or protection settings other
than the default ones.

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Assuming you applied your alignment formatting to the entire column and
> not just to a selection of cells, then odds are nothing you have done has
> affected the alignment format of the last cell in each column. If that is
> the case, then this macro should realign the entries in your cells to
> whatever the alignment format for the column it's in is...
>
> Sub RealignColumns()
> Dim C As Range
> For Each C In Columns
> C.HorizontalAlignment = Cells(Rows.Count, C.Column).HorizontalAlignment
> Next
> End Sub
>
> Rick
>
>
> "CAPTGNVR" <(E-Mail Removed)> wrote in message
> news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
>> DEAR ALL
>>
>> I have a sheet full of names, dates and particulars. Over a period of
>> time
>> some parts of the sheet is copied and dragged down, some cells erased
>> etc
>> using 'clear contents' in VB .
>>
>> Invariably some of the cell loses its format and few will be aligned
>> center
>> and some left and some right. Since the sheet is protected, can anyone
>> suggest what is the best way to keep the format that was made for each
>> column
>> regardless of the shifting and deleting data.
>>
>> VB is not a solution bcos I dont intend to sit and write alignment code
>> for
>> 100 columns and hence pls help - is there a way out?
>>
>> brgds/captgnvr
>

D/RICK

Slight language understanding problem. "then odds are nothing you have done
has
> affected the alignment format of the last cell in each column. If that is
> the case, then this macro should realign the ......"

I understand that if I have formatted the complete column then regardless
whatever deleting and clearing contents I do the format should not change--.
Did i understand it correctly??

Secondly, the macro code, how to know how is it aligning (left, right,
middle) bcos I read only horizontal alignment.

Thirdly, where shall i attach this macro.

I would have responded earlier but firefox was not opening the answer and
finally i had to open in IE to see ur help. Thanks for your prompt help.
Pls help little further.

brgds/captgnvr


"Rick Rothstein (MVP - VB)" wrote:

> Assuming you applied your alignment formatting to the entire column and not
> just to a selection of cells, then odds are nothing you have done has
> affected the alignment format of the last cell in each column. If that is
> the case, then this macro should realign the entries in your cells to
> whatever the alignment format for the column it's in is...
>
> Sub RealignColumns()
> Dim C As Range
> For Each C In Columns
> C.HorizontalAlignment = Cells(Rows.Count, C.Column).HorizontalAlignment
> Next
> End Sub
>
> Rick
>
>
> "CAPTGNVR" <(E-Mail Removed)> wrote in message
> news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
> > DEAR ALL
> >
> > I have a sheet full of names, dates and particulars. Over a period of
> > time
> > some parts of the sheet is copied and dragged down, some cells erased
> > etc
> > using 'clear contents' in VB .
> >
> > Invariably some of the cell loses its format and few will be aligned
> > center
> > and some left and some right. Since the sheet is protected, can anyone
> > suggest what is the best way to keep the format that was made for each
> > column
> > regardless of the shifting and deleting data.
> >
> > VB is not a solution bcos I dont intend to sit and write alignment code
> > for
> > 100 columns and hence pls help - is there a way out?
> >
> > brgds/captgnvr
>
>

D/MARK
I have done as u have sugested and it looks fine as of now. So regardless
of what I do in VB like clearing contents, shifting block up a row or down a
row the formats will remain same??

One change from your suggestion. I did not mark the entire row bcos I have
different format after 20 columns. I only highlighted the first row of 20
column and marked next 100rows and pastespecial for formats.

pls advice.

brgds/captgnvr

"Mark Ivey" wrote:

> Here is one thought...
>
> Manual process...
>
> 1. Unprotect your sheet
> 2. Find one row that meets your requirement for all your formats and
> highlight the entire row
> 3. Right click and select COPY
> 4. Highlight all the other rows that need fixing
> 5. Right click and select PASTE SPECIAL, select FORMATS, and then OK
>
> Walaaa...
>
> Let me know if this helps out...
>
> Mark
>
>
> "CAPTGNVR" <(E-Mail Removed)> wrote in message
> news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
> > DEAR ALL
> >
> > I have a sheet full of names, dates and particulars. Over a period of
> > time
> > some parts of the sheet is copied and dragged down, some cells erased
> > etc
> > using 'clear contents' in VB .
> >
> > Invariably some of the cell loses its format and few will be aligned
> > center
> > and some left and some right. Since the sheet is protected, can anyone
> > suggest what is the best way to keep the format that was made for each
> > column
> > regardless of the shifting and deleting data.
> >
> > VB is not a solution bcos I dont intend to sit and write alignment code
> > for
> > 100 columns and hence pls help - is there a way out?
> >
> > brgds/captgnvr
>

That should work as well...

You can set up the Macro Recorder to capture your events and use them again
and again if you see a need for repetition

I am not sure I totally follow... Does this solution fit your needs or were
you needing a different technique/solution?

Mark Ivey

"CAPTGNVR" <(E-Mail Removed)> wrote in message
news:A9E06A2C-27E8-4AE6-9CE0-(E-Mail Removed)...
> D/MARK
> I have done as u have sugested and it looks fine as of now. So regardless
> of what I do in VB like clearing contents, shifting block up a row or down
> a
> row the formats will remain same??
>
> One change from your suggestion. I did not mark the entire row bcos I
> have
> different format after 20 columns. I only highlighted the first row of 20
> column and marked next 100rows and pastespecial for formats.
>
> pls advice.
>
> brgds/captgnvr
>
> "Mark Ivey" wrote:
>
>> Here is one thought...
>>
>> Manual process...
>>
>> 1. Unprotect your sheet
>> 2. Find one row that meets your requirement for all your formats and
>> highlight the entire row
>> 3. Right click and select COPY
>> 4. Highlight all the other rows that need fixing
>> 5. Right click and select PASTE SPECIAL, select FORMATS, and then OK
>>
>> Walaaa...
>>
>> Let me know if this helps out...
>>
>> Mark
>>
>>
>> "CAPTGNVR" <(E-Mail Removed)> wrote in message
>> news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
>> > DEAR ALL
>> >
>> > I have a sheet full of names, dates and particulars. Over a period of
>> > time
>> > some parts of the sheet is copied and dragged down, some cells erased
>> > etc
>> > using 'clear contents' in VB .
>> >
>> > Invariably some of the cell loses its format and few will be aligned
>> > center
>> > and some left and some right. Since the sheet is protected, can anyone
>> > suggest what is the best way to keep the format that was made for each
>> > column
>> > regardless of the shifting and deleting data.
>> >
>> > VB is not a solution bcos I dont intend to sit and write alignment code
>> > for
>> > 100 columns and hence pls help - is there a way out?
>> >
>> > brgds/captgnvr
>>

See inline comments...

> Slight language understanding problem. "then odds are nothing you have
> done
> has
>> affected the alignment format of the last cell in each column. If that is
>> the case, then this macro should realign the ......"
>
> I understand that if I have formatted the complete column then regardless
> whatever deleting and clearing contents I do the format should not
> change--.
> Did i understand it correctly??

You can override the formatting of a column by copy/pasting or dragging a
cell or range of cells with a difrerent formatting into that column.


> Secondly, the macro code, how to know how is it aligning (left, right,
> middle) bcos I read only horizontal alignment.

HorizontalAlignment property of a Range can be set to be either Left, Center
or Right justified. The active line of code simply looks at the alignment
for the last cell in the column and applies it to the entire column which,
in turn, "fixes" and cells having a different alignment. As I said, nothing
you said you are doing on the worksheet should ever have changed any
formatting for the cells on the very last row of the worksheet; so I simply
used that as a template for the realigning the entire column.


> Thirdly, where shall i attach this macro.

Right-click the tab for the worksheet and select View Code from the popup
menu that appears. Copy/Paste my code into the code window that appeared
automatically. Be sure to use the last code I posted as it addresses the
fact that the worksheet is protected (something I left out of my original
code). And also be sure to change my reference to "Sheet1" in the With
statement to the actual name of your worksheet (and remember that name needs
to be in quotes like the sample I showed in my code).


> Thanks for your prompt help.

You are welcome.


Rick

D/RICK

Thanks for the detailed explanation and the time u hv devoted for this. It
was great help.

brgds/captgnvr

"Rick Rothstein (MVP - VB)" wrote:

> I just read Mark's posting and realized I forgot the sheet is protected.
> Here is a modification of my code to handle that...
>
> Sub RealignColumns()
> Dim C As Range
> With Worksheets("Sheet1")
> .Unprotect
> For Each C In .Columns
> C.HorizontalAlignment = .Cells(Rows.Count, C.Column). _
> HorizontalAlignment
> Next
> .Protect
> End With
> End Sub
>
> Note I assumed Sheet1 is the worksheet we are doing this to... change that
> as needed. Also, look at the help files for the Protect and Unprotect method
> as you may have to account for passwords and/or protection settings other
> than the default ones.
>
> Rick
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> > Assuming you applied your alignment formatting to the entire column and
> > not just to a selection of cells, then odds are nothing you have done has
> > affected the alignment format of the last cell in each column. If that is
> > the case, then this macro should realign the entries in your cells to
> > whatever the alignment format for the column it's in is...
> >
> > Sub RealignColumns()
> > Dim C As Range
> > For Each C In Columns
> > C.HorizontalAlignment = Cells(Rows.Count, C.Column).HorizontalAlignment
> > Next
> > End Sub
> >
> > Rick
> >
> >
> > "CAPTGNVR" <(E-Mail Removed)> wrote in message
> > news:46AACAE6-A66B-4B3C-9468-(E-Mail Removed)...
> >> DEAR ALL
> >>
> >> I have a sheet full of names, dates and particulars. Over a period of
> >> time
> >> some parts of the sheet is copied and dragged down, some cells erased
> >> etc
> >> using 'clear contents' in VB .
> >>
> >> Invariably some of the cell loses its format and few will be aligned
> >> center
> >> and some left and some right. Since the sheet is protected, can anyone
> >> suggest what is the best way to keep the format that was made for each
> >> column
> >> regardless of the shifting and deleting data.
> >>
> >> VB is not a solution bcos I dont intend to sit and write alignment code
> >> for
> >> 100 columns and hence pls help - is there a way out?
> >>
> >> brgds/captgnvr
> >
>
>