Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Delete/Hide Every nth Row

  1. #1
    Join Date
    9th July 2006
    Posts
    18

    Delete/Hide Every nth Row

    How to skip every second row (delete or hide) of an array of data (two columns) in a worksheet. I have too much data and want to delete every other row at a regular interval (increase step interval from 1 to two or even three). Tried to do this with VLOOKUP and Filter (custom) but no luck.

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th July 2006
    Posts
    6

    Re: Vlookup

    I see your assumed experience so this may be a little too much, but it's really not too hard. Have you ever recorded a macro? Something along these lines...

    VB:
    Sub DeleteEveryOtherRow() 
         
         ' DeleteEveryOtherRow Macro
         ' Macro recorded by Kesey
         
        i = 1 
        Do Until IsEmpty(ActiveCell.Value) 
            Rows(i).Select 
            Selection.Delete Shift:=xlUp 
            i = i + 2 
        Loop 
    End Sub 
    
    
    Here's an example. Just run the Macro.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Kesey; July 9th, 2006 at 13:58.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th July 2006
    Posts
    18

    Re: Vlookup

    Hi Kesey,

    That looks exactly what I am looking for: and if I want every 3rd row deleted I asume I change 1 to 2?

    I have recorded some macros in the past but never f'inished the VBA tutorial'. By the way, I am a 61 year old oil field professional and feel very flattered by the junior rating in this newsgroup.

    I will let you know if I get your macro to work.

    Thanks a lot

    Frank

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th July 2006
    Posts
    6

    Re: Vlookup

    For every third row change

    VB:
    i = i + 2 
    
    
    to

    VB:
    i = i + 3 
    
    
    Setting "i" tells the macro how many rows to go down to find the next row to delete - if that makes sense.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th July 2006
    Posts
    18

    Re: Vlookup

    Kesey,

    Thank you very much, I have been looking for this quite some time, never crossed my mind to post this on a forum......

    I ran your macro and it skipped two rows, deleted every third row (rows 1,4,7 etc. deleted) that's fine but I may want to skip only one row to get a certain pattern in my column 1 (normally a depth range). I went to macro edit and did see the i=1 statement, when I change this to 0 will it then do every second row?
    How to save this to a new macro? or do I change the 'i=1' somewhere else? not in macro mode? Please explain as I am not good with macros.

    Also when the file is huge I may want to delete two rows, so I will run the macro twice?

    Thanks again

    Frank

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Vlookup

    Hi Frank

    Welcome to ozgrid

    Please take more care with your Thread Titles, your current one (VLOOKUP) has nothing to do with your question and is of no help for those that search.

    For your problem, I wouldn't delete rows, but rather hide them.
    VB:
    Sub HideNthRow() 
        Dim rRange As Range, rCell As Range 
        Dim ln As Long, lCount As Long 
         
        ln = Application.InputBox(Prompt:="Hide Every:", Title:="Row Hider", Default:=2, Type:=1) 
        If ln = 0 Then Exit Sub 
         
        Set rRange = Range("A1", Range("A65536").End(xlUp)) 
         
        For lCount = ln To rRange.Rows.Count Step ln 
            rRange.Cells(lCount, 1).EntireRow.Hidden = True 
        Next lCount 
    End Sub 
    
    
    Last edited by Dave Hawley; July 9th, 2006 at 15:03.

  7. #7
    Join Date
    9th July 2006
    Posts
    18

    Re: Vlookup

    Kesey,

    I got it worked out i=i+1 will delete every second row. I did this in VBA edit and then changed back to my worksheet and ran the macro.

    I will spread this around as a lot of my friends have the same data problem (files too large) and cull the data by deleting rows manually. All credit to you.

    Cheers,

    Frank Witteman
    Kuala Lumpur, Malaysia

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  9. #9
    Join Date
    9th July 2006
    Posts
    18

    Re: Vlookup

    Hi Dave,

    Point taken, I tried out your code and that is even more
    sophisticated, I thank you all for this Excel macro.

    Best regards

    Frank Witteman

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th July 2005
    Posts
    214

    Re: Hide Every Nth Row

    Hi witteman,

    How about this - may be overkill, but hey...
    You will get a message box in which row to start, in which increments to delete rows and which row is the last to be deleted. Before deleting, you get an option to verify your selection.

    VB:
    Option Explicit 
    Sub DeleteRow() 
        Dim EndRow, CheckRows, I, StartRow, StepRow 
        StartRow = Application.InputBox _ 
        ("Enter which row is the first to be removed." & Chr(10), _ 
        "Rows to delete - Start point", , , , , , 1) 
        If TypeName(StartRow) = "Boolean" Then 
            Exit Sub 
        End If 
         
        StepRow = Application.InputBox _ 
        ("Enter increment of n-th row to delete," & Chr(10) & _ 
        "i.e. 2 = every other, 3 every third?" & Chr(10), _ 
        "Rows to delete - Step", , , , , , 1) 
        If TypeName(StepRow) = "Boolean" Or StepRow <= 1 Then 
            MsgBox "Sorry, do not remove every row with this code." 
            Exit Sub 
        End If 
         
        EndRow = Application.InputBox _ 
        ("Enter which row is the last to be removed." & Chr(10), _ 
        "Rows to delete - End point", , , , , , 1) 
        If TypeName(EndRow) = "Boolean" Then 
            Exit Sub 
        End If 
         
        CheckRows = MsgBox("You want to remove rows in steps of " & StepRow _ 
        & ", starting with row " & StartRow & "  and ending with row " _ 
        & EndRow & ". Correct?", vbYesNo, "Verify data!") 
        If CheckRows = vbYes Then 
            I = StartRow 
            Do Until I > EndRow 
                Rows(I).Select 
                Selection.Delete Shift:=xlUp 
                I = I + StepRow - 1 
            Loop 
        Else 
        End If 
        Application.Goto Reference:="R1C1" 
    End Sub 
    
    
    To build on this:
    Change
    VB:
     'to delete the tows
    Selection.Delete Shift:=xlUp 
    I = I + StepRow - 1 
    
    
    to this
    VB:
     ' to hide the rows
    Selection.EntireRow.Hidden = True 
    I = I + StepRow 
    
    
    and of course the message boxes from reference "deleting rows" to "hiding rows" and vica versa.

    Stefan

    Edit: small correction: from
    VB:
    "Do Until I >= EndRow" 
    
    
    to
    VB:
    "Do Until I > EndRow" 
    
    
    as it would otherwise omit the last desired row to be deleted/hidden.
    Last edited by StefanG; July 9th, 2006 at 15:38.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Code Runs When I Hide Rows Or Delete Cells
    By cricks11 in forum EXCEL HELP
    Replies: 4
    Last Post: August 3rd, 2008, 07:09
  2. Hide Workbook & Delete From Recent Files List
    By DonMS in forum EXCEL HELP
    Replies: 4
    Last Post: October 18th, 2006, 20:05
  3. Hide or delete Rows using a formula
    By Eddie1 in forum EXCEL HELP
    Replies: 2
    Last Post: July 21st, 2006, 23:40
  4. Replies: 4
    Last Post: April 22nd, 2006, 17:17
  5. Replies: 1
    Last Post: July 7th, 2004, 16:26

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno