Cutting every other row from a spreadsheet

J

jc40

I have a large spreadsheet which I want to cut in half. I want to delete
every second row. Is there a formula I can use to do that and then re-paste
the new spreadsheet in a separate sheet?
 
L

Leith Ross

Hello Jc40,

First copy the Macro code below using Ctrl + C. With your workbook
open, press Alt + F11 to open the VB Editor. Next insert a standard VBA
Module into your project.

To add a new Module:
Press Alt + I to drop down the Insert menu.
Press M to insert the new Module
Press Ctrl + V to paste the code into the Module
Press Ctrl + S to save the Macro
Press Alt + Q to quit the Editor and return to Excel

To Run the Macro:
Select the worksheet you want to cut in half
Press Alt + F8 to display the Macro List
Click on CutInHalf and press Enter

Macro Code:

Public Sub CutInHalf()

Dim EndRow As Long
Dim StartRow As Long
Dim NewRow As Long
Dim NewWks As Worksheet
Dim OldWks As Worksheet
Dim R As Long

Set OldWks = ActiveSheet
With OldWks.UsedRange
StartRow = .Row
EndRow = .Rows.Count + .Row - 1
End With

ActiveWorkbook.Worksheets.Add After:=OldWks
Set NewWks = ActiveSheet

For R = StartRow To EndRow Step 2
NewRow = NewRow + 1
OldWks.Cells(R, "A").EntireRow.Copy
Destination:=NewWks.Cells(NewRow, "A").EntireRow
Next R

End Sub


Sincerely,
Leith Ross
 
P

Paul Sheppard

A quick way to get rid of every other row is to add an extra row and
fill down this row with 1 and 2 alternately, then filter to show either
the 1's or 2's and select the filtered rows and delete them
 
P

Paul Sheppard

A quick way to get rid of every other row is to add an extra row and
fill down this row with 1 and 2 alternately, then filter to show either
the 1's or 2's and select the filtered rows and delete them
 
Top