Find repeat and than merge and centre

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep
0
5/20/2008 4:47:00 AM
excel.newusers 15348 articles. 2 followers. Follow

17 Replies
511 Views

Similar Articles

[PageSpeed] 15

Hi,

I think we need more detail - what do you want to merge and center, multiple 
rows, multiple columns, multiple blocks of cells?  You will loose the data in 
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells 
above each other need to be the same, does all the data on two adjacent rows 
need to be the same, do the rows with duplicate data need to be adjacent?

-- 
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

> Hi!
> 
> How to find repeated data and then merge and centre.
> 
> My data is from A to M.Range from A1 to M4500
> 
> Any Function Or Macro.
> 
> Thanks
> Hardeep
0
5/20/2008 7:43:00 PM
         A       B         C             D
1       HR     500     200          500
2       HR     500     200          500
3       WP     1000  500           1000
4      DP       200    50              200
5      DP       200    50             200
I want like this
        A        B          C              D
1       HR     500       200          500
2
3       WP      500    200           500
4       DP       200     50             200
5       

NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
A1-A2,B1-B2,C1-C2,D1-D2 is same. 
Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
same data. And i want to merge and  centre the same data in the rows.
Merge and Centre option is on the toolbar.

Thanks

Hardeep Kanwar

"ShaneDevenshire" wrote:


> Hi,
> 
> I think we need more detail - what do you want to merge and center, multiple 
> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> all but one of the merged cells, Excel does not merge the data.
> 
> What determines if a range has repeat data - does the data in two cells 
> above each other need to be the same, does all the data on two adjacent rows 
> need to be the same, do the rows with duplicate data need to be adjacent?
> 
> -- 
> Thanks,
> Shane Devenshire
> 
> 
> "Hardeep_kanwar" wrote:
> 
> > Hi!
> > 
> > How to find repeated data and then merge and centre.
> > 
> > My data is from A to M.Range from A1 to M4500
> > 
> > Any Function Or Macro.
> > 
> > Thanks
> > Hardeep
0
5/21/2008 9:59:01 AM
Hardeep

Run this macro based upon contents of Column A

Sub Clear_Stuff()
'Based on code by Sandy Mann July 1st, 2007
    Dim LastRow As Long
    Dim x As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False

    For x = LastRow To 2 Step -1
        If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
            If Cells(x - 1, 1).Value <> "" Then
                If Cells(x - 1, 1).Value <> "" Then
                    Cells(x, 1).EntireRow.ClearContents
                End If
            End If
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Does not merge and center, but clears contents of duplicated row.

You don't want merge and center because only the data in the top left cell of
selected cells will remain.

i.e.  if you selected A2:D2 and merged, only A2 data would remain.


Gord Dibben  MS Excel MVP

On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
<Hardeepkanwar@discussions.microsoft.com> wrote:

>         A       B         C             D
>1       HR     500     200          500
>2       HR     500     200          500
>3       WP     1000  500           1000
>4      DP       200    50              200
>5      DP       200    50             200
>I want like this
>        A        B          C              D
>1       HR     500       200          500
>2
>3       WP      500    200           500
>4       DP       200     50             200
>5       
>
>NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
>A1-A2,B1-B2,C1-C2,D1-D2 is same. 
>Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
>same data. And i want to merge and  centre the same data in the rows.
>Merge and Centre option is on the toolbar.
>
>Thanks
>
>Hardeep Kanwar
>
>"ShaneDevenshire" wrote:
>
>
>> Hi,
>> 
>> I think we need more detail - what do you want to merge and center, multiple 
>> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
>> all but one of the merged cells, Excel does not merge the data.
>> 
>> What determines if a range has repeat data - does the data in two cells 
>> above each other need to be the same, does all the data on two adjacent rows 
>> need to be the same, do the rows with duplicate data need to be adjacent?
>> 
>> -- 
>> Thanks,
>> Shane Devenshire
>> 
>> 
>> "Hardeep_kanwar" wrote:
>> 
>> > Hi!
>> > 
>> > How to find repeated data and then merge and centre.
>> > 
>> > My data is from A to M.Range from A1 to M4500
>> > 
>> > Any Function Or Macro.
>> > 
>> > Thanks
>> > Hardeep

0
Gord
5/21/2008 11:32:57 PM
Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
because the data is same. MY boss want not to delete repeated data but merge 
and centre the same data.

"Gord Dibben" wrote:

> Hardeep
> 
> Run this macro based upon contents of Column A
> 
> Sub Clear_Stuff()
> 'Based on code by Sandy Mann July 1st, 2007
>     Dim LastRow As Long
>     Dim x As Long
>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>     Application.ScreenUpdating = False
> 
>     For x = LastRow To 2 Step -1
>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>             If Cells(x - 1, 1).Value <> "" Then
>                 If Cells(x - 1, 1).Value <> "" Then
>                     Cells(x, 1).EntireRow.ClearContents
>                 End If
>             End If
>         End If
>     Next x
>     Application.ScreenUpdating = True
> End Sub
> 
> Does not merge and center, but clears contents of duplicated row.
> 
> You don't want merge and center because only the data in the top left cell of
> selected cells will remain.
> 
> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> <Hardeepkanwar@discussions.microsoft.com> wrote:
> 
> >         A       B         C             D
> >1       HR     500     200          500
> >2       HR     500     200          500
> >3       WP     1000  500           1000
> >4      DP       200    50              200
> >5      DP       200    50             200
> >I want like this
> >        A        B          C              D
> >1       HR     500       200          500
> >2
> >3       WP      500    200           500
> >4       DP       200     50             200
> >5       
> >
> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
> >same data. And i want to merge and  centre the same data in the rows.
> >Merge and Centre option is on the toolbar.
> >
> >Thanks
> >
> >Hardeep Kanwar
> >
> >"ShaneDevenshire" wrote:
> >
> >
> >> Hi,
> >> 
> >> I think we need more detail - what do you want to merge and center, multiple 
> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> >> all but one of the merged cells, Excel does not merge the data.
> >> 
> >> What determines if a range has repeat data - does the data in two cells 
> >> above each other need to be the same, does all the data on two adjacent rows 
> >> need to be the same, do the rows with duplicate data need to be adjacent?
> >> 
> >> -- 
> >> Thanks,
> >> Shane Devenshire
> >> 
> >> 
> >> "Hardeep_kanwar" wrote:
> >> 
> >> > Hi!
> >> > 
> >> > How to find repeated data and then merge and centre.
> >> > 
> >> > My data is from A to M.Range from A1 to M4500
> >> > 
> >> > Any Function Or Macro.
> >> > 
> >> > Thanks
> >> > Hardeep
> 
> 
0
5/22/2008 3:32:28 AM
I guess I just don't understand what your boss means by "merge and center the
same data"

Merge and center A1:A2 will delete the data in A2.


Gord

On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
<Hardeepkanwar@discussions.microsoft.com> wrote:

>Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
>because the data is same. MY boss want not to delete repeated data but merge 
>and centre the same data.
>
>"Gord Dibben" wrote:
>
>> Hardeep
>> 
>> Run this macro based upon contents of Column A
>> 
>> Sub Clear_Stuff()
>> 'Based on code by Sandy Mann July 1st, 2007
>>     Dim LastRow As Long
>>     Dim x As Long
>>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>>     Application.ScreenUpdating = False
>> 
>>     For x = LastRow To 2 Step -1
>>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>>             If Cells(x - 1, 1).Value <> "" Then
>>                 If Cells(x - 1, 1).Value <> "" Then
>>                     Cells(x, 1).EntireRow.ClearContents
>>                 End If
>>             End If
>>         End If
>>     Next x
>>     Application.ScreenUpdating = True
>> End Sub
>> 
>> Does not merge and center, but clears contents of duplicated row.
>> 
>> You don't want merge and center because only the data in the top left cell of
>> selected cells will remain.
>> 
>> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> 
>> >         A       B         C             D
>> >1       HR     500     200          500
>> >2       HR     500     200          500
>> >3       WP     1000  500           1000
>> >4      DP       200    50              200
>> >5      DP       200    50             200
>> >I want like this
>> >        A        B          C              D
>> >1       HR     500       200          500
>> >2
>> >3       WP      500    200           500
>> >4       DP       200     50             200
>> >5       
>> >
>> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
>> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
>> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
>> >same data. And i want to merge and  centre the same data in the rows.
>> >Merge and Centre option is on the toolbar.
>> >
>> >Thanks
>> >
>> >Hardeep Kanwar
>> >
>> >"ShaneDevenshire" wrote:
>> >
>> >
>> >> Hi,
>> >> 
>> >> I think we need more detail - what do you want to merge and center, multiple 
>> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
>> >> all but one of the merged cells, Excel does not merge the data.
>> >> 
>> >> What determines if a range has repeat data - does the data in two cells 
>> >> above each other need to be the same, does all the data on two adjacent rows 
>> >> need to be the same, do the rows with duplicate data need to be adjacent?
>> >> 
>> >> -- 
>> >> Thanks,
>> >> Shane Devenshire
>> >> 
>> >> 
>> >> "Hardeep_kanwar" wrote:
>> >> 
>> >> > Hi!
>> >> > 
>> >> > How to find repeated data and then merge and centre.
>> >> > 
>> >> > My data is from A to M.Range from A1 to M4500
>> >> > 
>> >> > Any Function Or Macro.
>> >> > 
>> >> > Thanks
>> >> > Hardeep
>> 
>> 

0
Gord
5/22/2008 3:26:00 PM
So what, it does't matter for me If A2 is deleted Because the data is same.If 
the data is not same then this is the problem for me. My question is not to 
deleted repeated data but merge and centre the data.


Just see my example


Thanks
hardeep


"Gord Dibben" wrote:

> I guess I just don't understand what your boss means by "merge and center the
> same data"
> 
> Merge and center A1:A2 will delete the data in A2.
> 
> 
> Gord
> 
> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> <Hardeepkanwar@discussions.microsoft.com> wrote:
> 
> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
> >because the data is same. MY boss want not to delete repeated data but merge 
> >and centre the same data.
> >
> >"Gord Dibben" wrote:
> >
> >> Hardeep
> >> 
> >> Run this macro based upon contents of Column A
> >> 
> >> Sub Clear_Stuff()
> >> 'Based on code by Sandy Mann July 1st, 2007
> >>     Dim LastRow As Long
> >>     Dim x As Long
> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >>     Application.ScreenUpdating = False
> >> 
> >>     For x = LastRow To 2 Step -1
> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >>             If Cells(x - 1, 1).Value <> "" Then
> >>                 If Cells(x - 1, 1).Value <> "" Then
> >>                     Cells(x, 1).EntireRow.ClearContents
> >>                 End If
> >>             End If
> >>         End If
> >>     Next x
> >>     Application.ScreenUpdating = True
> >> End Sub
> >> 
> >> Does not merge and center, but clears contents of duplicated row.
> >> 
> >> You don't want merge and center because only the data in the top left cell of
> >> selected cells will remain.
> >> 
> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
> >> 
> >> 
> >> Gord Dibben  MS Excel MVP
> >> 
> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> 
> >> >         A       B         C             D
       HR     500     200          500
       HR     500     200          500
       WP     1000  500           1000
       DP       200    50              200
       DP       200    50             200
> >> >I want like this
> >> >        A        B          C              D
     HR     500       200          500

      WP      500    200           500
      DP       200     50             200
      
> >> >
> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
> >> >same data. And i want to merge and  centre the same data in the rows.
> >> >Merge and Centre option is on the toolbar.
> >> >
> >> >Thanks
> >> >
> >> >Hardeep Kanwar
> >> >
> >> >"ShaneDevenshire" wrote:
> >> >
> >> >
> >> >> Hi,
> >> >> 
> >> >> I think we need more detail - what do you want to merge and center, multiple 
> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> >> >> all but one of the merged cells, Excel does not merge the data.
> >> >> 
> >> >> What determines if a range has repeat data - does the data in two cells 
> >> >> above each other need to be the same, does all the data on two adjacent rows 
> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
> >> >> 
> >> >> -- 
> >> >> Thanks,
> >> >> Shane Devenshire
> >> >> 
> >> >> 
> >> >> "Hardeep_kanwar" wrote:
> >> >> 
> >> >> > Hi!
> >> >> > 
> >> >> > How to find repeated data and then merge and centre.
> >> >> > 
> >> >> > My data is from A to M.Range from A1 to M4500
> >> >> > 
> >> >> > Any Function Or Macro.
> >> >> > 
> >> >> > Thanks
> >> >> > Hardeep
> >> 
> >> 
> 
> 
0
5/23/2008 6:29:00 AM
So what??

Getting a little testy are we?

Sub Clear_Stuff()
'Based on code by Sandy Mann July 1st, 2007
    Dim LastRow As Long
    Dim x As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For x = LastRow To 2 Step -1
        If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
            If Cells(x - 1, 1).Value <> "" Then
                If Cells(x - 1, 1).Value <> "" Then
                    Cells(x - 1, 1).Resize(2).MergeCells = True
                    Cells(x - 1, 2).Resize(2).MergeCells = True
                    Cells(x - 1, 3).Resize(2).MergeCells = True
                    Cells(x - 1, 4).Resize(2).MergeCells = True
                    
                End If
            End If
        End If
    Next x
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

I hope your boss likes his merged and centered cells which will cause him no end
of grief in the future if tries to copy, paste, sort, filter or many other
operations that won't work with merged cells.


Gord


On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
<Hardeepkanwar@discussions.microsoft.com> wrote:

