Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Delete rows
Is there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones Thanks -- tjc ------------------------------------------------------------------------ tjc's Profile: http://www.excelforum.com/member.php...o&userid=27792 View this thread: http://www.excelforum.com/showthread...hreadid=472999 |
#2
|
|||
|
|||
Use of VBA is required. Here is one way to do it, from a post by Ron de Bruin, modified to delete every even row:Ron de Bruin Wrote: One way to insert a column with a formula and use SpecialCells(xlCellTypeBlanks) to delete the rows TEST IT ON A COPY OF YOUR WORKBOOK Sub test1() Application.ScreenUpdating = False Dim myRows As Long Range("A1").EntireColumn.Insert myRows = ActiveSheet.UsedRange.Rows.Count With Range(Cells(1, 1), Cells(myRows, 1)) .FormulaR1C1 = "=IF(MOD(ROW(),2)=1,""Keep"","""")" .Value = .Value End With Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete Range("A1").EntireColumn.Delete Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=472999 |
#3
|
|||
|
|||
Hi
One way Create a helper column with the formula =MOD(ROW(),2) copy down the column DataFilterAutofilter select the drop down on the new helper column and select 0 Delete all the visible rows as they are the even ones. Regards Roger Govier tjc wrote: Is there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones Thanks |
#4
|
|||
|
|||
tjc wrote:
Is there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones Thanks I use a freebie program called ASAP Utilities available at: http://www.asap-utilities.com/ If you decide to get it select Rows and columns and it's the first choice. This little freebie has MANY functions with just a click or two. I find it very handy. Standard disclaimer: I'm in no way associated with or receive compensation from ASAP utilities. gls858 |
#5
|
|||
|
|||
Insert a column. Enter 1 in the first row. Select that row and select all
the way down your worksheet. Click Edit - Fill - Series - Linear with step value one. Now you have made a nice index for later use. Insert another column. Leave the row you want blank. Enter an x in the row you don't want. Next one blank. Next one an x. Select the first blank cell to the second x. Click on the lower right box (your cursor changes to a plus sign). Drag it down to the end of your data. Then you select all columns with data and sort by the column with the x's. This will put all the rows you want to delete together so you can quickly delete them. Then you grab all your columns with data and resort by that index you created. Try to do this within 2 minutes. "tjc" wrote: Is there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones Thanks -- tjc ------------------------------------------------------------------------ tjc's Profile: http://www.excelforum.com/member.php...o&userid=27792 View this thread: http://www.excelforum.com/showthread...hreadid=472999 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can we delete rows permanently from excel sheet | Excel Discussion (Misc queries) | |||
In a protected worksheet allow users to delete rows | Excel Worksheet Functions | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) | |||
Protect Worksheet but allow to insert or delete rows | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) |