Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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. |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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. |
#22
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find repeat and than merge and centre
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the difference between merge and centre and merge across | Excel Discussion (Misc queries) | |||
Match, Copy, Merge, Delete and Repeat... MVP's this one's for you. | Excel Discussion (Misc queries) | |||
undue merge and centre | New Users to Excel | |||
How do I get "centre across selection" to centre properly? | Excel Discussion (Misc queries) | |||
What is the Keyboard Shortcut of Merge and Centre Tool Command | Excel Discussion (Misc queries) |