>So what, it does't matter for me If A2 is deleted Because the data is same.If 
>the data is not same then this is the problem for me. My question is not to 
>deleted repeated data but merge and centre the data.
>
>
>Just see my example
>
>
>Thanks
>hardeep
>
>
>"Gord Dibben" wrote:
>
>> I guess I just don't understand what your boss means by "merge and center the
>> same data"
>> 
>> Merge and center A1:A2 will delete the data in A2.
>> 
>> 
>> Gord
>> 
>> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
>> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> 
>> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
>> >because the data is same. MY boss want not to delete repeated data but merge 
>> >and centre the same data.
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Hardeep
>> >> 
>> >> Run this macro based upon contents of Column A
>> >> 
>> >> Sub Clear_Stuff()
>> >> 'Based on code by Sandy Mann July 1st, 2007
>> >>     Dim LastRow As Long
>> >>     Dim x As Long
>> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >>     Application.ScreenUpdating = False
>> >> 
>> >>     For x = LastRow To 2 Step -1
>> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> >>             If Cells(x - 1, 1).Value <> "" Then
>> >>                 If Cells(x - 1, 1).Value <> "" Then
>> >>                     Cells(x, 1).EntireRow.ClearContents
>> >>                 End If
>> >>             End If
>> >>         End If
>> >>     Next x
>> >>     Application.ScreenUpdating = True
>> >> End Sub
>> >> 
>> >> Does not merge and center, but clears contents of duplicated row.
>> >> 
>> >> You don't want merge and center because only the data in the top left cell of
>> >> selected cells will remain.
>> >> 
>> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
>> >> 
>> >> 
>> >> Gord Dibben  MS Excel MVP
>> >> 
>> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> 
>> >> >         A       B         C             D
>       HR     500     200          500
>       HR     500     200          500
>       WP     1000  500           1000
>       DP       200    50              200
>       DP       200    50             200
>> >> >I want like this
>> >> >        A        B          C              D
>     HR     500       200          500
>
>      WP      500    200           500
>      DP       200     50             200
>      
>> >> >
>> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
>> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
>> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
>> >> >same data. And i want to merge and  centre the same data in the rows.
>> >> >Merge and Centre option is on the toolbar.
>> >> >
>> >> >Thanks
>> >> >
>> >> >Hardeep Kanwar
>> >> >
>> >> >"ShaneDevenshire" wrote:
>> >> >
>> >> >
>> >> >> Hi,
>> >> >> 
>> >> >> I think we need more detail - what do you want to merge and center, multiple 
>> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
>> >> >> all but one of the merged cells, Excel does not merge the data.
>> >> >> 
>> >> >> What determines if a range has repeat data - does the data in two cells 
>> >> >> above each other need to be the same, does all the data on two adjacent rows 
>> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
>> >> >> 
>> >> >> -- 
>> >> >> Thanks,
>> >> >> Shane Devenshire
>> >> >> 
>> >> >> 
>> >> >> "Hardeep_kanwar" wrote:
>> >> >> 
>> >> >> > Hi!
>> >> >> > 
>> >> >> > How to find repeated data and then merge and centre.
>> >> >> > 
>> >> >> > My data is from A to M.Range from A1 to M4500
>> >> >> > 
>> >> >> > Any Function Or Macro.
>> >> >> > 
>> >> >> > Thanks
>> >> >> > Hardeep
>> >> 
>> >> 
>> 
>> 

0
Gord
5/23/2008 11:29:53 PM
Dear Sir,
Thanks for Code, but unfortunatally it work on some cells not in all cells
I want to give the attach file.
So. pls provide me your e-mail id

Thanks once more for the code

Hardeep kanwar

"Gord Dibben" wrote:

> So what??
> 
> Getting a little testy are we?
> 
> Sub Clear_Stuff()
> 'Based on code by Sandy Mann July 1st, 2007
>     Dim LastRow As Long
>     Dim x As Long
>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>     Application.ScreenUpdating = False
>     Application.DisplayAlerts = False
> 
>     For x = LastRow To 2 Step -1
>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>             If Cells(x - 1, 1).Value <> "" Then
>                 If Cells(x - 1, 1).Value <> "" Then
>                     Cells(x - 1, 1).Resize(2).MergeCells = True
>                     Cells(x - 1, 2).Resize(2).MergeCells = True
>                     Cells(x - 1, 3).Resize(2).MergeCells = True
>                     Cells(x - 1, 4).Resize(2).MergeCells = True
>                     
>                 End If
>             End If
>         End If
>     Next x
>     Application.ScreenUpdating = True
>     Application.DisplayAlerts = True
> End Sub
> 
> I hope your boss likes his merged and centered cells which will cause him no end
> of grief in the future if tries to copy, paste, sort, filter or many other
> operations that won't work with merged cells.
> 
> 
> Gord
> 
> 
> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> <Hardeepkanwar@discussions.microsoft.com> wrote:
> 
> >So what, it does't matter for me If A2 is deleted Because the data is same.If 
> >the data is not same then this is the problem for me. My question is not to 
> >deleted repeated data but merge and centre the data.
> >
> >
> >Just see my example
> >
> >
> >Thanks
> >hardeep
> >
> >
> >"Gord Dibben" wrote:
> >
> >> I guess I just don't understand what your boss means by "merge and center the
> >> same data"
> >> 
> >> Merge and center A1:A2 will delete the data in A2.
> >> 
> >> 
> >> Gord
> >> 
> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> 
> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
> >> >because the data is same. MY boss want not to delete repeated data but merge 
> >> >and centre the same data.
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> Hardeep
> >> >> 
> >> >> Run this macro based upon contents of Column A
> >> >> 
> >> >> Sub Clear_Stuff()
> >> >> 'Based on code by Sandy Mann July 1st, 2007
> >> >>     Dim LastRow As Long
> >> >>     Dim x As Long
> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >>     Application.ScreenUpdating = False
> >> >> 
> >> >>     For x = LastRow To 2 Step -1
> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> >>             If Cells(x - 1, 1).Value <> "" Then
> >> >>                 If Cells(x - 1, 1).Value <> "" Then
> >> >>                     Cells(x, 1).EntireRow.ClearContents
> >> >>                 End If
> >> >>             End If
> >> >>         End If
> >> >>     Next x
> >> >>     Application.ScreenUpdating = True
> >> >> End Sub
> >> >> 
> >> >> Does not merge and center, but clears contents of duplicated row.
> >> >> 
> >> >> You don't want merge and center because only the data in the top left cell of
> >> >> selected cells will remain.
> >> >> 
> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
> >> >> 
> >> >> 
> >> >> Gord Dibben  MS Excel MVP
> >> >> 
> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >         A       B         C             D
> >       HR     500     200          500
> >       HR     500     200          500
> >       WP     1000  500           1000
> >       DP       200    50              200
> >       DP       200    50             200
> >> >> >I want like this
> >> >> >        A        B          C              D
> >     HR     500       200          500
> >
> >      WP      500    200           500
> >      DP       200     50             200
> >      
> >> >> >
> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
> >> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
> >> >> >same data. And i want to merge and  centre the same data in the rows.
> >> >> >Merge and Centre option is on the toolbar.
> >> >> >
> >> >> >Thanks
> >> >> >
> >> >> >Hardeep Kanwar
> >> >> >
> >> >> >"ShaneDevenshire" wrote:
> >> >> >
> >> >> >
> >> >> >> Hi,
> >> >> >> 
> >> >> >> I think we need more detail - what do you want to merge and center, multiple 
> >> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> >> >> >> all but one of the merged cells, Excel does not merge the data.
> >> >> >> 
> >> >> >> What determines if a range has repeat data - does the data in two cells 
> >> >> >> above each other need to be the same, does all the data on two adjacent rows 
> >> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
> >> >> >> 
> >> >> >> -- 
> >> >> >> Thanks,
> >> >> >> Shane Devenshire
> >> >> >> 
> >> >> >> 
> >> >> >> "Hardeep_kanwar" wrote:
> >> >> >> 
> >> >> >> > Hi!
> >> >> >> > 
> >> >> >> > How to find repeated data and then merge and centre.
> >> >> >> > 
> >> >> >> > My data is from A to M.Range from A1 to M4500
> >> >> >> > 
> >> >> >> > Any Function Or Macro.
> >> >> >> > 
> >> >> >> > Thanks
> >> >> >> > Hardeep
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
5/25/2008 8:12:01 AM
You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/ 
http://savefile.com/

When you have uploaded to that site, post the URL so we could download should we
choose.


Gord

On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
<Hardeepkanwar@discussions.microsoft.com> wrote:

>Dear Sir,
>Thanks for Code, but unfortunatally it work on some cells not in all cells
>I want to give the attach file.
>So. pls provide me your e-mail id
>
>Thanks once more for the code
>
>Hardeep kanwar
>
>"Gord Dibben" wrote:
>
>> So what??
>> 
>> Getting a little testy are we?
>> 
>> Sub Clear_Stuff()
>> 'Based on code by Sandy Mann July 1st, 2007
>>     Dim LastRow As Long
>>     Dim x As Long
>>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>>     Application.ScreenUpdating = False
>>     Application.DisplayAlerts = False
>> 
>>     For x = LastRow To 2 Step -1
>>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>>             If Cells(x - 1, 1).Value <> "" Then
>>                 If Cells(x - 1, 1).Value <> "" Then
>>                     Cells(x - 1, 1).Resize(2).MergeCells = True
>>                     Cells(x - 1, 2).Resize(2).MergeCells = True
>>                     Cells(x - 1, 3).Resize(2).MergeCells = True
>>                     Cells(x - 1, 4).Resize(2).MergeCells = True
>>                     
>>                 End If
>>             End If
>>         End If
>>     Next x
>>     Application.ScreenUpdating = True
>>     Application.DisplayAlerts = True
>> End Sub
>> 
>> I hope your boss likes his merged and centered cells which will cause him no end
>> of grief in the future if tries to copy, paste, sort, filter or many other
>> operations that won't work with merged cells.
>> 
>> 
>> Gord
>> 
>> 
>> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
>> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> 
>> >So what, it does't matter for me If A2 is deleted Because the data is same.If 
>> >the data is not same then this is the problem for me. My question is not to 
>> >deleted repeated data but merge and centre the data.
>> >
>> >
>> >Just see my example
>> >
>> >
>> >Thanks
>> >hardeep
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> I guess I just don't understand what your boss means by "merge and center the
>> >> same data"
>> >> 
>> >> Merge and center A1:A2 will delete the data in A2.
>> >> 
>> >> 
>> >> Gord
>> >> 
>> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
>> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> 
>> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
>> >> >because the data is same. MY boss want not to delete repeated data but merge 
>> >> >and centre the same data.
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> Hardeep
>> >> >> 
>> >> >> Run this macro based upon contents of Column A
>> >> >> 
>> >> >> Sub Clear_Stuff()
>> >> >> 'Based on code by Sandy Mann July 1st, 2007
>> >> >>     Dim LastRow As Long
>> >> >>     Dim x As Long
>> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >> >>     Application.ScreenUpdating = False
>> >> >> 
>> >> >>     For x = LastRow To 2 Step -1
>> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> >> >>             If Cells(x - 1, 1).Value <> "" Then
>> >> >>                 If Cells(x - 1, 1).Value <> "" Then
>> >> >>                     Cells(x, 1).EntireRow.ClearContents
>> >> >>                 End If
>> >> >>             End If
>> >> >>         End If
>> >> >>     Next x
>> >> >>     Application.ScreenUpdating = True
>> >> >> End Sub
>> >> >> 
>> >> >> Does not merge and center, but clears contents of duplicated row.
>> >> >> 
>> >> >> You don't want merge and center because only the data in the top left cell of
>> >> >> selected cells will remain.
>> >> >> 
>> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
>> >> >> 
>> >> >> 
>> >> >> Gord Dibben  MS Excel MVP
>> >> >> 
>> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >> 
>> >> >> >         A       B         C             D
>> >       HR     500     200          500
>> >       HR     500     200          500
>> >       WP     1000  500           1000
>> >       DP       200    50              200
>> >       DP       200    50             200
>> >> >> >I want like this
>> >> >> >        A        B          C              D
>> >     HR     500       200          500
>> >
>> >      WP      500    200           500
>> >      DP       200     50             200
>> >      
>> >> >> >
>> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
>> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
>> >> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
>> >> >> >same data. And i want to merge and  centre the same data in the rows.
>> >> >> >Merge and Centre option is on the toolbar.
>> >> >> >
>> >> >> >Thanks
>> >> >> >
>> >> >> >Hardeep Kanwar
>> >> >> >
>> >> >> >"ShaneDevenshire" wrote:
>> >> >> >
>> >> >> >
>> >> >> >> Hi,
>> >> >> >> 
>> >> >> >> I think we need more detail - what do you want to merge and center, multiple 
>> >> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
>> >> >> >> all but one of the merged cells, Excel does not merge the data.
>> >> >> >> 
>> >> >> >> What determines if a range has repeat data - does the data in two cells 
>> >> >> >> above each other need to be the same, does all the data on two adjacent rows 
>> >> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
>> >> >> >> 
>> >> >> >> -- 
>> >> >> >> Thanks,
>> >> >> >> Shane Devenshire
>> >> >> >> 
>> >> >> >> 
>> >> >> >> "Hardeep_kanwar" wrote:
>> >> >> >> 
>> >> >> >> > Hi!
>> >> >> >> > 
>> >> >> >> > How to find repeated data and then merge and centre.
>> >> >> >> > 
>> >> >> >> > My data is from A to M.Range from A1 to M4500
>> >> >> >> > 
>> >> >> >> > Any Function Or Macro.
>> >> >> >> > 
>> >> >> >> > Thanks
>> >> >> >> > Hardeep
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>> 

0
Gord
5/25/2008 5:01:59 PM
Dear sir

Pls find the attached file in this link
http://www.savefile.com/files/1574364

Regards 
Hardeep kanwar

"Gord Dibben" wrote:

