Articles
Downloads
Shopping
Tech Support

Tom's HardwareTom's IT Pro

Solved hide and sort columns and rows macro

Tags:
Mstest / Awrdacpi
columns
sort
macros
hide
November 13, 2012 at 17:39:51
Specs: Microsoft Windows XP Professional, 2 GHz / 958 MB

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        Rate
Acry       2.23%          0.05
Blea       0.00%          0.00
Buil       8.98%          0.03
Epox       5.67%          0.01
Fats       7.06%          3.07
Frag       3.33%          0.58
Gap        4.91%          0.04
Grai      -2.90%         -0.01
HotP       3.10%          0.01
HotT       6.21%          0.00
Inor      13.38%          0.48
Inte       0.00%          0.00
Meta       0.00%          0.00
Othe        #N/A          #N/A
Phos       8.33%          0.02
Plas       0.00%          0.00
Poly       2.42%          0.01
Sili       1.25%          0.07
Solv      -1.46%         -0.06
Spec       4.77%          0.84

Please help.
thanks.

See More: hide and sort columns and rows macro

Report •


#1
November 14, 2012 at 09:34:34
✔ Best Answer

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.


Report •

#2
November 14, 2012 at 22:05:42

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
Next

But instead of .Value I 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.


Report •

#3
November 15, 2012 at 09:54:20

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.


Report •

Related Solutions
See More ↓

#4
November 15, 2012 at 17:10:24

This works perfectly good!

thank you som much for all your help!


:)


Report •

#5
November 22, 2012 at 00:20:16

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

Report •

#6
November 22, 2012 at 02:46:24

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.


Report •

#7
November 25, 2012 at 17:48:28

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 method

when I click on debug it highlights the

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

thanks.


Report •

#8
November 25, 2012 at 19:47:32

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.


Report •

#9
November 25, 2012 at 22:54:09

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 Sub

Thanks :)


Report •

#10
November 26, 2012 at 06:54:12

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.


Report •

#11
November 26, 2012 at 16:50:16

Thanks a lot! :)

Report •

Start a discussion

Ask Question