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

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

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

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

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