UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Delete every other line macro    
 
   
rminche
post Aug 10 2006, 12:06 PM
Post #1

UtterAccess Addict
Posts: 228
I was wondering if there is a way to get a macro to delete every other line in a spreadsheet?
Go to the top of the page
 
+
Luceze
post Aug 10 2006, 12:31 PM
Post #2

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA
In the entire worksheet?

This will do it for the used range. Notice I commented out the delete line.

CODE
[color="blue"]Sub[/color] DeleteRows()
    [color="blue"]Dim[/color] rng1 [color="blue"]As[/color] Range
    [color="blue"]Dim[/color] i [color="blue"]As[/color] [color="blue"]Long[/color]
        [color="blue"]For[/color] i = 1 [color="blue"]To[/color] ActiveSheet.UsedRange.Rows.Count [color="blue"]Step[/color] 2
            [color="blue"]If[/color] rng1 [color="blue"]Is[/color] [color="blue"]Nothing[/color] [color="blue"]Then[/color]
                [color="blue"]Set[/color] rng1 = Cells(i, 1)
                    [color="blue"]Else[/color]
                [color="blue"]Set[/color] rng1 = Union(rng1, Cells(i, 1))
            [color="blue"]End[/color] [color="blue"]If[/color]
        [color="blue"]Next[/color]
    rng1.EntireRow.Select
    [color="green"]'rng1.EntireRow.delete[/color]
    [color="blue"]Set[/color] rng1 = [color="blue"]Nothing[/color]
[color="blue"]End[/color] [color="blue"]Sub[/color]


HTH,
Go to the top of the page
 
+
rminche
post Aug 10 2006, 02:19 PM
Post #3

UtterAccess Addict
Posts: 228
thanks!!!!!!!that works great.



Can it be changed from every third or fourth with this code also??
Go to the top of the page
 
+
rminche
post Aug 10 2006, 02:25 PM
Post #4

UtterAccess Addict
Posts: 228
I figured it out thanks so much for your help. You are awesome!!!!! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)
Go to the top of the page
 
+
rminche
post Aug 10 2006, 03:03 PM
Post #5

UtterAccess Addict
Posts: 228
Is there a dummy book I can read about this stuff?

Also, can you select the third & fourth line at the same time to delete and set a range like from 1 to 100?
Go to the top of the page
 
+
Luceze
post Aug 10 2006, 03:49 PM
Post #6

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA
Like this?

CODE
Sub DeleteRows()
Application.ScreenUpdating = False
    Dim rng1 As Range
    Dim i As Long, n As Long
        For i = 1 To 100
        n = n + 1
            If n = 3 Or n = 4 Then
                If rng1 Is Nothing Then
                    Set rng1 = Cells(i, 1)
                        Else
                    Set rng1 = Union(rng1, Cells(i, 1))
                End If
                If n = 4 Then n = 0
            End If
        Next
    rng1.EntireRow.Select
    'rng1.EntireRow.delete
    Set rng1 = Nothing
End Sub
Go to the top of the page
 
+
rminche
post Aug 11 2006, 09:22 AM
Post #7

UtterAccess Addict
Posts: 228
wonderful!!!!!!!!!

any suggestions on a book that helps you learn this?
Go to the top of the page
 
+
Luceze
post Aug 11 2006, 10:27 AM
Post #8

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA
Try here.

Nate and Martin reference some good reading materials.
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 19th June 2013 - 06:48 PM