Hi everyone,

I need your excel macro expertise help. Suppose I have a column of data like this:

A1: Router A
A2: Router B
A3: Router B
A4: Router B
A5: Switch A
A6: Switch B
A7: Switch C
A8: Switch C
A9: Switch C

Now, I need a macro that will first of all, search and blank off A3, A4, A8 and A9 since they are repeating and stops when no more repeats are found. And later part is to merge A2-A4 and A7-A9. Important is the 'search & blank' and 'merge' part is not cell specified. Hope you understand what I am trying to accomplish. Thanks for your attention and time.

Try these two procedures (I've used two because you said these are run at different times)

Sub BlankOutRepeatedCells(ByVal iColumn As Integer, ByVal iTopRow As Long, ByVal iBottomRow As Long)
Dim iRow As Long
Dim i As Long

iRow = iTopRow

Do While iRow <= iBottomRow
Do While Cells(iRow, iColumn) <> ""
i = iRow + 1
Do While Cells(iRow, iColumn) = Cells(i, iColumn) And i <= iBottomRow
Cells(i, iColumn).ClearContents
i = i + 1
Loop
iRow = i
Loop
iRow = iRow + 1
Loop
End Sub

Sub MergeBlankCells(ByVal iColumn As Integer, ByVal iTopRow As Long, ByVal iBottomRow As Long)
Dim iRow As Long
Dim i As Long

iRow = iTopRow

Do While iRow <= iBottomRow
Do While Cells(iRow, iColumn) <> ""
i = iRow + 1
Do While Cells(i, iColumn) = "" And i <= iBottomRow
i = i + 1
Loop
If i > iBottomRow Then Exit Do
With Range(Cells(iRow, iColumn).Address & ":" & Cells(i - 1, iColumn).Address)
.MergeCells = True
.VerticalAlignment = xlCenter
End With
iRow = i
Loop
iRow = iRow + 1
Loop
End Sub

Needless to say iCol is the column index to be Blanked/Merged & iTopRow/iBottomRow are the top and bot row index to be Blanked/Merged, therefore these two routines can be used on any column and row range.

If you specify the last row in the spread sheet then it will cause an error. This can be eliminated but I thought the chances of using 65,536 rows are a little slim !!

Also I would check the column an row index are valid if there is a posibility that they could be out of range (or bottom is above the top row) ?

Hope this is what you want

Hi Mike,

Thanks for your time and attention. I will try it out later. However, fyi, I have posted the same problem in mrexce.com and am following a thread there under my same user name and almost similiar post topic. Maybe you can help me to followup from there because I have added more details of what exactly I want and a sample file.

Thanks

Best Regards,
Ricky

typo correction:

it should be www.mrexcel.com

Best Regards,
Ricky

Blank/Merge Cells

I have had a lool at mrexcel sample and the above routine will work with all colums sorted or not.

If the data id continous from the top (title) row. then I suggest a range name be defined at the heading of each column to be processed and Selection.End(xlDown).Select used to locate the end of the data. tye code below would then assign the variable to use in the above routines


Sub testBlankMerge()
Dim Clmn As Integer
Dim tRow As Long
Dim bRow As Long


With Range("HostName")

Clmn = .Column
tRow = .Row
.Select
Selection.End(xlDown).Select
bRow = ActiveCell.Row
.Select

End With

BlankOutRepeatedCells Clmn, tRow, bRow

MergeBlankCells Clmn, tRow, bRow



With Range("DeviceType")

Clmn = .Column
tRow = .Row
.Select
Selection.End(xlDown).Select
bRow = ActiveCell.Row
.Select

End With

BlankOutRepeatedCells Clmn, tRow, bRow

MergeBlankCells Clmn, tRow, bRow

End Sub

Hope this helps


MTB