Solved

remove rows between two values in a column

Posted on 2011-09-08
8
270 Views
Last Modified: 2012-05-12
Hi
I'm working on some macros for a spreadsheet but am having a hardtime coming up with a way to hide or delete extra data via code.
The scenario is this - In column K there is either a 1 or a 0, 1 indicates the start of a process, 0 the end.
so the column can look like the below (i.e. multiple proceses start and stop)
I want to find the first 1 then the 0 and delete the rows in bettween, then find the next 1 and next 0 and delete the rows in between and so on
1
1 -Delete
1 -Delete
1 -Delete
0
0 -Delete
0 -Delete
1
1 -Delete
1 -Delete
0
0 -Delete
0 -Delete
1
1 -Delete
0
0
Comment
Question by:kwatt562
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 14

Expert Comment

by:peetjh
ID: 36504370
Try the attached code.
Sub cleanup()
Application.ScreenUpdating = False
Dim lastrow As Long
lastrow = [k65536].End(xlUp).Row
For i = lastrow To 2 Step -1 'Change the "to 2" to "to 1" if you do not have headers!
    If Cells(i - 1, "k").Value = Cells(i, "k").Value Then
    Rows(i).Delete (xlUp)
    End If
Next i
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:slycoder
ID: 36504408
Mine works very similar:


Public Sub DeleteItems()

    ' Turn off screen updates for speed
    Application.ScreenUpdating = False

    ' Change this to be the last row
    ' Position Cursor at end of range
    Range("K16").Select
   
    'Loop till at the top - again you can change this if you have headers
    Do While ActiveCell.Row > 1
   
        ' check previous cell
        If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
       
            ' delete current row
            Selection.EntireRow.Delete
        End If
       
        ' Move cursor up
        ActiveCell.Offset(-1, 0).Select
    Loop
   
    ' Turn on screen updates
    Application.ScreenUpdating = True
   
End Sub
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36509224
Would this logic apply?

If the value of the cell above is the same as the current row, the current row can be deleted.

If so this can be done with formula and then just apply a filter to this new column.

Assuming data starts in K1, formula in L2:

=IF(K2=K1,"Delete","")

Apply AutoFilter on column K and filter for Delete. Select visible cells with mouse or cursor keys but not whole column, delete rows, warning message about deleting entire row, OK.

This is what Slycoder's routine is doing but in code one row at a time. Might be quicker to apply filter and delete in one hit.

Thanks
Rob H
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kwatt562
ID: 36509523
Thanks a lot, all great suggestions/solutions
I wonder if you could help with a scenario i just encountered with one particular worksheet
it relates to first 0 (end point) not being the actual end point in the process
is there a way to set the end point in the process to be the first 0 (in column K) that has the maximum numeric value in column p
so
colK           colP
1
1 -delete
1 -delete
0 -delete          
0 -delete   400
0                600
1
1 -delete
etc
0
 
LVL 14

Expert Comment

by:peetjh
ID: 36510098
Try the attached code.
Sub cleanup()
Application.ScreenUpdating = False
Dim lastrow As Long
lastrow = [k65536].End(xlUp).Row
For i = lastrow To 2 Step -1 'Change the "to 2" to "to 1" if you do not have headers!
    If Cells(i - 1, "k").Value = Cells(i, "k").Value Then
        If Cells(i - 1, "p").Value > Cells(i, "p").Value Then
            Rows(i).Delete (xlUp)
        Else
            Rows(i - 1).Delete (xlUp)
            GoTo continue
        End If
    Rows(i).Delete (xlUp)
    End If
continue:
Next i
End Sub

Open in new window

0
 

Author Comment

by:kwatt562
ID: 36510763
Hi thanks for that, doesnt quite work out, but I'm probably not explaining myself well enough
I attach an example report (this is a simplied version without all the other macros on it.
Highlighted in red is the start of each process (number 1 column K), the end of the process is highlighted in blue (last number 0 in column K,with highest value in column P - In the case that the values are the same, then it should be the last value)
So once the macro runs there should only 4 rows remaining below the header
1 red (start),1 blue (end) then 1 red(start) ,1 (end) blue (obviously in other examples there may be more processes)


test.xls
0
 
LVL 14

Accepted Solution

by:
peetjh earned 500 total points
ID: 36511708
Sorry you were right there was some misunderstanding. The attached code worked for me with your sample file.
Sub cleanup()
Application.ScreenUpdating = False
Dim lastrow As Long
lastrow = [k65536].End(xlUp).Row
For i = lastrow To 8 Step -1 'Change the "to 8" to "to #" top row with data to be evaluated
    Select Case Cells(i, "k").Value
        Case 0
            If Cells(i - 1, "k").Value = Cells(i, "k").Value Then
                If Cells(i - 1, "p").Value <= Cells(i, "p").Value Then
                    Rows(i - 1).Delete (xlUp)
                    GoTo continue
                Else
                    Rows(i).Delete (xlUp)
                    GoTo continue
                End If
            End If
        Case 1
            If Cells(i - 1, "k").Value = Cells(i, "k").Value Then
                    Rows(i).Delete (xlUp)
                    GoTo continue
            End If
    End Select
continue:
Next i
End Sub

Open in new window

0
 

Author Comment

by:kwatt562
ID: 36511745
That works perfectly, thanks a lot!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question