Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    5

    Cool Unanswered: Search/Blank and Merge Cell Macro

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Blank/Merge Cells

    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

  3. #3
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    5
    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

  4. #4
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    5
    typo correction:

    it should be www.mrexcel.com

    Best Regards,
    Ricky

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •