Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Delete/Hide Every nth Row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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...

    Code:
    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
    Last edited by Kesey; July 9th, 2006, 13:58.

    Comment


    • #3
      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

      Comment


      • #4
        Re: Vlookup

        For every third row change

        Code:
        i = i + 2
        to

        Code:
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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.
            Code:
            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, 15:03.

            Comment


            • #7
              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

              Comment


              • #8
                Re: Vlookup

                Frank, please read & acknowledge my post above.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Code:
                    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
                    Code:
                                'to delete the tows
                                Selection.Delete Shift:=xlUp
                                I = I + StepRow - 1
                    to this
                    Code:
                     ' 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
                    Code:
                    "Do Until I >= EndRow"
                    to
                    Code:
                    "Do Until I > EndRow"
                    as it would otherwise omit the last desired row to be deleted/hidden.
                    Last edited by StefanG; July 9th, 2006, 15:38.

                    Comment


                    • #11
                      Re: Hide Every Nth Row

                      WOOOW

                      Stephan, you really did it, I like it very much, maybe the software vendor that I use for processing these files will include your code for the import file menu. I will give them your name.

                      Thanks guys, all of you, I really need to continue with my VBA lessons.

                      Best regards

                      Frank

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X