1

I have a spreadsheet that is ordered by an id and a calculated cell. I sort the sheet by the id and the calculated cell, thus giving me pairs of related rows. I simply want to delete the second row of each pair on the entire sheet. There are typically about 3000 rows.

Is there an easy way to delete or move the second row to another sheet?

5

Not a programming answer since you said you were not familiar with VBA in Excel, but if it's a one-off then just do it in Excel:

  1. Add a new column (let's say A)
  2. Set the first row (A1) to True
  3. Set row A2 to =NOT(A1)
  4. Then drag row A2 down to all your rows.

Then apply an automatic filter where column A is false, select all returned rows and delete them, and then remove the filter.

| improve this answer | |
  • Perfect! yes, i was just looking for a simple solution. Thanks so much for your help! Best, John..... – John Rattinger Jan 9 '13 at 5:38
0

If this is a one-off task, use Seph's answer. If it needs to be repeated, use VBA, like this:

Sub DemoDeleteAlternateRows()
    Dim iRow As Long, LastRow As Long
    Dim sh As Worksheet
    Set sh = ActiveSheet ' <-- Maybe change this to refer to a specific sheet
    Application.FindFormat.Clear ' Just in case Find was last used with a Format
    LastRow = sh.Cells.Find(What:="*", After:=sh.Cells(1, 1), _
      SearchDirection:=xlPrevious, SearchOrder:=xlByRows, SearchFormat:=False).Row
    For iRow = LastRow To 1 Step -1
        ' Delete even numberede rows (to delete odd numbered
        ' rows, instead use  If iRow Mod 2 = 1 Then).
        If iRow Mod 2 = 0 Then
            sh.Rows(iRow).Delete
        End If
    Next
End Sub

Make sure you back up before running any macro on your sheet. Macros don't use Undo!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy