Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to organize some data that was dumped out of a proprietary system into something I can use. In column A, I want to put a 'group by' number.

It dumps this long, spanning row out that separates the different duplicates. Whenever it hits a spanning row, I want it to increment the counter, and delete the spanning row.

The before data is this (I manually keyed A3, A4, A6, A7, A8, A10, A11):

Before

I'd like the after data to look like this:

After

Any ideas how to accomplish this?

Final solution made possible by comment from below:

Public Sub CleanMeImFilthy()

    Dim intCounter As Integer
    Dim i As Integer

    i = 1

    For intCounter = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
       If Trim(LCase(Left(Cells(intCounter, 1).Value, 14))) = "duplicate key:" Then
         Rows(intCounter).Delete
         i = i + 1
       Else
         Rows.Cells(intCounter, 1) = i
       End If
    Next

End Sub
share|improve this question

migrated from stackoverflow.com May 26 '11 at 21:23

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

Try the following piece of VBA:

Public Sub CleanMeImFilthy

  Dim intCounter as Integer

  For intCounter = ActiveSheet.UsedRange.Rows.Count to 1 Step -1
    If Trim(Lcase(Left(Cells(intCounter,1).Value,14))) = "duplicate key:" Then
      Rows(intCounter).Delete
    End If
  Next

End Sub 

All the code does is:

  1. Starts at the bottom of column A - where 'bottom' is the last row that has been used.
  2. Checks to see if the lower case version of the first 14 characters of text in the current cell, which has been stripped of spaces, equals 'duplicate key:'
  3. On the assumption that all rows you need to remove contain this, it then deletes the row

I started from the bottom and worked up as it's cleaner due to not deleting a row, which means the row below you is now you.

As you posted this on Stack Overflow originally, I'm fairly confident you can add this into Excel. If not:

  1. Copy the code from here
  2. Press ALT+F11 to go into the VB Editor
  3. Click Insert | Module on the Menu Bar
  4. Click Edit | Paste (CTRL+V)
  5. Close the VB Editor
  6. On the sheet that contains your dirty data, press ALT+F8 to get a list of available macros
  7. Select 'CleanMeImFilthy' and choose Run.
share|improve this answer
    
this is perfect! I only had to add a counter and an else statement to get the numbers the way I wanted. See final solution: Public Sub CleanMeImFilthy() Dim intCounter As Integer Dim i As Integer i = 1 For intCounter = ActiveSheet.UsedRange.Rows.Count To 1 Step -1 If Trim(LCase(Left(Cells(intCounter, 1).Value, 14))) = "duplicate key:" Then Rows(intCounter).Delete i = i + 1 Else Rows.Cells(intCounter, 1) = i End If Next End Sub –  Alex K May 26 '11 at 22:32
    
Excellent, glad it helped! –  wigf1 May 27 '11 at 0:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.