2

I am struggling with something that should be fairly straightforward, however, I have read at least 15 methods of doing this and cannot seem to get it to work.

Here is a sample dataset:

9:30:01 584.7
9:30:01 590
9:30:01 595
9:30:02 584.51
9:30:03 584.62
9:30:04 584.44
9:30:05 584.05

I only want one row per second, so of the first 3 rows, only one needs to stay. I don't care if it is the first or the last, but the code I have been using keeps the last, 595 in this case.

The way I am doing it is with a for loop that clears the contents of the row that has the same time as the row below it. I then sort the entire range.

I imagine there is a simpler way to simply delete the extra row from the get go. However, when I use delete on the range, instead of clear, it won't remove all of the duplicate rows.

Here is what I want the data to look like:

9:30:01 595
9:30:02 584.51
9:30:03 584.62
9:30:04 584.44
9:30:05 584.05

I need this to happen for the entire sheet. The time is Column B and the values are column C.

Here is the code I am using,

LastRow = ActiveSheet.UsedRange.row - 1 + _
    ActiveSheet.UsedRange.Rows.Count

For RowNum = 2 To LastRow
    If (Range("B" & RowNum) = Range("B" & RowNum + 1)) Then
    Range("B" & RowNum).EntireRow.Clear
    End If
Next RowNum

Range("A2:C" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
order1:=xlAscending, Header:=xlNo
Improve this question
3
  • 1
    If you loop bottom up then you can delete the row instantly. Share your code and I'll help you out with that. Commented May 23, 2014 at 2:47
  • 1
    @PortlandRunner presents a great solution -- you could also potentially use the Range.RemoveDuplicates method for this (especially since your column structure is so well defined)... Here's an MSDN reference: msdn.microsoft.com/en-us/library/office/… Commented May 23, 2014 at 3:03
  • @Portland Runner I have added the code. Your idea sounds very smart. Commented May 23, 2014 at 3:10

2 Answers 2

Reset to default
10

Don't loop. Use RemoveDuplicates. Way faster than any loop. One line of code.

Sub test()
    ActiveSheet.Range("B:C").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Edit: screenshots

BEFORE

enter image description here

AFTER

enter image description here

Edit: Does not work in Excel 2011 for Mac (go figure).

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This works only when the values in both columns are duplicates. It doesn't delete the rows based only on the fact that 9:30:01 repeats.
@user2926358 I added some screenshots and amended the code to columns B and C instead of A and B
@teylyn Is it possible this doesn't work on a mac? I am running Excel 2011 and no dice. I ran it on my old PC and it worked. Thanks.
You never mentioned Mac. I don't do Mac. Does the Data ribbon have a "Remove Duplicates" icon?
+1 for this solution too. But above comment is +1 for loops, they always work (even on Mac ;-))
|
2

This should do the trick:

Sub jzz()
Dim i As Long
For i = 1 To Cells.SpecialCells(xlLastCell).Row 'loop from row 1 to last row
    If Cells(i, 1) <> vbNullString Then 'check if something is in the cell
        If Cells(i, 1) = Cells(i + 1, 1) Then 'check if cell is the same as next cell
            Cells(i + 1, 1).EntireRow.Delete 'if so; delete
            i = i - 1 'go back one row
        End If
    End If
Next i
End Sub

Another option is to go from the bottom up, like so:

Sub jzz()
Dim i As Long
For i = Cells.SpecialCells(xlLastCell).Row to 1 step -1'loop from last row to row 1
    If Cells(i, 1) <> vbNullString Then 'check if something is in the cell
        If Cells(i, 1) = Cells(i + 1, 1) Then 'check if cell is the same as next cell
            Cells(i + 1, 1).EntireRow.Delete 'if so; delete
        End If
    End If
Next i
End Sub

what you prefer to use if personal. Please consider the answer of teylyn as well, in this situation, it is very usable.

Improve this answer

5 Comments

Thanks Jzz. I had something like this and it only deletes the second item and leaves the 3rd.
sorry, my bad! Simply forgot one (crucial) line of code. I edited my answer. Trick is to go back one row once you delete the row below it, so that the value is evalueated again.
Jzz, this does it! I could bet a lot of money that I tried this exact thing but it didn't work. I will run this on the full data set tomorrow, hopefully it does it. Thanks again.
Ahh, I meant: For i = Cells.SpecialCells(xlLastCell).Row to 1 Step -1 but then you have to change a few other things.
What is the correct step for the for loop to start from the bottom? I can't seem to get it to work. (VBA newbie, I know)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.