deleting every other line of sheet

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
 
G

greg mansius [MSFT]

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.
 
K

Ken Wright

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
 
A

ajohns

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top