deleting every other line of sheet

  • Thread starter Thread starter ajohns
  • Start date Start date
A

ajohns

I would like to delete every other line of an imported data.

I have tried writing a macro that deletes every other line but it is
very cumbersome.

Could someone suggest a smarter way to do it?

Many thanks

Yours

Andrew J
 
Andrew,

See if this method works any better.

1. In the column adjacent to your imported data enter and fill down the
following formula:

=Mod(Row(),2)

This will fill down 0,1,0,1,...

2. Autofilter your imported data, including the new column of 0 and 1
3. In the Autofilter for the new column, select either 1 or 0 (depending on
the data you want to keep)
4. Copy the autofiltered list and paste into a new worksheet.

Hope this helps.
 
Try this. Run it and it will prompt you for the starting row - This row will NOT be deleted. It
will then prompt you for the number of consecutive rows to delete after that starting row, ie if
you choose Row 14 as your starting row and then 1 as the number of rows, it will delete 15, 17,
19, etc.


Sub DelNumRows()

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 consecutive 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
 
Ken Wright said:
Try this. Run it and it will prompt you for the starting row - This row will NOT be deleted. It
will then prompt you for the number of consecutive rows to delete after that starting row, ie if
you choose Row 14 as your starting row and then 1 as the number of rows, it will delete 15, 17,
19, etc.


Sub DelNumRows()

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 consecutive 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
Click to expand...

Hi Guys

thanks a lot for all the suggestions. Will have to wait till I get
back to work to try them!

Yours

Andrew J
 
Back
Top