EdLB
asked on
Using VB to sort and eliminate rows based on a column value.
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( "ProcessDa ta").Sort. SortFields .Clear
'Sort the entire worksheet based on the Process Time column (Column G)
ActiveWorkbook.Worksheets( "ProcessDa ta").Sort. SortFields .Add key:=Range( _
"G2:G14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets( "ProcessDa ta").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( "ProcessDa ta").Sort. SortFields .Clear
ActiveWorkbook.Worksheets( "ProcessDa ta").Sort. SortFields .Add key:=Range( _
"A2:A14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets( "ProcessDa ta").Sort. SortFields .Add key:=Range( _
"D2:D14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets( "ProcessDa ta").Sort
.SetRange Range("A1:M14699")
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
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(
'Sort the entire worksheet based on the Process Time column (Column G)
ActiveWorkbook.Worksheets(
"G2:G14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(
.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(
ActiveWorkbook.Worksheets(
"A2:A14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(
"D2:D14699"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A1:M14699")
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Use Autofilter to leave visible only the rows where the process time is zero and then simply delete those rows all at once.
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
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
ASKER
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
Thanks, Ed
That shouldn't be difficult but first lets see if it does what you want the way it is.
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
Bregs
Rossano Praderi
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
Bregs
Rossano Praderi
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
ASKER
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
EliminateZeros.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014