hi, I have a 3 columns: 1st column is the type and the next 2 columns are percent and rate.

I want to automatically sort the data descending according to rate and hide rows with zero values and #N/A when I click run macro.

Type Percent RateAcry 2.23% 0.05Blea 0.00% 0.00Buil 8.98% 0.03Epox 5.67% 0.01Fats 7.06% 3.07Frag 3.33% 0.58Gap 4.91% 0.04Grai -2.90% -0.01HotP 3.10% 0.01HotT 6.21% 0.00Inor 13.38% 0.48Inte 0.00% 0.00Meta 0.00% 0.00Othe #N/A #N/APhos 8.33% 0.02Plas 0.00% 0.00Poly 2.42% 0.01Sili 1.25% 0.07Solv -1.46% -0.06Spec 4.77% 0.84

Please help.thanks.

You didn't include row numbers or column letters, so I had to assume that your data is in A1:C21. Try this...

Sub SortHideErrZero() 'Unhide all rows Cells.EntireRow.Hidden = False 'Determine length of data lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Sort Descending on Column C Set myRange = Range("A1:C" & lastRw) ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _ Key:=Range("C2:C" & lastRw), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange myRange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Hide Errors and 0 values based on Column C For myRow = 2 To lastRw If IsError(Cells(myRow, 3)) Then Cells(myRow, 3).EntireRow.Hidden = True ElseIf Cells(myRow, 3) = 0 Then Cells(myRow, 3).EntireRow.Hidden = True End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you.

This works fine. :)Forgot to say the columns and row. It is from Row9 Column C.

If you don't mind, I forgot to include that I will also be hiding the Type that contains "GAP". How would I do that? I am trying to add it on this

For myRow = 2 To lastRw

If IsError(Cells(myRow, 3)) Then

Cells(myRow, 3).EntireRow.Hidden = True

ElseIf Cells(myRow, 3) = 0 Then

Cells(myRow, 3).EntireRow.Hidden = True

ElseIf Cells(myRow, 1).Value = "Gap" then

Cells(myRow, 3).EntireRow.Hidden = True

End If

NextBut instead of

.ValueI want it"contains"Also, I forgot to include that this table has TOTAL on the last row. When I run the macro, the total is included on the sorting but it should not. It should remain on the last row.

Thanks so much again.

You code works fine for hiding Rows where Column A contains Gap. What problem are you having? As far as the Row that contains Total, just decrease the lastRw variable by 1:

lastRw = Range("A" & Rows.Count).End(xlUp).Row - 1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

This works perfectly good! thank you som much for all your help!

:)

hi..just a quick question..

How can I run the code in Excel 2003?Sub SortHideErrZero()

'Unhide all rows

Cells.EntireRow.Hidden = False

'Determine length of data

lastRw = Range("A" & Rows.Count).End(xlUp).Row

'Sort Descending on Column C

Set myRange = Range("A1:C" & lastRw)

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _

Key:=Range("C2:C" & lastRw), SortOn:=xlSortOnValues, _

Order:=xlDescending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange myRange

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

'Hide Errors and 0 values based on Column C

For myRow = 2 To lastRw

If IsError(Cells(myRow, 3)) Then

Cells(myRow, 3).EntireRow.Hidden = True

ElseIf Cells(myRow, 3) = 0 Then

Cells(myRow, 3).EntireRow.Hidden = True

End If

Next

End Sub

The code should work the same way in 2003. Have you tried it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, I tried to run it on a 2003 version and it has a Run-time error '438' Object doesn't support this property or methodwhen I click on debug it highlights the

ActiveSheet.Sort.SortFields.Add _ Key:=Range("C2:C" & lastRw), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormalon the codethanks.

I'll have to run it on a system with 2003 when I get a chance. I'll let you know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

hi.. i tried debugging and it is now working..

here's what I did..

Sub SortHideErrZero() 'Unhide all rows Cells.EntireRow.Hidden = False 'Determine length of data lastRw = Range("A" & Rows.Count).End(xlUp).Row - 1 'Sort Descending on Column C Set myRange = Range("A2:C" & lastRw) myRange.Select Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'With ActiveSheet.Sort '.SetRange myRange '.Header = xlYes ' .MatchCase = False ' .Orientation = xlTopToBottom ' .SortMethod = xlPinYin ' .Apply 'End With 'Hide Errors and 0 values based on Column C For myrow = 2 To lastRw If IsError(Cells(myrow, 3)) Then Cells(myrow, 3).EntireRow.Hidden = True ElseIf Cells(myrow, 3) = 0 Then Cells(myrow, 3).EntireRow.Hidden = True ElseIf Cells(myrow, 1).Text Like "Gap*" Then Cells(myrow, 1).EntireRow.Hidden = True End If Next End SubThanks :)

It looks like Excel 2010 uses different arguments for a VBA Sort than Excel 2003, and

it is not backwards compatible.Luckily the 2003 code works in 2010, so the following code should work in both versions.

I made some changes to your code, mainly because of this:

'Sort Descending on Column C Set myRange = Range("A2:C" & lastRw) myRange.Select Selection.Sort ...Rarely do you have to actually "select" an object in VBA in order to perform an operation on it. You can almost always perform the operation directly on the object.

It more efficient to use this:

'Sort Descending on Column C Set myRange = Range("A2:C" & lastRw) myRange.Sort ...This code worked for me in both 2003 and 2010:

Sub SortHideErrZero() 'Unhide all rows Cells.EntireRow.Hidden = False 'Determine length of data lastRw = Range("A" & Rows.Count).End(xlUp).Row - 1 'Sort Descending on Column C Set myRange = Range("A2:C" & lastRw) myRange.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Hide Errors and 0 values based on Column C 'Hide "Gap" in Column A For myRow = 2 To lastRw If IsError(Cells(myRow, 3)) Then Cells(myRow, 3).EntireRow.Hidden = True ElseIf Cells(myRow, 3) = 0 Then Cells(myRow, 3).EntireRow.Hidden = True ElseIf Cells(myRow, 1).Value = "Gap" Then Cells(myRow, 3).EntireRow.Hidden = True End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks a lot! :)

Ask Your Question

Weekly Poll

Did you watch the X-Files when it was on TV?

Discuss in The Lounge

Poll History