Delete every 2nd and 3rd row- VBA 
Author Message
 Delete every 2nd and 3rd row- VBA

I am using XL 2000 and am in need of a macro  but have very limited VBA
experience.

Here is what I have been trying to do with a file:

row 1
row 2 delete
row 3 delete
row 4
row 5 delete
row 6 delete
row 7

I want to delete every 2nd and 3rd row, but am having problems trying to
record the macro.  I used Chip Pearson's deleting duplicate entries
macro and it worked great.  But the file will not always have an easy to
identify duplicate record field.

Thanks in advance

Scott



Wed, 18 Jun 1902 08:00:00 GMT  
 Delete every 2nd and 3rd row- VBA

Quote:

>I am using XL 2000 and am in need of a macro  but have very limited VBA
>experience.

>Here is what I have been trying to do with a file:

>row 1
>row 2 delete
>row 3 delete
>row 4
>row 5 delete
>row 6 delete
>row 7

>I want to delete every 2nd and 3rd row, but am having problems trying to
>record the macro.  I used Chip Pearson's deleting duplicate entries
>macro and it worked great.  But the file will not always have an easy to
>identify duplicate record field.

Here's one method. I assume you want to keep rows 1, 4, 7, etc, as you show. I
locate the last row of data by looking at column A.

  Dim R As Long

  R = Cells(Rows.Count, 1).End(xlUp).Row

  Select Case R Mod 3
  Case 0: R = R - 1
  Case 1: R = R - 2
  End Select

  For R = R To 2 Step - 3
    Rows(R).Resize(2).EntireRow.Delete
  Next R



Wed, 18 Jun 1902 08:00:00 GMT  
 Delete every 2nd and 3rd row- VBA

Quote:

> Here is what I have been trying to do with a file:

> row 1
> row 2 delete
> row 3 delete
> row 4
> row 5 delete
> row 6 delete
> row 7

Sub DeleteRows()
  Dim lRow As Long
  Dim lLastRow As Long
  lLastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
  For lRow = (lLastRow \ 3)*3+2 To 2 Step -3
    Rows(lRow).Resize(2).Delete
  Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Wed, 18 Jun 1902 08:00:00 GMT  
 Delete every 2nd and 3rd row- VBA
Thank you both very much.  Both examples work great.
 I am still trying to figure out exactly how they both work though.
Quote:


> >I am using XL 2000 and am in need of a macro  but have very limited VBA
> >experience.

> >Here is what I have been trying to do with a file:

> >row 1
> >row 2 delete
> >row 3 delete
> >row 4
> >row 5 delete
> >row 6 delete
> >row 7

> >I want to delete every 2nd and 3rd row, but am having problems trying to
> >record the macro.  I used Chip Pearson's deleting duplicate entries
> >macro and it worked great.  But the file will not always have an easy to
> >identify duplicate record field.

> Here's one method. I assume you want to keep rows 1, 4, 7, etc, as you show. I
> locate the last row of data by looking at column A.

>   Dim R As Long

>   R = Cells(Rows.Count, 1).End(xlUp).Row

>   Select Case R Mod 3
>   Case 0: R = R - 1
>   Case 1: R = R - 2
>   End Select

>   For R = R To 2 Step - 3
>     Rows(R).Resize(2).EntireRow.Delete
>   Next R



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Deleting every 2nd and 3rd row

2. Getting #deleted message in every field of every row when linking to a SQL Server DB

3. Sorting every 3rd row

4. Selecting every 3rd row for copying

5. Dynamic Sum (of every 2nd row) on Range Resize

6. Every 2nd row bold, how to?

7. Deleting several rows every nth row

8. how to delete every second row

9. Deleting every second row?

10. Please - Macro that deletes every second row


 
Powered by phpBB® Forum Software © phpBB Group