> You can post the workbook at one of the hosting sites below.
> 
> http://www.freefilehosting.net/ 
> http://savefile.com/
> 
> When you have uploaded to that site, post the URL so we could download should we
> choose.
> 
> 
> Gord
> 
> On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
> <Hardeepkanwar@discussions.microsoft.com> wrote:
> 
> >Dear Sir,
> >Thanks for Code, but unfortunatally it work on some cells not in all cells
> >I want to give the attach file.
> >So. pls provide me your e-mail id
> >
> >Thanks once more for the code
> >
> >Hardeep kanwar
> >
> >"Gord Dibben" wrote:
> >
> >> So what??
> >> 
> >> Getting a little testy are we?
> >> 
> >> Sub Clear_Stuff()
> >> 'Based on code by Sandy Mann July 1st, 2007
> >>     Dim LastRow As Long
> >>     Dim x As Long
> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >>     Application.ScreenUpdating = False
> >>     Application.DisplayAlerts = False
> >> 
> >>     For x = LastRow To 2 Step -1
> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >>             If Cells(x - 1, 1).Value <> "" Then
> >>                 If Cells(x - 1, 1).Value <> "" Then
> >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
> >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
> >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
> >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
> >>                     
> >>                 End If
> >>             End If
> >>         End If
> >>     Next x
> >>     Application.ScreenUpdating = True
> >>     Application.DisplayAlerts = True
> >> End Sub
> >> 
> >> I hope your boss likes his merged and centered cells which will cause him no end
> >> of grief in the future if tries to copy, paste, sort, filter or many other
> >> operations that won't work with merged cells.
> >> 
> >> 
> >> Gord
> >> 
> >> 
> >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> 
> >> >So what, it does't matter for me If A2 is deleted Because the data is same.If 
> >> >the data is not same then this is the problem for me. My question is not to 
> >> >deleted repeated data but merge and centre the data.
> >> >
> >> >
> >> >Just see my example
> >> >
> >> >
> >> >Thanks
> >> >hardeep
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> I guess I just don't understand what your boss means by "merge and center the
> >> >> same data"
> >> >> 
> >> >> Merge and center A1:A2 will delete the data in A2.
> >> >> 
> >> >> 
> >> >> Gord
> >> >> 
> >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
> >> >> >because the data is same. MY boss want not to delete repeated data but merge 
> >> >> >and centre the same data.
> >> >> >
> >> >> >"Gord Dibben" wrote:
> >> >> >
> >> >> >> Hardeep
> >> >> >> 
> >> >> >> Run this macro based upon contents of Column A
> >> >> >> 
> >> >> >> Sub Clear_Stuff()
> >> >> >> 'Based on code by Sandy Mann July 1st, 2007
> >> >> >>     Dim LastRow As Long
> >> >> >>     Dim x As Long
> >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >> >>     Application.ScreenUpdating = False
> >> >> >> 
> >> >> >>     For x = LastRow To 2 Step -1
> >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> >> >>             If Cells(x - 1, 1).Value <> "" Then
> >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
> >> >> >>                     Cells(x, 1).EntireRow.ClearContents
> >> >> >>                 End If
> >> >> >>             End If
> >> >> >>         End If
> >> >> >>     Next x
> >> >> >>     Application.ScreenUpdating = True
> >> >> >> End Sub
> >> >> >> 
> >> >> >> Does not merge and center, but clears contents of duplicated row.
> >> >> >> 
> >> >> >> You don't want merge and center because only the data in the top left cell of
> >> >> >> selected cells will remain.
> >> >> >> 
> >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
> >> >> >> 
> >> >> >> 
> >> >> >> Gord Dibben  MS Excel MVP
> >> >> >> 
> >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> >> 
> >> >> >> >         A       B         C             D
> >> >       HR     500     200          500
> >> >       HR     500     200          500
> >> >       WP     1000  500           1000
> >> >       DP       200    50              200
> >> >       DP       200    50             200
> >> >> >> >I want like this
> >> >> >> >        A        B          C              D
> >> >     HR     500       200          500
> >> >
> >> >      WP      500    200           500
> >> >      DP       200     50             200
> >> >      
> >> >> >> >
> >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
> >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
> >> >> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
> >> >> >> >same data. And i want to merge and  centre the same data in the rows.
> >> >> >> >Merge and Centre option is on the toolbar.
> >> >> >> >
> >> >> >> >Thanks
> >> >> >> >
> >> >> >> >Hardeep Kanwar
> >> >> >> >
> >> >> >> >"ShaneDevenshire" wrote:
> >> >> >> >
> >> >> >> >
> >> >> >> >> Hi,
> >> >> >> >> 
> >> >> >> >> I think we need more detail - what do you want to merge and center, multiple 
> >> >> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> >> >> >> >> all but one of the merged cells, Excel does not merge the data.
> >> >> >> >> 
> >> >> >> >> What determines if a range has repeat data - does the data in two cells 
> >> >> >> >> above each other need to be the same, does all the data on two adjacent rows 
> >> >> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
> >> >> >> >> 
> >> >> >> >> -- 
> >> >> >> >> Thanks,
> >> >> >> >> Shane Devenshire
> >> >> >> >> 
> >> >> >> >> 
> >> >> >> >> "Hardeep_kanwar" wrote:
> >> >> >> >> 
> >> >> >> >> > Hi!
> >> >> >> >> > 
> >> >> >> >> > How to find repeated data and then merge and centre.
> >> >> >> >> > 
> >> >> >> >> > My data is from A to M.Range from A1 to M4500
> >> >> >> >> > 
> >> >> >> >> > Any Function Or Macro.
> >> >> >> >> > 
> >> >> >> >> > Thanks
> >> >> >> >> > Hardeep
> >> >> >> 
> >> >> >> 
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
5/26/2008 3:34:01 PM
Dear sir
Pls ignore the previous file
Find the new  attached file (http://www.savefile.com/files/1574440)
This is the sample of my Master sheet

Thanks
Hardeep kanwar

"Hardeep_kanwar" wrote:

> Dear sir
> 
> Pls find the attached file in this link
> http://www.savefile.com/files/1574364
> 
> Regards 
> Hardeep kanwar
> 
> "Gord Dibben" wrote:
> 
> > You can post the workbook at one of the hosting sites below.
> > 
> > http://www.freefilehosting.net/ 
> > http://savefile.com/
> > 
> > When you have uploaded to that site, post the URL so we could download should we
> > choose.
> > 
> > 
> > Gord
> > 
> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
> > <Hardeepkanwar@discussions.microsoft.com> wrote:
> > 
> > >Dear Sir,
> > >Thanks for Code, but unfortunatally it work on some cells not in all cells
> > >I want to give the attach file.
> > >So. pls provide me your e-mail id
> > >
> > >Thanks once more for the code
> > >
> > >Hardeep kanwar
> > >
> > >"Gord Dibben" wrote:
> > >
> > >> So what??
> > >> 
> > >> Getting a little testy are we?
> > >> 
> > >> Sub Clear_Stuff()
> > >> 'Based on code by Sandy Mann July 1st, 2007
> > >>     Dim LastRow As Long
> > >>     Dim x As Long
> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> > >>     Application.ScreenUpdating = False
> > >>     Application.DisplayAlerts = False
> > >> 
> > >>     For x = LastRow To 2 Step -1
> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> > >>             If Cells(x - 1, 1).Value <> "" Then
> > >>                 If Cells(x - 1, 1).Value <> "" Then
> > >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
> > >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
> > >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
> > >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
> > >>                     
> > >>                 End If
> > >>             End If
> > >>         End If
> > >>     Next x
> > >>     Application.ScreenUpdating = True
> > >>     Application.DisplayAlerts = True
> > >> End Sub
> > >> 
> > >> I hope your boss likes his merged and centered cells which will cause him no end
> > >> of grief in the future if tries to copy, paste, sort, filter or many other
> > >> operations that won't work with merged cells.
> > >> 
> > >> 
> > >> Gord
> > >> 
> > >> 
> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> > >> 
> > >> >So what, it does't matter for me If A2 is deleted Because the data is same.If 
> > >> >the data is not same then this is the problem for me. My question is not to 
> > >> >deleted repeated data but merge and centre the data.
> > >> >
> > >> >
> > >> >Just see my example
> > >> >
> > >> >
> > >> >Thanks
> > >> >hardeep
> > >> >
> > >> >
> > >> >"Gord Dibben" wrote:
> > >> >
> > >> >> I guess I just don't understand what your boss means by "merge and center the
> > >> >> same data"
> > >> >> 
> > >> >> Merge and center A1:A2 will delete the data in A2.
> > >> >> 
> > >> >> 
> > >> >> Gord
> > >> >> 
> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> > >> >> 
> > >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9 
> > >> >> >because the data is same. MY boss want not to delete repeated data but merge 
> > >> >> >and centre the same data.
> > >> >> >
> > >> >> >"Gord Dibben" wrote:
> > >> >> >
> > >> >> >> Hardeep
> > >> >> >> 
> > >> >> >> Run this macro based upon contents of Column A
> > >> >> >> 
> > >> >> >> Sub Clear_Stuff()
> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
> > >> >> >>     Dim LastRow As Long
> > >> >> >>     Dim x As Long
> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> > >> >> >>     Application.ScreenUpdating = False
> > >> >> >> 
> > >> >> >>     For x = LastRow To 2 Step -1
> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
> > >> >> >>                 End If
> > >> >> >>             End If
> > >> >> >>         End If
> > >> >> >>     Next x
> > >> >> >>     Application.ScreenUpdating = True
> > >> >> >> End Sub
> > >> >> >> 
> > >> >> >> Does not merge and center, but clears contents of duplicated row.
> > >> >> >> 
> > >> >> >> You don't want merge and center because only the data in the top left cell of
> > >> >> >> selected cells will remain.
> > >> >> >> 
> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would remain.
> > >> >> >> 
> > >> >> >> 
> > >> >> >> Gord Dibben  MS Excel MVP
> > >> >> >> 
> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> > >> >> >> 
> > >> >> >> >         A       B         C             D
> > >> >       HR     500     200          500
> > >> >       HR     500     200          500
> > >> >       WP     1000  500           1000
> > >> >       DP       200    50              200
> > >> >       DP       200    50             200
> > >> >> >> >I want like this
> > >> >> >> >        A        B          C              D
> > >> >     HR     500       200          500
> > >> >
> > >> >      WP      500    200           500
> > >> >      DP       200     50             200
> > >> >      
> > >> >> >> >
> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in 
> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same. 
> > >> >> >> >Or you may say that The rows are same it may goes to 3 or 4 or 7  rows are 
> > >> >> >> >same data. And i want to merge and  centre the same data in the rows.
> > >> >> >> >Merge and Centre option is on the toolbar.
> > >> >> >> >
> > >> >> >> >Thanks
> > >> >> >> >
> > >> >> >> >Hardeep Kanwar
> > >> >> >> >
> > >> >> >> >"ShaneDevenshire" wrote:
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >> Hi,
> > >> >> >> >> 
> > >> >> >> >> I think we need more detail - what do you want to merge and center, multiple 
> > >> >> >> >> rows, multiple columns, multiple blocks of cells?  You will loose the data in 
> > >> >> >> >> all but one of the merged cells, Excel does not merge the data.
> > >> >> >> >> 
> > >> >> >> >> What determines if a range has repeat data - does the data in two cells 
> > >> >> >> >> above each other need to be the same, does all the data on two adjacent rows 
> > >> >> >> >> need to be the same, do the rows with duplicate data need to be adjacent?
> > >> >> >> >> 
> > >> >> >> >> -- 
> > >> >> >> >> Thanks,
> > >> >> >> >> Shane Devenshire
> > >> >> >> >> 
> > >> >> >> >> 
> > >> >> >> >> "Hardeep_kanwar" wrote:
> > >> >> >> >> 
> > >> >> >> >> > Hi!
> > >> >> >> >> > 
> > >> >> >> >> > How to find repeated data and then merge and centre.
> > >> >> >> >> > 
> > >> >> >> >> > My data is from A to M.Range from A1 to M4500
> > >> >> >> >> > 
> > >> >> >> >> > Any Function Or Macro.
> > >> >> >> >> > 
> > >> >> >> >> > Thanks
> > >> >> >> >> > Hardeep
> > >> >> >> 
> > >> >> >> 
> > >> >> 
> > >> >> 
> > >> 
> > >> 
> > 
> > 
0
5/26/2008 4:09:00 PM
I am not saying that my programming skills are any better than Gord's but 
try this simple minded Macro:

Option Explicit
Sub MergeItandCentre()
    Dim LastRow As Long
    Dim LineRow As Long
    Dim cCell As Long
    Dim Col As Long
    Dim N As Integer

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    For LineRow = 1 To LastRow - 1
        For Col = 1 To 11
            If Cells(LineRow, Col).Value = _
                Cells(LineRow + 1, Col).Value Then
                    N = N + 1
            End If
        Next Col

        If N = 11 Then
            For cCell = 1 To 11
                With Range(Cells(LineRow, cCell), Cells(LineRow + 1, cCell))
                    .MergeCells = True
                    .VerticalAlignment = xlCenter
                End With
            Next cCell
        End If

        N = 0

    Next LineRow

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True


End Sub



-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message 
news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
> Dear sir
> Pls ignore the previous file
> Find the new  attached file (http://www.savefile.com/files/1574440)
> This is the sample of my Master sheet
>
> Thanks
> Hardeep kanwar
>
> "Hardeep_kanwar" wrote:
>
>> Dear sir
>>
>> Pls find the attached file in this link
>> http://www.savefile.com/files/1574364
>>
>> Regards
>> Hardeep kanwar
>>
>> "Gord Dibben" wrote:
>>
>> > You can post the workbook at one of the hosting sites below.
>> >
>> > http://www.freefilehosting.net/
>> > http://savefile.com/
>> >
>> > When you have uploaded to that site, post the URL so we could download 
>> > should we
>> > choose.
>> >
>> >
>> > Gord
>> >
>> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
>> > <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >
>> > >Dear Sir,
>> > >Thanks for Code, but unfortunatally it work on some cells not in all 
>> > >cells
>> > >I want to give the attach file.
>> > >So. pls provide me your e-mail id
>> > >
>> > >Thanks once more for the code
>> > >
>> > >Hardeep kanwar
>> > >
>> > >"Gord Dibben" wrote:
>> > >
>> > >> So what??
>> > >>
>> > >> Getting a little testy are we?
>> > >>
>> > >> Sub Clear_Stuff()
>> > >> 'Based on code by Sandy Mann July 1st, 2007
>> > >>     Dim LastRow As Long
>> > >>     Dim x As Long
>> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> > >>     Application.ScreenUpdating = False
>> > >>     Application.DisplayAlerts = False
>> > >>
>> > >>     For x = LastRow To 2 Step -1
>> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> > >>             If Cells(x - 1, 1).Value <> "" Then
>> > >>                 If Cells(x - 1, 1).Value <> "" Then
>> > >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
>> > >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
>> > >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
>> > >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
>> > >>
>> > >>                 End If
>> > >>             End If
>> > >>         End If
>> > >>     Next x
>> > >>     Application.ScreenUpdating = True
>> > >>     Application.DisplayAlerts = True
>> > >> End Sub
>> > >>
>> > >> I hope your boss likes his merged and centered cells which will 
>> > >> cause him no end
>> > >> of grief in the future if tries to copy, paste, sort, filter or many 
>> > >> other
>> > >> operations that won't work with merged cells.
>> > >>
>> > >>
>> > >> Gord
>> > >>
>> > >>
>> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
>> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> > >>
>> > >> >So what, it does't matter for me If A2 is deleted Because the data 
>> > >> >is same.If
>> > >> >the data is not same then this is the problem for me. My question 
>> > >> >is not to
>> > >> >deleted repeated data but merge and centre the data.
>> > >> >
>> > >> >
>> > >> >Just see my example
>> > >> >
>> > >> >
>> > >> >Thanks
>> > >> >hardeep
>> > >> >
>> > >> >
>> > >> >"Gord Dibben" wrote:
>> > >> >
>> > >> >> I guess I just don't understand what your boss means by "merge 
>> > >> >> and center the
>> > >> >> same data"
>> > >> >>
>> > >> >> Merge and center A1:A2 will delete the data in A2.
>> > >> >>
>> > >> >>
>> > >> >> Gord
>> > >> >>
>> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
>> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> > >> >>
>> > >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or 
>> > >> >> >b1:b9
>> > >> >> >because the data is same. MY boss want not to delete repeated 
>> > >> >> >data but merge
>> > >> >> >and centre the same data.
>> > >> >> >
>> > >> >> >"Gord Dibben" wrote:
>> > >> >> >
>> > >> >> >> Hardeep
>> > >> >> >>
>> > >> >> >> Run this macro based upon contents of Column A
>> > >> >> >>
>> > >> >> >> Sub Clear_Stuff()
>> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
>> > >> >> >>     Dim LastRow As Long
>> > >> >> >>     Dim x As Long
>> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> > >> >> >>     Application.ScreenUpdating = False
>> > >> >> >>
>> > >> >> >>     For x = LastRow To 2 Step -1
>> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
>> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
>> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
>> > >> >> >>                 End If
>> > >> >> >>             End If
>> > >> >> >>         End If
>> > >> >> >>     Next x
>> > >> >> >>     Application.ScreenUpdating = True
>> > >> >> >> End Sub
>> > >> >> >>
>> > >> >> >> Does not merge and center, but clears contents of duplicated 
>> > >> >> >> row.
>> > >> >> >>
>> > >> >> >> You don't want merge and center because only the data in the 
>> > >> >> >> top left cell of
>> > >> >> >> selected cells will remain.
>> > >> >> >>
>> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would 
>> > >> >> >> remain.
>> > >> >> >>
>> > >> >> >>
>> > >> >> >> Gord Dibben  MS Excel MVP
>> > >> >> >>
>> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> > >> >> >>
>> > >> >> >> >         A       B         C             D
>> > >> >       HR     500     200          500
>> > >> >       HR     500     200          500
>> > >> >       WP     1000  500           1000
>> > >> >       DP       200    50              200
>> > >> >       DP       200    50             200
>> > >> >> >> >I want like this
>> > >> >> >> >        A        B          C              D
>> > >> >     HR     500       200          500
>> > >> >
>> > >> >      WP      500    200           500
>> > >> >      DP       200     50             200
>> > >> >
>> > >> >> >> >
>> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. 
>> > >> >> >> >The data in
>> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same.
>> > >> >> >> >Or you may say that The rows are same it may goes to 3 or 4 
>> > >> >> >> >or 7  rows are
>> > >> >> >> >same data. And i want to merge and  centre the same data in 
>> > >> >> >> >the rows.
>> > >> >> >> >Merge and Centre option is on the toolbar.
>> > >> >> >> >
>> > >> >> >> >Thanks
>> > >> >> >> >
>> > >> >> >> >Hardeep Kanwar
>> > >> >> >> >
>> > >> >> >> >"ShaneDevenshire" wrote:
>> > >> >> >> >
>> > >> >> >> >
>> > >> >> >> >> Hi,
>> > >> >> >> >>
>> > >> >> >> >> I think we need more detail - what do you want to merge and 
>> > >> >> >> >> center, multiple
>> > >> >> >> >> rows, multiple columns, multiple blocks of cells?  You will 
>> > >> >> >> >> loose the data in
>> > >> >> >> >> all but one of the merged cells, Excel does not merge the 
>> > >> >> >> >> data.
>> > >> >> >> >>
>> > >> >> >> >> What determines if a range has repeat data - does the data 
>> > >> >> >> >> in two cells
>> > >> >> >> >> above each other need to be the same, does all the data on 
>> > >> >> >> >> two adjacent rows
>> > >> >> >> >> need to be the same, do the rows with duplicate data need 
>> > >> >> >> >> to be adjacent?
>> > >> >> >> >>
>> > >> >> >> >> -- 
>> > >> >> >> >> Thanks,
>> > >> >> >> >> Shane Devenshire
>> > >> >> >> >>
>> > >> >> >> >>
>> > >> >> >> >> "Hardeep_kanwar" wrote:
>> > >> >> >> >>
>> > >> >> >> >> > Hi!
>> > >> >> >> >> >
>> > >> >> >> >> > How to find repeated data and then merge and centre.
>> > >> >> >> >> >
>> > >> >> >> >> > My data is from A to M.Range from A1 to M4500
>> > >> >> >> >> >
>> > >> >> >> >> > Any Function Or Macro.
>> > >> >> >> >> >
>> > >> >> >> >> > Thanks
>> > >> >> >> >> > Hardeep
>> > >> >> >>
>> > >> >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> >
>> >
> 


0
sandymann2 (1054)
5/26/2008 10:00:01 PM
Thanks All of you

It works perfectly 

Regards
Hardeep kanwar

"Sandy Mann" wrote:

> I am not saying that my programming skills are any better than Gord's but 
> try this simple minded Macro:
> 
> Option Explicit
> Sub MergeItandCentre()
>     Dim LastRow As Long
>     Dim LineRow As Long
>     Dim cCell As Long
>     Dim Col As Long
>     Dim N As Integer
> 
>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> 
>     Application.DisplayAlerts = False
>     Application.ScreenUpdating = False
> 
>     For LineRow = 1 To LastRow - 1
>         For Col = 1 To 11
>             If Cells(LineRow, Col).Value = _
>                 Cells(LineRow + 1, Col).Value Then
>                     N = N + 1
>             End If
>         Next Col
> 
>         If N = 11 Then
>             For cCell = 1 To 11
>                 With Range(Cells(LineRow, cCell), Cells(LineRow + 1, cCell))
>                     .MergeCells = True
>                     .VerticalAlignment = xlCenter
>                 End With
>             Next cCell
>         End If
> 
>         N = 0
> 
>     Next LineRow
> 
>     Application.DisplayAlerts = True
>     Application.ScreenUpdating = True
> 
> 
> End Sub
> 
> 
> 
> -- 
> HTH
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message 
> news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
> > Dear sir
> > Pls ignore the previous file
> > Find the new  attached file (http://www.savefile.com/files/1574440)
> > This is the sample of my Master sheet
> >
> > Thanks
> > Hardeep kanwar
> >
> > "Hardeep_kanwar" wrote:
> >
> >> Dear sir
> >>
> >> Pls find the attached file in this link
> >> http://www.savefile.com/files/1574364
> >>
> >> Regards
> >> Hardeep kanwar
> >>
> >> "Gord Dibben" wrote:
> >>
> >> > You can post the workbook at one of the hosting sites below.
> >> >
> >> > http://www.freefilehosting.net/
> >> > http://savefile.com/
> >> >
> >> > When you have uploaded to that site, post the URL so we could download 
> >> > should we
> >> > choose.
> >> >
> >> >
> >> > Gord
> >> >
> >> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
> >> > <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >
> >> > >Dear Sir,
> >> > >Thanks for Code, but unfortunatally it work on some cells not in all 
> >> > >cells
> >> > >I want to give the attach file.
> >> > >So. pls provide me your e-mail id
> >> > >
> >> > >Thanks once more for the code
> >> > >
> >> > >Hardeep kanwar
> >> > >
> >> > >"Gord Dibben" wrote:
> >> > >
> >> > >> So what??
> >> > >>
> >> > >> Getting a little testy are we?
> >> > >>
> >> > >> Sub Clear_Stuff()
> >> > >> 'Based on code by Sandy Mann July 1st, 2007
> >> > >>     Dim LastRow As Long
> >> > >>     Dim x As Long
> >> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> > >>     Application.ScreenUpdating = False
> >> > >>     Application.DisplayAlerts = False
> >> > >>
> >> > >>     For x = LastRow To 2 Step -1
> >> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> > >>             If Cells(x - 1, 1).Value <> "" Then
> >> > >>                 If Cells(x - 1, 1).Value <> "" Then
> >> > >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
> >> > >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
> >> > >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
> >> > >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
> >> > >>
> >> > >>                 End If
> >> > >>             End If
> >> > >>         End If
> >> > >>     Next x
> >> > >>     Application.ScreenUpdating = True
> >> > >>     Application.DisplayAlerts = True
> >> > >> End Sub
> >> > >>
> >> > >> I hope your boss likes his merged and centered cells which will 
> >> > >> cause him no end
> >> > >> of grief in the future if tries to copy, paste, sort, filter or many 
> >> > >> other
> >> > >> operations that won't work with merged cells.
> >> > >>
> >> > >>
> >> > >> Gord
> >> > >>
> >> > >>
> >> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> >> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> > >>
> >> > >> >So what, it does't matter for me If A2 is deleted Because the data 
> >> > >> >is same.If
> >> > >> >the data is not same then this is the problem for me. My question 
> >> > >> >is not to
> >> > >> >deleted repeated data but merge and centre the data.
> >> > >> >
> >> > >> >
> >> > >> >Just see my example
> >> > >> >
> >> > >> >
> >> > >> >Thanks
> >> > >> >hardeep
> >> > >> >
> >> > >> >
> >> > >> >"Gord Dibben" wrote:
> >> > >> >
> >> > >> >> I guess I just don't understand what your boss means by "merge 
> >> > >> >> and center the
> >> > >> >> same data"
> >> > >> >>
> >> > >> >> Merge and center A1:A2 will delete the data in A2.
> >> > >> >>
> >> > >> >>
> >> > >> >> Gord
> >> > >> >>
> >> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> >> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> > >> >>
> >> > >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 or 
> >> > >> >> >b1:b9
> >> > >> >> >because the data is same. MY boss want not to delete repeated 
> >> > >> >> >data but merge
> >> > >> >> >and centre the same data.
> >> > >> >> >
> >> > >> >> >"Gord Dibben" wrote:
> >> > >> >> >
> >> > >> >> >> Hardeep
> >> > >> >> >>
> >> > >> >> >> Run this macro based upon contents of Column A
> >> > >> >> >>
> >> > >> >> >> Sub Clear_Stuff()
> >> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
> >> > >> >> >>     Dim LastRow As Long
> >> > >> >> >>     Dim x As Long
> >> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> > >> >> >>     Application.ScreenUpdating = False
> >> > >> >> >>
> >> > >> >> >>     For x = LastRow To 2 Step -1
> >> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
> >> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
> >> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
> >> > >> >> >>                 End If
> >> > >> >> >>             End If
> >> > >> >> >>         End If
> >> > >> >> >>     Next x
> >> > >> >> >>     Application.ScreenUpdating = True
> >> > >> >> >> End Sub
> >> > >> >> >>
> >> > >> >> >> Does not merge and center, but clears contents of duplicated 
> >> > >> >> >> row.
> >> > >> >> >>
> >> > >> >> >> You don't want merge and center because only the data in the 
> >> > >> >> >> top left cell of
> >> > >> >> >> selected cells will remain.
> >> > >> >> >>
> >> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would 
> >> > >> >> >> remain.
> >> > >> >> >>
> >> > >> >> >>
> >> > >> >> >> Gord Dibben  MS Excel MVP
> >> > >> >> >>
> >> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> >> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> > >> >> >>
> >> > >> >> >> >         A       B         C             D
> >> > >> >       HR     500     200          500
> >> > >> >       HR     500     200          500
> >> > >> >       WP     1000  500           1000
> >> > >> >       DP       200    50              200
> >> > >> >       DP       200    50             200
> >> > >> >> >> >I want like this
> >> > >> >> >> >        A        B          C              D
> >> > >> >     HR     500       200          500
> >> > >> >
> >> > >> >      WP      500    200           500
> >> > >> >      DP       200     50             200
> >> > >> >
> >> > >> >> >> >
> >> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. 
> >> > >> >> >> >The data in
> >> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same.
> >> > >> >> >> >Or you may say that The rows are same it may goes to 3 or 4 
> >> > >> >> >> >or 7  rows are
> >> > >> >> >> >same data. And i want to merge and  centre the same data in 
> >> > >> >> >> >the rows.
> >> > >> >> >> >Merge and Centre option is on the toolbar.
> >> > >> >> >> >
> >> > >> >> >> >Thanks
> >> > >> >> >> >
> >> > >> >> >> >Hardeep Kanwar
> >> > >> >> >> >
> >> > >> >> >> >"ShaneDevenshire" wrote:
> >> > >> >> >> >
> >> > >> >> >> >
> >> > >> >> >> >> Hi,
> >> > >> >> >> >>
> >> > >> >> >> >> I think we need more detail - what do you want to merge and 
> >> > >> >> >> >> center, multiple
> >> > >> >> >> >> rows, multiple columns, multiple blocks of cells?  You will 
> >> > >> >> >> >> loose the data in
> >> > >> >> >> >> all but one of the merged cells, Excel does not merge the 
> >> > >> >> >> >> data.
> >> > >> >> >> >>
> >> > >> >> >> >> What determines if a range has repeat data - does the data 
> >> > >> >> >> >> in two cells
> >> > >> >> >> >> above each other need to be the same, does all the data on 
> >> > >> >> >> >> two adjacent rows
> >> > >> >> >> >> need to be the same, do the rows with duplicate data need 
> >> > >> >> >> >> to be adjacent?
> >> > >> >> >> >>
> >> > >> >> >> >> -- 
> >> > >> >> >> >> Thanks,
> >> > >> >> >> >> Shane Devenshire
> >> > >> >> >> >>
> >> > >> >> >> >>
> >> > >> >> >> >> "Hardeep_kanwar" wrote:
> >> > >> >> >> >>
> >> > >> >> >> >> > Hi!
> >> > >> >> >> >> >
> >> > >> >> >> >> > How to find repeated data and then merge and centre.
> >> > >> >> >> >> >
> >> > >> >> >> >> > My data is from A to M.Range from A1 to M4500
> >> > >> >> >> >> >
> >> > >> >> >> >> > Any Function Or Macro.
> >> > >> >> >> >> >
> >> > >> >> >> >> > Thanks
> >> > >> >> >> >> > Hardeep
> >> > >> >> >>
> >> > >> >> >>
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> >
> >> >
> > 
> 
> 
> 
0
5/27/2008 3:45:04 PM
You are verywelcome, glad that it worked for you

-- 

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message 
news:81C177D8-C2B0-4D1E-A5BC-1FB4B0FCACE9@microsoft.com...
> Thanks All of you
>
> It works perfectly
>
> Regards
> Hardeep kanwar
>
> "Sandy Mann" wrote:
>
>> I am not saying that my programming skills are any better than Gord's but
>> try this simple minded Macro:
>>
>> Option Explicit
>> Sub MergeItandCentre()
>>     Dim LastRow As Long
>>     Dim LineRow As Long
>>     Dim cCell As Long
>>     Dim Col As Long
>>     Dim N As Integer
>>
>>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>>
>>     Application.DisplayAlerts = False
>>     Application.ScreenUpdating = False
>>
>>     For LineRow = 1 To LastRow - 1
>>         For Col = 1 To 11
>>             If Cells(LineRow, Col).Value = _
>>                 Cells(LineRow + 1, Col).Value Then
>>                     N = N + 1
>>             End If
>>         Next Col
>>
>>         If N = 11 Then
>>             For cCell = 1 To 11
>>                 With Range(Cells(LineRow, cCell), Cells(LineRow + 1, 
>> cCell))
>>                     .MergeCells = True
>>                     .VerticalAlignment = xlCenter
>>                 End With
>>             Next cCell
>>         End If
>>
>>         N = 0
>>
>>     Next LineRow
>>
>>     Application.DisplayAlerts = True
>>     Application.ScreenUpdating = True
>>
>>
>> End Sub
>>
>>
>>
>> -- 
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in 
>> message
>> news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
>> > Dear sir
>> > Pls ignore the previous file
>> > Find the new  attached file (http://www.savefile.com/files/1574440)
>> > This is the sample of my Master sheet
>> >
>> > Thanks
>> > Hardeep kanwar
>> >
>> > "Hardeep_kanwar" wrote:
>> >
>> >> Dear sir
>> >>
>> >> Pls find the attached file in this link
>> >> http://www.savefile.com/files/1574364
>> >>
>> >> Regards
>> >> Hardeep kanwar
>> >>
>> >> "Gord Dibben" wrote:
>> >>
>> >> > You can post the workbook at one of the hosting sites below.
>> >> >
>> >> > http://www.freefilehosting.net/
>> >> > http://savefile.com/
>> >> >
>> >> > When you have uploaded to that site, post the URL so we could 
>> >> > download
>> >> > should we
>> >> > choose.
>> >> >
>> >> >
>> >> > Gord
>> >> >
>> >> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
>> >> > <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >
>> >> > >Dear Sir,
>> >> > >Thanks for Code, but unfortunatally it work on some cells not in 
>> >> > >all
>> >> > >cells
>> >> > >I want to give the attach file.
>> >> > >So. pls provide me your e-mail id
>> >> > >
>> >> > >Thanks once more for the code
>> >> > >
>> >> > >Hardeep kanwar
>> >> > >
>> >> > >"Gord Dibben" wrote:
>> >> > >
>> >> > >> So what??
>> >> > >>
>> >> > >> Getting a little testy are we?
>> >> > >>
>> >> > >> Sub Clear_Stuff()
>> >> > >> 'Based on code by Sandy Mann July 1st, 2007
>> >> > >>     Dim LastRow As Long
>> >> > >>     Dim x As Long
>> >> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >> > >>     Application.ScreenUpdating = False
>> >> > >>     Application.DisplayAlerts = False
>> >> > >>
>> >> > >>     For x = LastRow To 2 Step -1
>> >> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> >> > >>             If Cells(x - 1, 1).Value <> "" Then
>> >> > >>                 If Cells(x - 1, 1).Value <> "" Then
>> >> > >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
>> >> > >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
>> >> > >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
>> >> > >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
>> >> > >>
>> >> > >>                 End If
>> >> > >>             End If
>> >> > >>         End If
>> >> > >>     Next x
>> >> > >>     Application.ScreenUpdating = True
>> >> > >>     Application.DisplayAlerts = True
>> >> > >> End Sub
>> >> > >>
>> >> > >> I hope your boss likes his merged and centered cells which will
>> >> > >> cause him no end
>> >> > >> of grief in the future if tries to copy, paste, sort, filter or 
>> >> > >> many
>> >> > >> other
>> >> > >> operations that won't work with merged cells.
>> >> > >>
>> >> > >>
>> >> > >> Gord
>> >> > >>
>> >> > >>
>> >> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
>> >> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> > >>
>> >> > >> >So what, it does't matter for me If A2 is deleted Because the 
>> >> > >> >data
>> >> > >> >is same.If
>> >> > >> >the data is not same then this is the problem for me. My 
>> >> > >> >question
>> >> > >> >is not to
>> >> > >> >deleted repeated data but merge and centre the data.
>> >> > >> >
>> >> > >> >
>> >> > >> >Just see my example
>> >> > >> >
>> >> > >> >
>> >> > >> >Thanks
>> >> > >> >hardeep
>> >> > >> >
>> >> > >> >
>> >> > >> >"Gord Dibben" wrote:
>> >> > >> >
>> >> > >> >> I guess I just don't understand what your boss means by "merge
>> >> > >> >> and center the
>> >> > >> >> same data"
>> >> > >> >>
>> >> > >> >> Merge and center A1:A2 will delete the data in A2.
>> >> > >> >>
>> >> > >> >>
>> >> > >> >> Gord
>> >> > >> >>
>> >> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
>> >> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> > >> >>
>> >> > >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 
>> >> > >> >> >or
>> >> > >> >> >b1:b9
>> >> > >> >> >because the data is same. MY boss want not to delete repeated
>> >> > >> >> >data but merge
>> >> > >> >> >and centre the same data.
>> >> > >> >> >
>> >> > >> >> >"Gord Dibben" wrote:
>> >> > >> >> >
>> >> > >> >> >> Hardeep
>> >> > >> >> >>
>> >> > >> >> >> Run this macro based upon contents of Column A
>> >> > >> >> >>
>> >> > >> >> >> Sub Clear_Stuff()
>> >> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
>> >> > >> >> >>     Dim LastRow As Long
>> >> > >> >> >>     Dim x As Long
>> >> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >> > >> >> >>     Application.ScreenUpdating = False
>> >> > >> >> >>
>> >> > >> >> >>     For x = LastRow To 2 Step -1
>> >> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> >> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
>> >> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
>> >> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
>> >> > >> >> >>                 End If
>> >> > >> >> >>             End If
>> >> > >> >> >>         End If
>> >> > >> >> >>     Next x
>> >> > >> >> >>     Application.ScreenUpdating = True
>> >> > >> >> >> End Sub
>> >> > >> >> >>
>> >> > >> >> >> Does not merge and center, but clears contents of 
>> >> > >> >> >> duplicated
>> >> > >> >> >> row.
>> >> > >> >> >>
>> >> > >> >> >> You don't want merge and center because only the data in 
>> >> > >> >> >> the
>> >> > >> >> >> top left cell of
>> >> > >> >> >> selected cells will remain.
>> >> > >> >> >>
>> >> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would
>> >> > >> >> >> remain.
>> >> > >> >> >>
>> >> > >> >> >>
>> >> > >> >> >> Gord Dibben  MS Excel MVP
>> >> > >> >> >>
>> >> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> >> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> > >> >> >>
>> >> > >> >> >> >         A       B         C             D
>> >> > >> >       HR     500     200          500
>> >> > >> >       HR     500     200          500
>> >> > >> >       WP     1000  500           1000
>> >> > >> >       DP       200    50              200
>> >> > >> >       DP       200    50             200
>> >> > >> >> >> >I want like this
>> >> > >> >> >> >        A        B          C              D
>> >> > >> >     HR     500       200          500
>> >> > >> >
>> >> > >> >      WP      500    200           500
>> >> > >> >      DP       200     50             200
>> >> > >> >
>> >> > >> >> >> >
>> >> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and 
>> >> > >> >> >> >Centre.
>> >> > >> >> >> >The data in
>> >> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same.
>> >> > >> >> >> >Or you may say that The rows are same it may goes to 3 or 
>> >> > >> >> >> >4
>> >> > >> >> >> >or 7  rows are
>> >> > >> >> >> >same data. And i want to merge and  centre the same data 
>> >> > >> >> >> >in
>> >> > >> >> >> >the rows.
>> >> > >> >> >> >Merge and Centre option is on the toolbar.
>> >> > >> >> >> >
>> >> > >> >> >> >Thanks
>> >> > >> >> >> >
>> >> > >> >> >> >Hardeep Kanwar
>> >> > >> >> >> >
>> >> > >> >> >> >"ShaneDevenshire" wrote:
>> >> > >> >> >> >
>> >> > >> >> >> >
>> >> > >> >> >> >> Hi,
>> >> > >> >> >> >>
>> >> > >> >> >> >> I think we need more detail - what do you want to merge 
>> >> > >> >> >> >> and
>> >> > >> >> >> >> center, multiple
>> >> > >> >> >> >> rows, multiple columns, multiple blocks of cells?  You 
>> >> > >> >> >> >> will
>> >> > >> >> >> >> loose the data in
>> >> > >> >> >> >> all but one of the merged cells, Excel does not merge 
>> >> > >> >> >> >> the
>> >> > >> >> >> >> data.
>> >> > >> >> >> >>
>> >> > >> >> >> >> What determines if a range has repeat data - does the 
>> >> > >> >> >> >> data
>> >> > >> >> >> >> in two cells
>> >> > >> >> >> >> above each other need to be the same, does all the data 
>> >> > >> >> >> >> on
>> >> > >> >> >> >> two adjacent rows
>> >> > >> >> >> >> need to be the same, do the rows with duplicate data 
>> >> > >> >> >> >> need
>> >> > >> >> >> >> to be adjacent?
>> >> > >> >> >> >>
>> >> > >> >> >> >> -- 
>> >> > >> >> >> >> Thanks,
>> >> > >> >> >> >> Shane Devenshire
>> >> > >> >> >> >>
>> >> > >> >> >> >>
>> >> > >> >> >> >> "Hardeep_kanwar" wrote:
>> >> > >> >> >> >>
>> >> > >> >> >> >> > Hi!
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > How to find repeated data and then merge and centre.
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > My data is from A to M.Range from A1 to M4500
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > Any Function Or Macro.
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > Thanks
>> >> > >> >> >> >> > Hardeep
>> >> > >> >> >>
>> >> > >> >> >>
>> >> > >> >>
>> >> > >> >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >
>>
>>
>> 


0
sandymann2 (1054)
5/27/2008 8:47:13 PM
Dear Sir,

Sorry to disturb u again

 http://www.savefile.com/files/1578364

Kindly find the attached file. I had received this original data from my 
boss now I will tell u what I want.

 

Firstly, Merge and centre the Yellow color rows.

Secondly, copy the data Zone wise For example Cz001 in another workbook 
sheet1, Cz002 in sheet 2, CZ003 in sheet3, Nz001 in Another workbook 
sheet1.Nz002 in sheet2,Ez001 in another sheet1 and so on.

 

I hope this time you will help me

 

Regards,

Hardeep kanwar 



"Sandy Mann" wrote:

> You are verywelcome, glad that it worked for you
> 
> -- 
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message 
> news:81C177D8-C2B0-4D1E-A5BC-1FB4B0FCACE9@microsoft.com...
> > Thanks All of you
> >
> > It works perfectly
> >
> > Regards
> > Hardeep kanwar
> >
> > "Sandy Mann" wrote:
> >
> >> I am not saying that my programming skills are any better than Gord's but
> >> try this simple minded Macro:
> >>
> >> Option Explicit
> >> Sub MergeItandCentre()
> >>     Dim LastRow As Long
> >>     Dim LineRow As Long
> >>     Dim cCell As Long
> >>     Dim Col As Long
> >>     Dim N As Integer
> >>
> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >>
> >>     Application.DisplayAlerts = False
> >>     Application.ScreenUpdating = False
> >>
> >>     For LineRow = 1 To LastRow - 1
> >>         For Col = 1 To 11
> >>             If Cells(LineRow, Col).Value = _
> >>                 Cells(LineRow + 1, Col).Value Then
> >>                     N = N + 1
> >>             End If
> >>         Next Col
> >>
> >>         If N = 11 Then
> >>             For cCell = 1 To 11
> >>                 With Range(Cells(LineRow, cCell), Cells(LineRow + 1, 
> >> cCell))
> >>                     .MergeCells = True
> >>                     .VerticalAlignment = xlCenter
> >>                 End With
> >>             Next cCell
> >>         End If
> >>
> >>         N = 0
> >>
> >>     Next LineRow
> >>
> >>     Application.DisplayAlerts = True
> >>     Application.ScreenUpdating = True
> >>
> >>
> >> End Sub
> >>
> >>
> >>
> >> -- 
> >> HTH
> >>
> >> Sandy
> >> In Perth, the ancient capital of Scotland
> >> and the crowning place of kings
> >>
> >> sandymann2@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in 
> >> message
> >> news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
> >> > Dear sir
> >> > Pls ignore the previous file
> >> > Find the new  attached file (http://www.savefile.com/files/1574440)
> >> > This is the sample of my Master sheet
> >> >
> >> > Thanks
> >> > Hardeep kanwar
> >> >
> >> > "Hardeep_kanwar" wrote:
> >> >
> >> >> Dear sir
> >> >>
> >> >> Pls find the attached file in this link
> >> >> http://www.savefile.com/files/1574364
> >> >>
> >> >> Regards
> >> >> Hardeep kanwar
> >> >>
> >> >> "Gord Dibben" wrote:
> >> >>
> >> >> > You can post the workbook at one of the hosting sites below.
> >> >> >
> >> >> > http://www.freefilehosting.net/
> >> >> > http://savefile.com/
> >> >> >
> >> >> > When you have uploaded to that site, post the URL so we could 
> >> >> > download
> >> >> > should we
> >> >> > choose.
> >> >> >
> >> >> >
> >> >> > Gord
> >> >> >
> >> >> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
> >> >> > <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> >
> >> >> > >Dear Sir,
> >> >> > >Thanks for Code, but unfortunatally it work on some cells not in 
> >> >> > >all
> >> >> > >cells
> >> >> > >I want to give the attach file.
> >> >> > >So. pls provide me your e-mail id
> >> >> > >
> >> >> > >Thanks once more for the code
> >> >> > >
> >> >> > >Hardeep kanwar
> >> >> > >
> >> >> > >"Gord Dibben" wrote:
> >> >> > >
> >> >> > >> So what??
> >> >> > >>
> >> >> > >> Getting a little testy are we?
> >> >> > >>
> >> >> > >> Sub Clear_Stuff()
> >> >> > >> 'Based on code by Sandy Mann July 1st, 2007
> >> >> > >>     Dim LastRow As Long
> >> >> > >>     Dim x As Long
> >> >> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >> > >>     Application.ScreenUpdating = False
> >> >> > >>     Application.DisplayAlerts = False
> >> >> > >>
> >> >> > >>     For x = LastRow To 2 Step -1
> >> >> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> >> > >>             If Cells(x - 1, 1).Value <> "" Then
> >> >> > >>                 If Cells(x - 1, 1).Value <> "" Then
> >> >> > >>                     Cells(x - 1, 1).Resize(2).MergeCells = True
> >> >> > >>                     Cells(x - 1, 2).Resize(2).MergeCells = True
> >> >> > >>                     Cells(x - 1, 3).Resize(2).MergeCells = True
> >> >> > >>                     Cells(x - 1, 4).Resize(2).MergeCells = True
> >> >> > >>
> >> >> > >>                 End If
> >> >> > >>             End If
> >> >> > >>         End If
> >> >> > >>     Next x
> >> >> > >>     Application.ScreenUpdating = True
> >> >> > >>     Application.DisplayAlerts = True
> >> >> > >> End Sub
> >> >> > >>
> >> >> > >> I hope your boss likes his merged and centered cells which will
> >> >> > >> cause him no end
> >> >> > >> of grief in the future if tries to copy, paste, sort, filter or 
> >> >> > >> many
> >> >> > >> other
> >> >> > >> operations that won't work with merged cells.
> >> >> > >>
> >> >> > >>
> >> >> > >> Gord
> >> >> > >>
> >> >> > >>
> >> >> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> >> >> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> > >>
> >> >> > >> >So what, it does't matter for me If A2 is deleted Because the 
> >> >> > >> >data
> >> >> > >> >is same.If
> >> >> > >> >the data is not same then this is the problem for me. My 
> >> >> > >> >question
> >> >> > >> >is not to
> >> >> > >> >deleted repeated data but merge and centre the data.
> >> >> > >> >
> >> >> > >> >
> >> >> > >> >Just see my example
> >> >> > >> >
> >> >> > >> >
> >> >> > >> >Thanks
> >> >> > >> >hardeep
> >> >> > >> >
> >> >> > >> >
> >> >> > >> >"Gord Dibben" wrote:
> >> >> > >> >
> >> >> > >> >> I guess I just don't understand what your boss means by "merge
> >> >> > >> >> and center the
> >> >> > >> >> same data"
> >> >> > >> >>
> >> >> > >> >> Merge and center A1:A2 will delete the data in A2.
> >> >> > >> >>
> >> >> > >> >>
> >> >> > >> >> Gord
> >> >> > >> >>
> >> >> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> >> >> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> > >> >>
> >> >> > >> >> >Yes, exactly i want this but i want to merge and centre a1:a2 
> >> >> > >> >> >or
> >> >> > >> >> >b1:b9
> >> >> > >> >> >because the data is same. MY boss want not to delete repeated
> >> >> > >> >> >data but merge
> >> >> > >> >> >and centre the same data.
> >> >> > >> >> >
> >> >> > >> >> >"Gord Dibben" wrote:
> >> >> > >> >> >
> >> >> > >> >> >> Hardeep
> >> >> > >> >> >>
> >> >> > >> >> >> Run this macro based upon contents of Column A
> >> >> > >> >> >>
> >> >> > >> >> >> Sub Clear_Stuff()
> >> >> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
> >> >> > >> >> >>     Dim LastRow As Long
> >> >> > >> >> >>     Dim x As Long
> >> >> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >> > >> >> >>     Application.ScreenUpdating = False
> >> >> > >> >> >>
> >> >> > >> >> >>     For x = LastRow To 2 Step -1
> >> >> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> >> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
> >> >> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
> >> >> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
> >> >> > >> >> >>                 End If
> >> >> > >> >> >>             End If
> >> >> > >> >> >>         End If
> >> >> > >> >> >>     Next x
> >> >> > >> >> >>     Application.ScreenUpdating = True
> >> >> > >> >> >> End Sub
> >> >> > >> >> >>
> >> >> > >> >> >> Does not merge and center, but clears contents of 
> >> >> > >> >> >> duplicated
> >> >> > >> >> >> row.
> >> >> > >> >> >>
> >> >> > >> >> >> You don't want merge and center because only the data in 
> >> >> > >> >> >> the
> >> >> > >> >> >> top left cell of
> >> >> > >> >> >> selected cells will remain.
> >> >> > >> >> >>
> >> >> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data would
> >> >> > >> >> >> remain.
> >> >> > >> >> >>
> >> >> > >> >> >>
> >> >> > >> >> >> Gord Dibben  MS Excel MVP
> >> >> > >> >> >>
> >> >> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
> >> >> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> > >> >> >>
> >> >> > >> >> >> >         A       B         C             D
> >> >> > >> >       HR     500     200          500
> >> >> > >> >       HR     500     200          500
> >> >> > >> >       WP     1000  500           1000
> >> >> > >> >       DP       200    50              200
> >> >> > >> >       DP       200    50             200
> >> >> > >> >> >> >I want like this
> >> >> > >> >> >> >        A        B          C              D
> >> >> > >> >     HR     500       200          500
> >> >> > >> >
> >> >> > >> >      WP      500    200           500
> >> >> > >> >      DP       200     50             200
> >> >> > >> >
> >> >> > >> >> >> >
> >> >> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and 
> >> >> > >> >> >> >Centre.
> >> >> > >> >> >> >The data in
> >> >> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same.
> >> >> > >> >> >> >Or you may say that The rows are same it may goes to 3 or 
> >> >> > >> >> >> >4
> >> >> > >> >> >> >or 7  rows are
> >> >> > >> >> >> >same data. And i want to merge and  centre the same data 
> >> >> > >> >> >> >in
> >> >> > >> >> >> >the rows.
> >> >> > >> >> >> >Merge and Centre option is on the toolbar.
> >> >> > >> >> >> >
> >> >> > >> >> >> >Thanks
> >> >> > >> >> >> >
> >> >> > >> >> >> >Hardeep Kanwar
> >> >> > >> >> >> >
> >> >> > >> >> >> >"ShaneDevenshire" wrote:
> >> >> > >> >> >> >
> >> >> > >> >> >> >
> >> >> > >> >> >> >> Hi,
> >> >> > >> >> >> >>
> >> >> > >> >> >> >> I think we need more detail - what do you want to merge 
> >> >> > >> >> >> >> and
> >> >> > >> >> >> >> center, multiple
> >> >> > >> >> >> >> rows, multiple columns, multiple blocks of cells?  You 
> >> >> > >> >> >> >> will
> >> >> > >> >> >> >> loose the data in
> >> >> > >> >> >> >> all but one of the merged cells, Excel does not merge 
> >> >> > >> >> >> >> the
> >> >> > >> >> >> >> data.
> >> >> > >> >> >> >>
0
5/28/2008 4:51:03 PM
> Firstly, Merge and centre the Yellow color rows.
>

I don't see what your obsession is with merging rows  After merging all rows
with CZ001 and copying to another Workbook you will have:

221000800011 Biraja Star T V 12568.5775 7075 5493 400 BLUE DEACTIVE

which is the data in the top row, in a cell 48 rows high.  What is the
benefit in merging?

> Secondly, copy the data Zone wise For example Cz001 in another workbook
> sheet1, Cz002 in sheet 2, CZ003 in sheet3, Nz001 in Another workbook
> sheet1.Nz002 in sheet2,Ez001 in another sheet1 and so on.

Why use other Workbooks?  Why not just other Worksheets in the same
Workbook?

Finally why not ask Aidan Heritage who wrote the existing Macros in the
Workbook?


-- 
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message
news:0508E659-09C5-461B-88DC-F83C863FCA98@microsoft.com...
> Dear Sir,
>
> Sorry to disturb u again
>
> http://www.savefile.com/files/1578364
>
> Kindly find the attached file. I had received this original data from my
> boss now I will tell u what I want.
>
>
>
> Firstly, Merge and centre the Yellow color rows.
>
> Secondly, copy the data Zone wise For example Cz001 in another workbook
> sheet1, Cz002 in sheet 2, CZ003 in sheet3, Nz001 in Another workbook
> sheet1.Nz002 in sheet2,Ez001 in another sheet1 and so on.
>
>
>
> I hope this time you will help me
>
>
>
> Regards,
>
> Hardeep kanwar
>
>
>
> "Sandy Mann" wrote:
>
>> You are verywelcome, glad that it worked for you
>>
>> -- 
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in
>> message
>> news:81C177D8-C2B0-4D1E-A5BC-1FB4B0FCACE9@microsoft.com...
>> > Thanks All of you
>> >
>> > It works perfectly
>> >
>> > Regards
>> > Hardeep kanwar
>> >
>> > "Sandy Mann" wrote:
>> >
>> >> I am not saying that my programming skills are any better than Gord's
>> >> but
>> >> try this simple minded Macro:
>> >>
>> >> Option Explicit
>> >> Sub MergeItandCentre()
>> >>     Dim LastRow As Long
>> >>     Dim LineRow As Long
>> >>     Dim cCell As Long
>> >>     Dim Col As Long
>> >>     Dim N As Integer
>> >>
>> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >>
>> >>     Application.DisplayAlerts = False
>> >>     Application.ScreenUpdating = False
>> >>
>> >>     For LineRow = 1 To LastRow - 1
>> >>         For Col = 1 To 11
>> >>             If Cells(LineRow, Col).Value = _
>> >>                 Cells(LineRow + 1, Col).Value Then
>> >>                     N = N + 1
>> >>             End If
>> >>         Next Col
>> >>
>> >>         If N = 11 Then
>> >>             For cCell = 1 To 11
>> >>                 With Range(Cells(LineRow, cCell), Cells(LineRow + 1,
>> >> cCell))
>> >>                     .MergeCells = True
>> >>                     .VerticalAlignment = xlCenter
>> >>                 End With
>> >>             Next cCell
>> >>         End If
>> >>
>> >>         N = 0
>> >>
>> >>     Next LineRow
>> >>
>> >>     Application.DisplayAlerts = True
>> >>     Application.ScreenUpdating = True
>> >>
>> >>
>> >> End Sub
>> >>
>> >>
>> >>
>> >> -- 
>> >> HTH
>> >>
>> >> Sandy
>> >> In Perth, the ancient capital of Scotland
>> >> and the crowning place of kings
>> >>
>> >> sandymann2@mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in
>> >> message
>> >> news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
>> >> > Dear sir
>> >> > Pls ignore the previous file
>> >> > Find the new  attached file (http://www.savefile.com/files/1574440)
>> >> > This is the sample of my Master sheet
>> >> >
>> >> > Thanks
>> >> > Hardeep kanwar
>> >> >
>> >> > "Hardeep_kanwar" wrote:
>> >> >
>> >> >> Dear sir
>> >> >>
>> >> >> Pls find the attached file in this link
>> >> >> http://www.savefile.com/files/1574364
>> >> >>
>> >> >> Regards
>> >> >> Hardeep kanwar
>> >> >>
>> >> >> "Gord Dibben" wrote:
>> >> >>
>> >> >> > You can post the workbook at one of the hosting sites below.
>> >> >> >
>> >> >> > http://www.freefilehosting.net/
>> >> >> > http://savefile.com/
>> >> >> >
>> >> >> > When you have uploaded to that site, post the URL so we could
>> >> >> > download
>> >> >> > should we
>> >> >> > choose.
>> >> >> >
>> >> >> >
>> >> >> > Gord
>> >> >> >
>> >> >> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
>> >> >> > <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >> >
>> >> >> > >Dear Sir,
>> >> >> > >Thanks for Code, but unfortunatally it work on some cells not in
>> >> >> > >all
>> >> >> > >cells
>> >> >> > >I want to give the attach file.
>> >> >> > >So. pls provide me your e-mail id
>> >> >> > >
>> >> >> > >Thanks once more for the code
>> >> >> > >
>> >> >> > >Hardeep kanwar
>> >> >> > >
>> >> >> > >"Gord Dibben" wrote:
>> >> >> > >
>> >> >> > >> So what??
>> >> >> > >>
>> >> >> > >> Getting a little testy are we?
>> >> >> > >>
>> >> >> > >> Sub Clear_Stuff()
>> >> >> > >> 'Based on code by Sandy Mann July 1st, 2007
>> >> >> > >>     Dim LastRow As Long
>> >> >> > >>     Dim x As Long
>> >> >> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >> >> > >>     Application.ScreenUpdating = False
>> >> >> > >>     Application.DisplayAlerts = False
>> >> >> > >>
>> >> >> > >>     For x = LastRow To 2 Step -1
>> >> >> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
>> >> >> > >>             If Cells(x - 1, 1).Value <> "" Then
>> >> >> > >>                 If Cells(x - 1, 1).Value <> "" Then
>> >> >> > >>                     Cells(x - 1, 1).Resize(2).MergeCells =
>> >> >> > >> True
>> >> >> > >>                     Cells(x - 1, 2).Resize(2).MergeCells =
>> >> >> > >> True
>> >> >> > >>                     Cells(x - 1, 3).Resize(2).MergeCells =
>> >> >> > >> True
>> >> >> > >>                     Cells(x - 1, 4).Resize(2).MergeCells =
>> >> >> > >> True
>> >> >> > >>
>> >> >> > >>                 End If
>> >> >> > >>             End If
>> >> >> > >>         End If
>> >> >> > >>     Next x
>> >> >> > >>     Application.ScreenUpdating = True
>> >> >> > >>     Application.DisplayAlerts = True
>> >> >> > >> End Sub
>> >> >> > >>
>> >> >> > >> I hope your boss likes his merged and centered cells which
>> >> >> > >> will
>> >> >> > >> cause him no end
>> >> >> > >> of grief in the future if tries to copy, paste, sort, filter
>> >> >> > >> or
>> >> >> > >> many
>> >> >> > >> other
>> >> >> > >> operations that won't work with merged cells.
>> >> >> > >>
>> >> >> > >>
>> >> >> > >> Gord
>> >> >> > >>
>> >> >> > >>
>> >> >> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
>> >> >> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >> > >>
>> >> >> > >> >So what, it does't matter for me If A2 is deleted Because the
>> >> >> > >> >data
>> >> >> > >> >is same.If
>> >> >> > >> >the data is not same then this is the problem for me. My
>> >> >> > >> >question
>> >> >> > >> >is not to
>> >> >> > >> >deleted repeated data but merge and centre the data.
>> >> >> > >> >
>> >> >> > >> >
>> >> >> > >> >Just see my example
>> >> >> > >> >
>> >> >> > >> >
>> >> >> > >> >Thanks
>> >> >> > >> >hardeep
>> >> >> > >> >
>> >> >> > >> >
>> >> >> > >> >"Gord Dibben" wrote:
>> >> >> > >> >
>> >> >> > >> >> I guess I just don't understand what your boss means by
>> >> >> > >> >> "merge
>> >> >> > >> >> and center the
>> >> >> > >> >> same data"
>> >> >> > >> >>
>> >> >> > >> >> Merge and center A1:A2 will delete the data in A2.
>> >> >> > >> >>
>> >> >> > >> >>
>> >> >> > >> >> Gord
>> >> >> > >> >>
>> >> >> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
>> >> >> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >> > >> >>
>> >> >> > >> >> >Yes, exactly i want this but i want to merge and centre
>> >> >> > >> >> >a1:a2
>> >> >> > >> >> >or
>> >> >> > >> >> >b1:b9
>> >> >> > >> >> >because the data is same. MY boss want not to delete
>> >> >> > >> >> >repeated
>> >> >> > >> >> >data but merge
>> >> >> > >> >> >and centre the same data.
>> >> >> > >> >> >
>> >> >> > >> >> >"Gord Dibben" wrote:
>> >> >> > >> >> >
>> >> >> > >> >> >> Hardeep
>> >> >> > >> >> >>
>> >> >> > >> >> >> Run this macro based upon contents of Column A
>> >> >> > >> >> >>
>> >> >> > >> >> >> Sub Clear_Stuff()
>> >> >> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
>> >> >> > >> >> >>     Dim LastRow As Long
>> >> >> > >> >> >>     Dim x As Long
>> >> >> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
>> >> >> > >> >> >>     Application.ScreenUpdating = False
>> >> >> > >> >> >>
>> >> >> > >> >> >>     For x = LastRow To 2 Step -1
>> >> >> > >> >> >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value
>> >> >> > >> >> >> Then
>> >> >> > >> >> >>             If Cells(x - 1, 1).Value <> "" Then
>> >> >> > >> >> >>                 If Cells(x - 1, 1).Value <> "" Then
>> >> >> > >> >> >>                     Cells(x, 1).EntireRow.ClearContents
>> >> >> > >> >> >>                 End If
>> >> >> > >> >> >>             End If
>> >> >> > >> >> >>         End If
>> >> >> > >> >> >>     Next x
>> >> >> > >> >> >>     Application.ScreenUpdating = True
>> >> >> > >> >> >> End Sub
>> >> >> > >> >> >>
>> >> >> > >> >> >> Does not merge and center, but clears contents of
>> >> >> > >> >> >> duplicated
>> >> >> > >> >> >> row.
>> >> >> > >> >> >>
>> >> >> > >> >> >> You don't want merge and center because only the data in
>> >> >> > >> >> >> the
>> >> >> > >> >> >> top left cell of
>> >> >> > >> >> >> selected cells will remain.
>> >> >> > >> >> >>
>> >> >> > >> >> >> i.e.  if you selected A2:D2 and merged, only A2 data
>> >> >> > >> >> >> would
>> >> >> > >> >> >> remain.
>> >> >> > >> >> >>
>> >> >> > >> >> >>
>> >> >> > >> >> >> Gord Dibben  MS Excel MVP
>> >> >> > >> >> >>
>> >> >> > >> >> >> On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
>> >> >> > >> >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
>> >> >> > >> >> >>
>> >> >> > >> >> >> >         A       B         C             D
>> >> >> > >> >       HR     500     200          500
>> >> >> > >> >       HR     500     200          500
>> >> >> > >> >       WP     1000  500           1000
>> >> >> > >> >       DP       200    50              200
>> >> >> > >> >       DP       200    50             200
>> >> >> > >> >> >> >I want like this
>> >> >> > >> >> >> >        A        B          C              D
>> >> >> > >> >     HR     500       200          500
>> >> >> > >> >
>> >> >> > >> >      WP      500    200           500
>> >> >> > >> >      DP       200     50             200
>> >> >> > >> >
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and
>> >> >> > >> >> >> >Centre.
>> >> >> > >> >> >> >The data in
>> >> >> > >> >> >> >A1-A2,B1-B2,C1-C2,D1-D2 is same.
>> >> >> > >> >> >> >Or you may say that The rows are same it may goes to 3
>> >> >> > >> >> >> >or
>> >> >> > >> >> >> >4
>> >> >> > >> >> >> >or 7  rows are
>> >> >> > >> >> >> >same data. And i want to merge and  centre the same
>> >> >> > >> >> >> >data
>> >> >> > >> >> >> >in
>> >> >> > >> >> >> >the rows.
>> >> >> > >> >> >> >Merge and Centre option is on the toolbar.
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >Thanks
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >Hardeep Kanwar
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >"ShaneDevenshire" wrote:
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >
>> >> >> > >> >> >> >> Hi,
>> >> >> > >> >> >> >>
>> >> >> > >> >> >> >> I think we need more detail - what do you want to
>> >> >> > >> >> >> >> merge
>> >> >> > >> >> >> >> and
>> >> >> > >> >> >> >> center, multiple
>> >> >> > >> >> >> >> rows, multiple columns, multiple blocks of cells?
>> >> >> > >> >> >> >> You
>> >> >> > >> >> >> >> will
>> >> >> > >> >> >> >> loose the data in
>> >> >> > >> >> >> >> all but one of the merged cells, Excel does not merge
>> >> >> > >> >> >> >> the
>> >> >> > >> >> >> >> data.
>> >> >> > >> >> >> >>
>



0
sandymann2 (1054)
5/28/2008 8:53:32 PM
Dear sir,
First i don't want to merge and centre the A:A column as i Mentioned you 
earlier that only yellow colour Column i.e (B:B, D:D,H:H,I:I,J:J,K:K,L:L,M:M)

Secondly the Macors which is given by  Mr.Aidan Heritage it works fine but 
it will copy in the same workbook due to our Client requriement they want 
individual workbook for every Zone.
The code is given by u i.e MERGEITCENTRE it work in some cells for example 
if one cell is repeated 4 times it will merge only 2 or 3 times. I think it 
will merge only 2 cells instead of 3 or 4 cells. In B1:B4 is same after run 
the code it will merge the B1:B2

I hope you understand my problem 

Thanks in advance

Hardeep kanwar


"Sandy Mann" wrote:

> > Firstly, Merge and centre the Yellow color rows.
> >
> 
> I don't see what your obsession is with merging rows  After merging all rows
> with CZ001 and copying to another Workbook you will have:
> 
> 221000800011 Biraja Star T V 12568.5775 7075 5493 400 BLUE DEACTIVE
> 
> which is the data in the top row, in a cell 48 rows high.  What is the
> benefit in merging?
> 
> > Secondly, copy the data Zone wise For example Cz001 in another workbook
> > sheet1, Cz002 in sheet 2, CZ003 in sheet3, Nz001 in Another workbook
> > sheet1.Nz002 in sheet2,Ez001 in another sheet1 and so on.
> 
> Why use other Workbooks?  Why not just other Worksheets in the same
> Workbook?
> 
> Finally why not ask Aidan Heritage who wrote the existing Macros in the
> Workbook?
> 
> 
> -- 
> Regards,
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in message
> news:0508E659-09C5-461B-88DC-F83C863FCA98@microsoft.com...
> > Dear Sir,
> >
> > Sorry to disturb u again
> >
> > http://www.savefile.com/files/1578364
> >
> > Kindly find the attached file. I had received this original data from my
> > boss now I will tell u what I want.
> >
> >
> >
> > Firstly, Merge and centre the Yellow color rows.
> >
> > Secondly, copy the data Zone wise For example Cz001 in another workbook
> > sheet1, Cz002 in sheet 2, CZ003 in sheet3, Nz001 in Another workbook
> > sheet1.Nz002 in sheet2,Ez001 in another sheet1 and so on.
> >
> >
> >
> > I hope this time you will help me
> >
> >
> >
> > Regards,
> >
> > Hardeep kanwar
> >
> >
> >
> > "Sandy Mann" wrote:
> >
> >> You are verywelcome, glad that it worked for you
> >>
> >> -- 
> >>
> >> Sandy
> >> In Perth, the ancient capital of Scotland
> >> and the crowning place of kings
> >>
> >> sandymann2@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in
> >> message
> >> news:81C177D8-C2B0-4D1E-A5BC-1FB4B0FCACE9@microsoft.com...
> >> > Thanks All of you
> >> >
> >> > It works perfectly
> >> >
> >> > Regards
> >> > Hardeep kanwar
> >> >
> >> > "Sandy Mann" wrote:
> >> >
> >> >> I am not saying that my programming skills are any better than Gord's
> >> >> but
> >> >> try this simple minded Macro:
> >> >>
> >> >> Option Explicit
> >> >> Sub MergeItandCentre()
> >> >>     Dim LastRow As Long
> >> >>     Dim LineRow As Long
> >> >>     Dim cCell As Long
> >> >>     Dim Col As Long
> >> >>     Dim N As Integer
> >> >>
> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >>
> >> >>     Application.DisplayAlerts = False
> >> >>     Application.ScreenUpdating = False
> >> >>
> >> >>     For LineRow = 1 To LastRow - 1
> >> >>         For Col = 1 To 11
> >> >>             If Cells(LineRow, Col).Value = _
> >> >>                 Cells(LineRow + 1, Col).Value Then
> >> >>                     N = N + 1
> >> >>             End If
> >> >>         Next Col
> >> >>
> >> >>         If N = 11 Then
> >> >>             For cCell = 1 To 11
> >> >>                 With Range(Cells(LineRow, cCell), Cells(LineRow + 1,
> >> >> cCell))
> >> >>                     .MergeCells = True
> >> >>                     .VerticalAlignment = xlCenter
> >> >>                 End With
> >> >>             Next cCell
> >> >>         End If
> >> >>
> >> >>         N = 0
> >> >>
> >> >>     Next LineRow
> >> >>
> >> >>     Application.DisplayAlerts = True
> >> >>     Application.ScreenUpdating = True
> >> >>
> >> >>
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >> -- 
> >> >> HTH
> >> >>
> >> >> Sandy
> >> >> In Perth, the ancient capital of Scotland
> >> >> and the crowning place of kings
> >> >>
> >> >> sandymann2@mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "Hardeep_kanwar" <Hardeepkanwar@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:6C7FB605-0C26-4CB3-B83C-701CCE69762F@microsoft.com...
> >> >> > Dear sir
> >> >> > Pls ignore the previous file
> >> >> > Find the new  attached file (http://www.savefile.com/files/1574440)
> >> >> > This is the sample of my Master sheet
> >> >> >
> >> >> > Thanks
> >> >> > Hardeep kanwar
> >> >> >
> >> >> > "Hardeep_kanwar" wrote:
> >> >> >
> >> >> >> Dear sir
> >> >> >>
> >> >> >> Pls find the attached file in this link
> >> >> >> http://www.savefile.com/files/1574364
> >> >> >>
> >> >> >> Regards
> >> >> >> Hardeep kanwar
> >> >> >>
> >> >> >> "Gord Dibben" wrote:
> >> >> >>
> >> >> >> > You can post the workbook at one of the hosting sites below.
> >> >> >> >
> >> >> >> > http://www.freefilehosting.net/
> >> >> >> > http://savefile.com/
> >> >> >> >
> >> >> >> > When you have uploaded to that site, post the URL so we could
> >> >> >> > download
> >> >> >> > should we
> >> >> >> > choose.
> >> >> >> >
> >> >> >> >
> >> >> >> > Gord
> >> >> >> >
> >> >> >> > On Sun, 25 May 2008 01:12:01 -0700, Hardeep_kanwar
> >> >> >> > <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> >> >
> >> >> >> > >Dear Sir,
> >> >> >> > >Thanks for Code, but unfortunatally it work on some cells not in
> >> >> >> > >all
> >> >> >> > >cells
> >> >> >> > >I want to give the attach file.
> >> >> >> > >So. pls provide me your e-mail id
> >> >> >> > >
> >> >> >> > >Thanks once more for the code
> >> >> >> > >
> >> >> >> > >Hardeep kanwar
> >> >> >> > >
> >> >> >> > >"Gord Dibben" wrote:
> >> >> >> > >
> >> >> >> > >> So what??
> >> >> >> > >>
> >> >> >> > >> Getting a little testy are we?
> >> >> >> > >>
> >> >> >> > >> Sub Clear_Stuff()
> >> >> >> > >> 'Based on code by Sandy Mann July 1st, 2007
> >> >> >> > >>     Dim LastRow As Long
> >> >> >> > >>     Dim x As Long
> >> >> >> > >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
> >> >> >> > >>     Application.ScreenUpdating = False
> >> >> >> > >>     Application.DisplayAlerts = False
> >> >> >> > >>
> >> >> >> > >>     For x = LastRow To 2 Step -1
> >> >> >> > >>         If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
> >> >> >> > >>             If Cells(x - 1, 1).Value <> "" Then
> >> >> >> > >>                 If Cells(x - 1, 1).Value <> "" Then
> >> >> >> > >>                     Cells(x - 1, 1).Resize(2).MergeCells =
> >> >> >> > >> True
> >> >> >> > >>                     Cells(x - 1, 2).Resize(2).MergeCells =
> >> >> >> > >> True
> >> >> >> > >>                     Cells(x - 1, 3).Resize(2).MergeCells =
> >> >> >> > >> True
> >> >> >> > >>                     Cells(x - 1, 4).Resize(2).MergeCells =
> >> >> >> > >> True
> >> >> >> > >>
> >> >> >> > >>                 End If
> >> >> >> > >>             End If
> >> >> >> > >>         End If
> >> >> >> > >>     Next x
> >> >> >> > >>     Application.ScreenUpdating = True
> >> >> >> > >>     Application.DisplayAlerts = True
> >> >> >> > >> End Sub
> >> >> >> > >>
> >> >> >> > >> I hope your boss likes his merged and centered cells which
> >> >> >> > >> will
> >> >> >> > >> cause him no end
> >> >> >> > >> of grief in the future if tries to copy, paste, sort, filter
> >> >> >> > >> or
> >> >> >> > >> many
> >> >> >> > >> other
> >> >> >> > >> operations that won't work with merged cells.
> >> >> >> > >>
> >> >> >> > >>
> >> >> >> > >> Gord
> >> >> >> > >>
> >> >> >> > >>
> >> >> >> > >> On Thu, 22 May 2008 23:29:00 -0700, Hardeep_kanwar
> >> >> >> > >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> >> > >>
> >> >> >> > >> >So what, it does't matter for me If A2 is deleted Because the
> >> >> >> > >> >data
> >> >> >> > >> >is same.If
> >> >> >> > >> >the data is not same then this is the problem for me. My
> >> >> >> > >> >question
> >> >> >> > >> >is not to
> >> >> >> > >> >deleted repeated data but merge and centre the data.
> >> >> >> > >> >
> >> >> >> > >> >
> >> >> >> > >> >Just see my example
> >> >> >> > >> >
> >> >> >> > >> >
> >> >> >> > >> >Thanks
> >> >> >> > >> >hardeep
> >> >> >> > >> >
> >> >> >> > >> >
> >> >> >> > >> >"Gord Dibben" wrote:
> >> >> >> > >> >
> >> >> >> > >> >> I guess I just don't understand what your boss means by
> >> >> >> > >> >> "merge
> >> >> >> > >> >> and center the
> >> >> >> > >> >> same data"
> >> >> >> > >> >>
> >> >> >> > >> >> Merge and center A1:A2 will delete the data in A2.
> >> >> >> > >> >>
> >> >> >> > >> >>
> >> >> >> > >> >> Gord
> >> >> >> > >> >>
> >> >> >> > >> >> On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
> >> >> >> > >> >> <Hardeepkanwar@discussions.microsoft.com> wrote:
> >> >> >> > >> >>
> >> >> >> > >> >> >Yes, exactly i want this but i want to merge and centre
> >> >> >> > >> >> >a1:a2
> >> >> >> > >> >> >or
> >> >> >> > >> >> >b1:b9
> >> >> >> > >> >> >because the data is same. MY boss want not to delete
> >> >> >> > >> >> >repeated
> >> >> >> > >> >> >data but merge
> >> >> >> > >> >> >and centre the same data.
> >> >> >> > >> >> >
> >> >> >> > >> >> >"Gord Dibben" wrote:
> >> >> >> > >> >> >
> >> >> >> > >> >> >> Hardeep
> >> >> >> > >> >> >>
> >> >> >> > >> >> >> Run this macro based upon contents of Column A
> >> >> >> > >> >> >>
> >> >> >> > >> >> >> Sub Clear_Stuff()
> >> >> >> > >> >> >> 'Based on code by Sandy Mann July 1st, 2007
> >> >> >> > >> >> >>     Dim LastRow As Long
> >> >> >> > >> >> >>     Dim x As Long
> >> >> >> > >> >> >>     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
0
5/30/2008 4:54:04 AM
Reply:

Similar Artilces:

How can I find career change resumes templates?
I like to review some nice "Professional Career Change Resume Templates" for the job market. If someone could tell me where to go on the inter-net, it will be well appreciated. Thanks! You could start at www.google.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:705F99E5-1326-4E81-B98F-A7337C5C9AEA@microsoft.com... :I like to review some nice "Professional Career Change Resume Templates" for : the job market. If someone could tell me where to go on the inter-net, it : will be well appreciated. Thanks! Templates are...

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

Mail Merge and Labels
OK. I've read the posts but don't seem to find an answer for my problem. I have created a Publisher file and set it up to run an Avery 5163 label. I get a single lable to work with and that's fine. I want use the mail merge feature to merge in data from my Access Database. While I'm creating the label, I don't use the mail merge feature (haven't even yet added the data source). I just want to see how it looks and feels. So I put a dummy name (Jane Doe) on the label. When I publish to Adobe .pdf (which is how I'm going to distribute the labels), I get a f...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

Design for future merging
I've created a small but somewhat complex relational database that uses autonumber fields in higher level tables as primary keys to link with foreign keys in lower level tables. At this point I have up to 5 levels of tables. I'd like to copy this database for use at 4 other remote sites, with the ultimate intention of merging all 5 back together after a few months of data entry. I've read quite a bit about how to merge databases that weren't originally designed with future merges in mind, and this ends up being quite complex with so many levels and autonumber-dependent t...

can't find normal.dot
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I need to delete my normal.dot file and I can't find it anywhere. I've had to do this before and I was able to find it in the templates folder, but I guess an update or two has changed that. Can anyone tell me where to find it? Spotlight says that it doesn't exist. But I have to be able to delete it. Is it called something different now? If you're using Spotlight & searching for "Normal.dot" it's no wonder it isn't being found - that isn't the correct name :-) it's ...

Finding maximum value
Hello, I want to find the maximum value in a column, but I want to ignore the negative and positive part of the value. Basically, the maximum difference from zero. Example: the maximum value I am looking for will be -0.467. -0.467 0.345 -0.253 0.411 Thanks Ruan One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE)) In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>, "Ruan" <ruan@aegismed.com> wrote: > Hello, > > I want to find the maximum value in a column, but I want to ignore the > negati...

Outlook 2000 Merging to Email
When I use Word and Outlook 2000 to merge to email. It will merge but it will not put the messages in my outbox so that I can send them. I have tried everything. The contacts are there the merged letter is there, I just can't get it to actually send the emails. Help! Thanks ...

How do I find the inside page in the 3-panel brochure.
I am trying to make a 3-panel brochure and all that seems to be available is the side shown in the brochure options. When I print, it prints both sides yet I can see no way to access the inside part. Thanks. View, status bar... this will allow you to change page views, you will see the page tabs at the bottom of the screen. I am not sure I know what you are asking. Are you saying you are printing the brochure sight unseen? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "springmtn" <springmtn@discussions.microsoft.co...

Find a multiple photos on a page template for Publisher?
Possible to find a template for Publisher to print a page full of multiple photos without recreating the page each time I print. Why not save your page you have created as a template? File, "save as", files of type, scroll down to Publisher template. -- Mary Sauer http://msauer.mvps.org/ "srfaith" <srfaith@discussions.microsoft.com> wrote in message news:157E95AB-1159-4889-B196-61E147E6E240@microsoft.com... > Possible to find a template for Publisher to print a page full of multiple > photos without recreating the page each time I print. ...

Finding Desktop region in MFC
I am working on a commercial application, and recently discovered a bug I have been asked to fix (they threw it back in my lap). Run the application on a 2-monitor system, such as a laptop in a dock with an external monitor. Drag the main window to the second (non-taskbar) window and quit. Now move the laptop to a different dock with the second menu on the other side (or without a second monitor at all) and launch the app. Oops. The app continues to want to go on the side it was last put, even though there is now no monitor there. (Yes, my dock in the office and at home have the second ...

Performance counter for Outlook Finds?
What server performance counters (if any) are there for monitoring the number of "Find" requests that Outlook clients make? -GT ...

finding calendar and data info
I had to move my primary hard drive into another computer. It is now running as a slavein the new computer. I'm unable however, to find my calendar and contacts from Outlook 2003 when I open the program on the new computer. If I attempt to open the program from the F drive (the drive assigned in the new computer to the hard drive that was moved from another computer) it tells me the program is not installed. If I open Outlook 2003 from the C drive of the new computer, of course, it doesn't find the old calendar and contacts. I would appreciate any and all assistance in findin...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Using sdk to find out if an Account already exists
Hi, Want to use sdk to see if an Account already exists , all i have is its name. I've looked at Retrieve method but it requires the Guid. Any equivalent call to Retrieve that just takes name? Or a way to get the Guid from the name? Sample code would be great if available. Thanks for all help. John Hi John, The method you are looking for is RetrieveMultiple as only the guid search gives the certainty to retrieve only one record. All explanations are in the sdk but as the sdk sample is nearly the exact code you want, here it is : // Set up the CRM Service. CrmService service =3D new ...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Doesn't find files w/spaces in name
There are a lot of folders and Excel spreadsheets on our shared drive that I need to open. Most of the folders and file names are named like: get reports\reports sep 2004.xls When I try to open one, Excel doesn't find it and I think it's because there are spaces in the folder names and/or file names. Is there a setting in Excel that will help me get around this? This was working until I had a glitch in Excel and the technician fixed the glitch and this problem appeared. Any help would be appreciated. I found an answer and so far it works. "C:/program files/microsoft off...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Help with Document Merging
I am not even sure if this is possible....so I will give you some background to help you understand what I need... Thanks in advance for any help you can provide... I work for a staffing firm and we use various questionnaires to collect information about our candidates. Each questionnaire is targeted to a specific type of candidate, i.e. a questionnaire for accounting candidates versus a questionnaire for an HR candidate or an IT candidate. Each questionnaire has a set of questions that is specific for that job type, but each questionnaire also has a set of general question...

Can I merge a list of email addresses from exel into outlook?
w/out having to enter each on separately, can I merge the email addresses into a group? Hi MDuda See this page http://www.rondebruin.nl/mail/importcontacts.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "MDuda" <MDuda@discussions.microsoft.com> wrote in message news:B58DEF30-7EEA-4244-BBED-C1851156DF6C@microsoft.com... > w/out having to enter each on separately, can I merge the email addresses > into a group? ...

Mail Merge 03-12-10
I have a log that has multiple lines with the same individual that I am trying to merge into one page per individual in word, I have to do a more advanced mail merge. Assuming you are using Word for the mailmerge and Excel for the source see these sites for help. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 08:33:01 -0800, Brooke <Brooke@discussions.microsoft.com> wrote: >I have a...

find
How can I setup a macro or use a function to find a column who's first cell is called "Addresses" then move the whole column to sheet 2. Is it possible? Puzzled J, If there is only one cell with the word "Addresses" in it: Sub MoveAddresses() Cells.Find("Addresses").EntireColumn.Cut Worksheets("Sheet 2").Range("IV1").End(xlToLeft)(1,2) End Sub This will put the column into the column with the first free cell in row 1 of Sheet 2. HTH, Bernie MS Excel MVP "J Bates" <newss@pittam.plus.com> wrote in message news:4507...

Email Mail Merge
I formatted a MS Word document using the web layout view however when it emails the format is all over the place please advise Advise posting with enough clarity and detail for someone to understand what you are attempting to say in an appropriate group. You did neither. -- Russ Valentine [MVP-Outlook] "Zee" <xdsouza@sterlinghomesgroup.com> wrote in message news:4C5ACEF9-838C-4141-A1E1-C0CF4E1394AE@microsoft.com... >I formatted a MS Word document using the web layout view however when it > emails the format is all over the place please advise I have created a mail...

Merging Protected word documents.
I have 3 Protected word documents that I would like to have in 1 document. I cannot get them to keep their individual spacing ad margins when I import the documents. How can I do this? ...

Finding/deleting macros...
Thanks to anyone who helped me with my finding/deleting link question... Now in a different workbook.. when i open it, it prompts whether t enable or disable macros. When i go to the macros list though, it blank, none listed. How could I find where thesee phantom macros ar and get rid of them? FYI we always choose Disable macros. This is work book that was built a long time ago, probably has about 50+ tab of which we only currently use about 15. Thanks for any help! Chri -- Message posted from http://www.ExcelForum.com Hi you also have to remove the modules: - open the VBA editor (hi...