Search to blank and merge macro help needed

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Search to blank and merge macro help needed

  1. #1
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search to blank and merge macro help needed

     
    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.



    Best Regards,
    Ricky

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    You only clear it if the cell above is the same ? for example, you won't clear A5 or A6 even they appear above on the list ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    Assuming your list is sorted, try this (not neat but will work):

    Code:
    Sub macro()
    Range("A100").Select
    Selection.End(xlUp).Select
    counter = 0
    Test1 = ActiveCell.Text
    Test2 = ActiveCell.Offset(-1, 0).Text
    Test3 = ActiveCell.Row
    Do Until counter = Test3
    If Test1 = Test2 Then
    Selection.ClearContents
    End If
    ActiveCell.Offset(-1, 0).Select
    counter = counter + 1
    Test1 = ActiveCell.Text
    On Error GoTo Finish
    Test2 = ActiveCell.Offset(-1, 0).Text
    Loop
    
    Finish:
    MsgBox ("Done")
    End Sub
    There are three kinds of people - those that can count and those that can't.

  4. #4
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    Hi Juan Pablo González,

    Yes, correct

    Hi tactps,

    Thanks, will try it out

  5. #5
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    Hi tactps,

    I have tried yur scripts, works great but I still have 3 questions :P

    1) Your script will not work if the column A is not sorted, corect ?
    (sorry, I have not tried that out yet)

    2) I need to set the range ? Can it somehow be 'auto' (meaning no need to set range)

    3) How about the merge part ? Based on my column example, I need to merge A2, A3 and A4 together as one cell.

    Thanks again.

    Best Regards,
    Ricky

  6. #6
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    BTW, how am i able ti attach a sample file ? Any idea ?

    Best Regards,
    Ricky

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed


    Your post:
    1) Your script will not work if the column A is not sorted, corect ?
    (sorry, I have not tried that out yet)

    2) I need to set the range ? Can it somehow be 'auto' (meaning no need to set range)

    3) How about the merge part ? Based on my column example, I need to merge A2, A3 and A4 together as one cell.
    Re:
    1) - correct. you would need to sort the code in column A
    2) - I have it going to the bottom of column A (assuming that you have less than 100 rows).
    If not, change:
    Range("A100").Select
    to
    Range("A60000").select

    3) I am not sure what you mean by merge. Do you have data in other columns?

    Perhaps you need to post your spreadsheet (details changed to protect the guilty) using Colo's HTML Maker (bottom of screen).

    Then I'm sure we can help you out better.
    There are three kinds of people - those that can count and those that can't.

  8. #8
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    Hi tactps,

    I have created a html sample .....DUH.....now how do I post/show it ?



    Best Regards,
    Ricky

  9. #9
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

    Hi tactps,

    ******** ******************** ************************************************************************>
    Microsoft Excel - sample.xls___Running: xl2000 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    HostnameIP AddressDevice TypeLocation
    2
    AIX_Server_A4.3.2.1AXIHong Kong
    3
    Switch_A5.6.7.8SWITCHSingapore
    4
    Switch_A5.6.8.7SWITCHSingapore
    5
    Switch_A5.6.8.9SWITCHSingapore
    6
    Netware_Server_A1.1.1.1NETWAREJapan
    7
    Router_A6.7.3.4ROUTERUSA
    8
    Router_A6.7.8.9ROUTERUSA
    9
    Router_A6.7.9.8ROUTERUSA
    10
    SUN_Server_A2.2.2.2SUNKorea
    11
    Linux_Server_A3.3.3.3LINUXAustralia
    12
    Router_B9.8.7.6ROUTERThailand
    13
    Router_B9.8.1.2ROUTERThailand
    14
    Router_B9.8.3.4ROUTERThailand
    15
    16
    HostnameIP AddressDevice TypeLocation
    17
    AIX_Server_A4.3.2.1AXIHong Kong
    18
    Switch_A5.6.7.8SWITCHSingapore
    19
    5.6.8.7
    20
    5.6.8.9
    21
    Netware_Server_A1.1.1.1NETWAREJapan
    22
    Router_A6.7.3.4ROUTERUSA
    23
    6.7.8.9
    24
    6.7.9.8
    25
    SUN_Server_A2.2.2.2SUNKorea
    26
    Linux_Server_A3.3.3.3LINUXAustralia
    27
    Router_B9.8.7.6ROUTERThailand
    28
    9.8.1.2
    29
    9.8.3.4
    All Devices

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    OK. I have managed to figure it out how to post the html file.

    Now.....the top portion is the raw file

    and the bottom portion is my preferred final output

    Thanks.

    Best Regards,
    Ricky

  10. #10
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search to blank and merge macro help needed

      
    This should be close to what you want. I'm sure an MVP out there can clean it up a fair bit as it is far longer than it needs to be:

    Code:
    Sub macro()
    Range("A100").Select
    Selection.End(xlUp).Select
    counter = 0
    Test1 = ActiveCell.Text
    Test2 = ActiveCell.Offset(-1, 0).Text
    Test3 = ActiveCell.Row
    Do Until counter = Test3
    If Test1 = Test2 Then
    Selection.ClearContents
    End If
    ActiveCell.Offset(-1, 0).Select
    counter = counter + 1
    Test1 = ActiveCell.Text
    On Error GoTo Step2
    Test2 = ActiveCell.Offset(-1, 0).Text
    Loop
    
    
    Step2:
    Range("A1").Select
    countermerge = 0
    Do While countermerge < 100
    On Error GoTo Finish
    Merge1 = ActiveCell.Text
    Merge2 = ActiveCell.Offset(1, 0).Text
    Merge3 = ActiveCell.Offset(2, 0).Text
    Merge4 = ActiveCell.Address
    Merge5 = ActiveCell.Offset(2, 0).Address
    
    If Merge1 <> "" And Merge2 = "" And Merge3 = "" Then
    Range(Merge4 & ":" & Merge5).Select
        With Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .MergeCells = True
        End With
    If Selection.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex Then
    If Selection.Interior.ColorIndex = xlNone Then
    Selection.EntireRow.Select
    Selection.Interior.ColorIndex = 6
    Else
    ActiveCell.EntireRow.Select
    Selection.Interior.ColorIndex = xlNone
    End If
    End If
    End If
    
    countermerge = countermerge + 1
    ActiveCell.Offset(1, 0).Select
    Loop
    
    Finish:
    MsgBox ("Done")
    End Sub
    There are three kinds of people - those that can count and those that can't.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com