Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

remove rows between two values in a column

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
kwatt562
Asked:
kwatt562
1 Solution
 
JPIT DirectorCommented:
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
 
slycoderCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
kwatt562Author Commented:
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
 
JPIT DirectorCommented:
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
 
kwatt562Author Commented:
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
 
JPIT DirectorCommented:
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
 
kwatt562Author Commented:
That works perfectly, thanks a lot!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now