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

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

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

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
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

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
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

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
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
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

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
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
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

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
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
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
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

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
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
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
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

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
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
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
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
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

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
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
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
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
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

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
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
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
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
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

I have to admit that is beyond my skills in VBA.

Maybe someone else will download and fix you up.

Could I ask.......what is the purpose of the "merge and center" when autofilter
could just return unique rows?


Gord


On Mon, 26 May 2008 09:09:00 -0700, Hardeep_kanwar
wrote:

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
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
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
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
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

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


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


"Hardeep_kanwar" wrote in message
...
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
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
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
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
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

Very good Sandy.

Hardeep note: rows 3 and 4 are not merged and centered as per your "I want
this" because C3 and C4 are not the same.

221000400739-1
221000400739-2


Gord


On Mon, 26 May 2008 23:00:01 +0100, "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

Not so good - I downloaded the first file not the OP's corrected download.
I will need to look at it again.

--
HTH

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


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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Very good Sandy.

Hardeep note: rows 3 and 4 are not merged and centered as per your "I
want
this" because C3 and C4 are not the same.

221000400739-1
221000400739-2


Gord


On Mon, 26 May 2008 23:00:01 +0100, "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

This seems to work but I would have thought that hiding the Columns and
diplicate Rows would have been a betetr option because then there would be
no loss of data.

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

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

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Columns("A:A").EntireColumn.Hidden = True
Columns("E:G").EntireColumn.Hidden = True

For LineRow = LastRow To 2 Step -1
For Col = 2 To 13
If (Col = 4 And Col < 8) Then GoTo skip
CellValue1 = Cells(LineRow, Col).Value
CellValue2 = Cells(LineRow + 1, Col).Value
If Col = 3 Then
CellValue1 = Left(CellValue1, 12)
CellValue2 = Left(CellValue2, 12)
End If
If CellValue1 = CellValue2 Then
N = N + 1
End If
skip:
Next Col

If N = 8 Then
For cCell = 1 To 13
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


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


"Sandy Mann" wrote in message
...
Not so good - I downloaded the first file not the OP's corrected download.
I will need to look at it again.

--
HTH

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


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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Very good Sandy.

Hardeep note: rows 3 and 4 are not merged and centered as per your "I
want
this" because C3 and C4 are not the same.

221000400739-1
221000400739-2


Gord


On Mon, 26 May 2008 23:00:01 +0100, "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

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


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


"Hardeep_kanwar" wrote in message
...
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
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
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
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
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

You are verywelcome, glad that it worked for you

--

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


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


"Hardeep_kanwar" wrote in message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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
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
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
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
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

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


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


"Hardeep_kanwar" wrote in message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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
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
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
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
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.

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


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


"Hardeep_kanwar" wrote in message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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
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
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
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
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.

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


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


"Hardeep_kanwar" wrote in message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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


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


"Hardeep_kanwar" wrote in
message
...
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
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
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
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