Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cutting every other row from a spreadsheet
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cutting every other row from a spreadsheet
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 -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=546975 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cutting every other row from a spreadsheet
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 -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=546975 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cutting every other row from a spreadsheet
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 -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=546975 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to handle multiple currencies with one spreadsheet | Excel Discussion (Misc queries) | |||
Using Excel spreadsheet as input to Access | Excel Discussion (Misc queries) | |||
Spreadsheet merging problems | Excel Worksheet Functions | |||
Linkage data between two spreadsheet | Excel Worksheet Functions | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) |