[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using VB to sort and eliminate rows based on a column value.

Posted on 2015-02-20
12
Medium Priority
?
80 Views
Last Modified: 2016-02-10
I have a spreadsheet that has many columns and rows, If the value in the process time column is zero, I want to delete that row using VBA as part of an existing macro. the spreadsheet has about 16,000 rows and the zero values are in about 1,100 rows interspersed randomly throughput the spreadsheet.

One solution is to just loop through all rows and delete the row where the zero values are. However that gets a little tricky as deleting the rows will change the number of rows which will affect the loop counter.

I am hoping there is a way to do this without a loop. Thanks in advance for your help.

Here is the VBA for a macro recorded doing this manually:

Sub EliminateZeroProcessTimes()
'
' EliminateZeroProcessTimes Macro
'
    Cells.Select
    Range("B1").Activate
    ActiveWorkbook.Worksheets("ProcessData").Sort.SortFields.Clear
   
    'Sort the entire worksheet based on the Process Time column (Column G)
    ActiveWorkbook.Worksheets("ProcessData").Sort.SortFields.Add key:=Range( _
        "G2:G14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ProcessData").Sort
        .SetRange Range("A1:M14699")
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    'Manually select the rows with zero values in column G and delete those rows
    Rows("2:1027").Select
    Range("B2").Activate
    Selection.Delete Shift:=xlUp
    Cells.Select
   
    'Sort the entire worksheet to get back in original order (minus delted rows)
    Range("B1").Activate
    ActiveWorkbook.Worksheets("ProcessData").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ProcessData").Sort.SortFields.Add key:=Range( _
        "A2:A14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("ProcessData").Sort.SortFields.Add key:=Range( _
        "D2:D14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ProcessData").Sort
        .SetRange Range("A1:M14699")
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
0
Comment
Question by:EdLB
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40622400
Use Autofilter to leave visible only the rows where the process time is zero and then simply delete those rows all at once.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40622418
In general removing from array or deleteng rows should be in reverse order I.e. you counter should start from a variable that has activesheet.usedrange.rows.count to 1 step -1
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40622420
This should do it. No sorting or looping needed. It assumes that Process Time is in column D.

   
    Range("D1").Select
    Selection.AutoFilter
    With ActiveSheet
        .Range("D1:D" & .UsedRange.Rows.Count).AutoFilter Field:=1, Criteria1:="0"
    End With
    Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:EdLB
ID: 40622472
Martin, that looks great! Can't get to it right now to try but I have another twist. I have the column heading as a named range called, surprisingly, "ProcessTimes." How would I incorporate use of the named range into your code.
Thanks, Ed
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40622483
That shouldn't be difficult but first lets see if it does what you want the way it is.
0
 
LVL 2

Expert Comment

by:Rossano Praderi
ID: 40622950
With the code posted by Martin you will delete the first row because there isn't any check.
The following code is not perfect and isn't the faster but is more secure

Sub delRows()
  Dim mRange As Range
  Application.Goto Reference:="ProcessTimes"
  Selection.AutoFilter
  ActiveSheet.Range("ProcessTimes").AutoFilter Field:=4, Criteria1:="0"
  Set mRange = Selection.SpecialCells(xlCellTypeVisible)
  If mRange.Areas.Item(1).Cells(1, 4).Value <> "0" Then mRange.Areas.Item(1).Rows(1).Hidden = True
    Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Selection.AutoFilter
End Sub

Open in new window


Bregs
Rossano Praderi
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40623105
Dshortway is right. Using my original code the header would be deleted if there were no process times of zero. However that's easily fixed. Again this assumes that the process times are in column D.

    Range("D1").Select
    Selection.AutoFilter
    With ActiveSheet
        .Range("D1:D" & .UsedRange.Rows.Count).AutoFilter Field:=1, Criteria1:="0"
    End With
    If WorksheetFunction.CountIf(ActiveSheet.Columns(4), "0") > 0 Then
        ActiveSheet.Rows(1).Hidden = True
        Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.Rows(1).Hidden = False
    End If
    Selection.AutoFilter

Open in new window

0
 

Author Comment

by:EdLB
ID: 40623603
Neither solution worked. I have put together a sample file with the macro in it for illustration of the layout. "Process Times" is in column G but I put zeros in column D and it made no difference.
EliminateZeros.xlsm
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 40623769
    
Sub delRows()
Worksheets("ProcessData").Activate
    Range("G1").Select
    Selection.AutoFilter
    With ActiveSheet
        .Range("G1:G" & .UsedRange.Rows.Count).AutoFilter Field:=7, Criteria1:="0"
    End With
    If WorksheetFunction.CountIf(ActiveSheet.Columns("G"), "0") > 0 Then
        ActiveSheet.Rows(1).Hidden = True
        Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.Rows(1).Hidden = False
    End If
    Selection.AutoFilter
    Range("G1").Select
End Sub

Open in new window

0
 
LVL 12

Assisted Solution

by:FarWest
FarWest earned 1000 total points
ID: 40623791
here is the code which will delete rows in less than one second
using the idea I told you before
Sub delRows2()
Debug.Print Now()
Dim ii As Integer, rCount, fColumn
fColumn = 4 ' column to check value
rCount = ActiveSheet.UsedRange.Rows.Count
For ii = rCount To 2 Step -1 'end on 2 to exclude header 
If ActiveSheet.Cells(ii, fColumn) = 0 Then ActiveSheet.Rows(ii).EntireRow.Delete
Next
Debug.Print Now()
End Sub

Open in new window

good luck
0
 

Author Closing Comment

by:EdLB
ID: 40629597
Thanks Martin and FarWest. Both work now. I appreciate the help. Not sure how to split up the points so I split them 50/50.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40629615
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

649 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