Deleting all even numbered rows?? Help

  • Thread starter Thread starter Yel268
  • Start date Start date
Y

Yel268

I have some data up to row 2200. Unfortunately, the data was capture
with a glitch, to where all even numbered rows (2, 4, 6,.....2200
contain bogus data.

I want to delete all even numbered rows in the spreadsheet, and hav
the 1100 odd numbered rows shift up, so I have only 1100 rows of th
real data on the spreadsheet
 
Hi
one way:
- in a helper column enter the formula
=--(MOD(ROW(),2)=0)

- Now apply a filter and filter all rows containing '1' in this helper
column
- delete these rows and remove the filter
 
Here's one you can try.

Sub DelNumRows()'Ken Wright

Dim lastrow As Long
Dim ans1 As Long
Dim ans2 As Long
Dim modans As Long
Dim r As Long

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

ans1 = InputBox("Choose a starting row")
ans2 = InputBox("How many rows to delete after starting row")
num = ans2 + 1
modans = Abs(num - (ans1 Mod num))

Application.ScreenUpdating = False

For r = lastrow To ans1 Step -1
If (Rows(r).Row + modans) Mod num <> 0 Then
Rows(r).Delete
End If
Next r

Application.ScreenUpdating = True
End Sub
 
Sub delete_even_rows()
Dim R As Long
Dim Rng As Range
Dim numLoops As Long
Set Rng = [A1:A2200] 'adjust to suit
numLoops = Int((Rng.Rows.Count / 2))
For R = 1 To numLoops
Rng(R + 1, 1).EntireRow.Delete
Next
End Sub

Gord Dibben Excel MVP
 
Back
Top