Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching Text and Deleting Rows (Excel 2003 SP2)

    Each month I get spreadsheets that were downloaded from our corporate mainframe. These spreadsheets have text or numbers in 4 columns and have up to 1,500 rows. My dilemma is; in only one column, I have to search for matching text from the cell above, if it matches, then I have to delete that whole row. I am doing this by hand now and it takes me about 15 minutes per worksheet. Is there a way to do this automatically for all rows? Would I use functions or do I have to get somebody to write some VBA code?

    Thank you in advance for help. It will save me hours a month if I get an answer.
    T-man

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    Revised by author. to correct the code statement for deleting the upper row from the spreadsheet.

    Hi T-Man
    The following code should do what you want.
    <pre>Sub RemoveDupes()
    '
    Dim oSht As Worksheet
    Dim ROI As Long ' Row of Interest

    Set oSht = ActiveSheet
    With oSht
    ROI = .Range(COI & 65536).End(xlUp).Row
    While ROI > 1
    If UCase(.Range(COI & ROI)) = UCase(.Range(COI & ROI - 1)) Then
    ' The following row removes the lower of the two rows.
    .Range(ROI & ":" & ROI).Delete
    ' To delete the upper row use the next row
    ' .Range(ROI - 1 & ":" & ROI - 1).Delete
    End If
    ROI = ROI - 1
    Wend
    End With
    Set oSht = Nothing
    End Sub
    </pre>



    You will need to set the COI constant to the column you're interested in. Also, you will need to have the worksheet of interest active.

    I assume that you did not intend allowing duplicate numerical values. If this assumption is incorrect, please advise and I'll send an update.

    The current code is Case Insensitive. To make it Case Sensitive, change the "If" statement:
    From: If UCase(.Range(COI & ROI)) = UCase(.Range(COI & ROI - 1)) Then
    To: If .Range(COI & ROI) = .Range(COI & ROI - 1) Then
    Regards
    Don

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    Don;

    Thank you for your reply. I am not much of a VBA programmer so I don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    Here is your workbook with a slightly modified version of Don's macro.
    If you want to use the macro in several workbooks, put it in Personal.xls. See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> for more info.
    In Excel, you can assign a macro to a Ctrl+character or Ctrl+Shift+character combination:
    Select Tools | Macro | Macros...
    Select RemoveDupes in the list of macros.
    Click Options...
    Click in the little box for the shortcut key.
    Press a key or Shift+a key.
    Click OK.
    Note: several Ctrl+character combinations have "fixed" meanings, e.g. Ctrl+O = Open, Ctrl+S = Save. It's best not to use those, to avoid confusion.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    Hi Hans
    I am inclined to use While/Wend and For/Next loops interchangeably, because I cannot determine a benefit of using one over the other. Can you provide some illumination?
    TIA
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    I tend to use For ... Next if I need a loop with fixed increments, and variations on Do ... Loop otherwise (While ... Wend is perfectly valid, but slightly old-fashioned. Do ... Loop is more flexible)

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    Dank
    Regards
    Don

  8. #8
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching Text and Deleting Rows (Excel 2003 SP2)

    You are awesom Hans and Don Thx much. I actually am reading about the VBA process, but you guys made it much easier.

Posting Permissions

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