Delete/Hide Every nth Row

witteman · Jul 9th 2006

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

Kesey · Jul 9th 2006

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

Display More

Here's an example. Just run the Macro.

witteman · Jul 9th 2006

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

Kesey · Jul 9th 2006

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.

witteman · Jul 9th 2006

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

Dave Hawley · Jul 9th 2006

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

Display More

witteman · Jul 9th 2006

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

Dave Hawley · Jul 9th 2006

Re: Vlookup

Frank, please read & acknowledge my post above.

witteman · Jul 9th 2006

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

StefanG · Jul 9th 2006

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

Display More

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.

witteman · Jul 9th 2006

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

Reilly · Jul 7th 2019

Quote from StefanG

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.

Hi, responding after 13 years, but well, thank you for this solution!!

Delete/Hide Every nth Row

Participate now!

Share