How to Delete/Hide Every nth Row

Category: [Excel] Demo Available

How to Delete/Hide Every nth Row

Requirement:

How to skip every second row (delete or hide) of an array of data (two columns) in a worksheet. The user has too much data and want to delete every other row at a regular interval (increase step interval from 1 to two or even three).

Solution:

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

For every third row change

Code:

i = i + 2

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.

OR ANOTHER SOLUTION

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

OR ANOTHER SOLUTION

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.

Edit: small correction: from

Code:

"Do Until I >= EndRow"

Code:

"Do Until I > EndRow"

as it would otherwise omit the last desired row to be deleted/hidden.

Obtained from the OzGrid Help Forum.

Solution provided by Kesey, Dave Hawley and StefanG.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.