Solved Need Macro to Hide Rows

May 14, 2010 at 07:33:43
Specs: Win 2003, 512

Hi,

I have a spreadsheet with Columns, G to S, where if in a Row all the values in these columns are Zero to hide this Row. Is it possible to set up a Macro so to click a button and all rows with Zeros are hidden.

I will be adding Columns and Rows going forward, would it be easy to update the Macro accordingly?

Much appreciated,
Kasey


See More: Need Macro to Hide Rows

Report •


#1
May 14, 2010 at 11:53:34

Will the zeros really be zeros or do you want to hide empty cells also?

Are the zeros the result of a formula?

If SUM(Gx:Sx) evaluated to zero, would that be the kind of Row you would want to hide?

Is there any column in your spreadsheet that contains data down to bottom of the range you want to check and hide?

If you want to be able to run the code, unmodified, even after you add or delete rows, it would be nice if you had a column that the macro could use to determine how long the range is each time it is run.


Report •

#2
May 17, 2010 at 02:20:34

Hi Derby,

1.Yes, the zeros will be zeros there wont be any empty cells.

2.Yes they are a formula.

3.No, as the columns are not SUMed in that way, only each cell which contains the formula (see 2)

4. All columns are of the same length searching on which Customers have bought which product.

An example of my spreadsheet -

Each column G to S is a customer and the rows are products, say orange, apples etc. If all customers have not bought oranges then I would like to hide the oranges row. If 1 Customer bought apples then this row would remain visible ...and so on. Products and Customers will be added going forward.

I hope this answers your questions.

Thanks


Report •

#3
May 17, 2010 at 04:40:24
✔ Best Answer

re: 3.No, as the columns are not SUMed in that way, only each cell which contains the formula (see 2)

You seemed to have misinterpreted my question. The purpose of the question about SUMing Gx:Sx was to see if that could be used as a test to determine if all cells in Gx:Sx were zero. That's quicker than testing each individual cell. See the comments included in the code below.

I'm making 3 assumptions with this code:

1 - Your data is in Sheet1.
2 - Row 1 contains your Customer names and won't ever be hidden.
3 - Column A contains your products and can be used to determine the length (last row) of data.

BTW...You didn't anything about unhiding the rows, so I'm assuming that you want rows to be unhidden if a formula places a value in any given row.

The code below first unhides all rows then hides those that have 0 in G:S

Try this:

Sub HideZeroRows()
Dim lstRw, nxtRw As Integer
'Disable Screenupdating
 Application.ScreenUpdating = False
'Determine last Row that contains data:
  With Sheets(1)
   lstRw = .Range("A" & Rows.Count).End(xlUp).Row
'Unhide all rows
   .Rows("2:" & lstRw).Hidden = False
'Loop through rows
     For nxtRw = 2 To lstRw
'Hide rows where G:S Sum to 0
      If WorksheetFunction.Sum(.Range("G" & nxtRw & ":S" & nxtRw)) = 0 Then
        .Rows(nxtRw).Hidden = True
      End If
     Next
  End With
'Enable Screenupdating
 Application.ScreenUpdating = True
 End Sub



Report •

Related Solutions

#4
May 17, 2010 at 06:07:15

Wonderful, works perfectly! Thank you so much Derby!

Report •

#5
July 8, 2010 at 18:31:38

This is very close to solving a problem that I have except that there are 3 columns K, M & N and are obviously not a range.

Any assistance would be appreciated in modifying the original code


Report •

#6
July 8, 2010 at 19:11:33

re: columns K, M & N and are obviously not a range.

I'm not sure what you mean by "are obviously not a range", but here's the original code modified to hide rows where the cells in the same row in Columns K, M and N SUM to 0.

As before, the code uses the data in Column A to determine the number of rows for the macro to check.

Option Explicit
Sub HideZeroRows()
Dim lstRw, nxtRw As Integer
'Disable Screenupdating
 Application.ScreenUpdating = False
'Determine last Row that contains data:
  With Sheets(1)
   lstRw = .Range("A" & Rows.Count).End(xlUp).Row
'Unhide all rows
   .Rows("2:" & lstRw).Hidden = False
'Loop through rows
     For nxtRw = 2 To lstRw
'Hide rows where G:S Sum to 0
      If WorksheetFunction.Sum(.Range("K" & nxtRw), _
                               .Range("M" & nxtRw), _
                               .Range("N" & nxtRw)) = 0 Then
        .Rows(nxtRw).Hidden = True
      End If
     Next
  End With
'Enable Screenupdating
 Application.ScreenUpdating = True
 End Sub


Report •

#7
July 8, 2010 at 19:45:19

Wow, that was fast. It works a treat. Thank you very much for your help.

Report •

#8
July 18, 2010 at 23:55:34

I have a similar issue but want to delete the rows using the same criteria as before instead of hiding them. I tried to modify the code provided but couldn't get it to work. Any help would be appreciated.

Thanks


Report •

#9
July 19, 2010 at 03:56:41

Replace
.Rows(nxtRw).Hidden = True

With
.Rows(nxtRw).Delete


Report •

#10
July 19, 2010 at 05:54:39

It seems so easy when you do it..... Thanks again

Report •

#11
July 19, 2010 at 07:08:50

I'd be very surprised if that simple change worked consistently.

When you loop through a range of rows and delete any of them, you screw up the For-Next loop.

For example, this code will not delete rows 1 through 10:

Sub DelTest
 For myRow = 1 to 10
  Rows(myRow).Delete
 Next
End Sub

Once you delete Row 1, Row 2 will move up to take it's spot. The loop will then increment and tell VBA to delete Row 2, leaving the old Row 2 in Row 1's spot. This will continue throughout the loop so that you will only delete every other row.

Therefore, in your case, when you find a row that matches your deletion criteria, it will indeed delete that row, but it will also move all of the others rows up. If the very next row also meets your deletion criteria, it will not get deleted because the Loop counter will have incremented past that row.

In order to not miss any rows when deleting them, you have to loop through the range in reverse order.

This code will delete rows 1 through 10.

Sub DelTest
 For myRow = 10 to 1 Step -1
  Rows(myRow).Delete
 Next
End Sub


Report •

#12
August 3, 2010 at 19:27:32

Thanks for that I missed your follow up and have just encountered the problem you envisaged.

I have used your suggestion and it works fine. Thanks for your follow-up, much appreciated.


Report •

#13
August 3, 2010 at 20:07:11

Don't take this the wrong way but I'm glad you ran into the problem.

To be more accurate, I'm glad you ran into the problem and realized it.

Depending on the size of the file, you might not have noticed the missed lines which might have created an issue downstream if you assumed that every line that should have been deleted had been.

Just remember to keep that "trick" in your back pocket for the next time you have to delete rows.


Report •

#14
October 14, 2010 at 10:18:39

What is my data starts in row 8? I have a few rows to use as a header

Report •

#15
October 14, 2010 at 11:32:45

This will delete rows 8, 9 & 10

Sub DelTest
 For myRow = 10 to 8 Step -1
  Rows(myRow).Delete
 Next
End Sub



Report •

#16
October 15, 2010 at 12:49:15

But I want to keep the rows... I want the hide macro to begin in row 10

Report •

#17
October 15, 2010 at 13:35:09

also i need to be able to select which worksheets it is applied to

Report •

#18
October 15, 2010 at 13:42:52

There are 2 different macros in this thread that Hide rows, with each one based on a different criteria.

I don't know which one you plan to use, but in any case, replace the 2 with a 10 and you should be good to go. Just be aware that the 2 appears twice in both macros and both instances should be changed.


Report •

#19
October 15, 2010 at 15:17:11

Thanks for the help. I can't get it to work :/ works on a random spreadsheet in the book and sometimes hides rows with data...

Thanks for trying!


Report •

#20
October 15, 2010 at 20:24:14

Don't take this the wrong way, but my assumption is that you can't get the code "to work" because you don't understand what the code is supposed to be doing.

Both pieces of code were written to address specific requirements posted by other members of this forum. Both pieces of code check specific ranges for specific values and hide specific rows based on what values are found.

You never posted any requirements other than "I want the hide macro to begin in row 10". I explained what changes had to be made in order for the code to do just that. However, unless you want the code to hide the rows that meet the specific conditions it was written for, it's going to appear to not work, when in reality, it works exactly as it's supposed to.

Look at Responses # 4 and #12. The code works just fine for them because it matches the requirements they posted.

Unless you tell us under what specific conditions you want your rows to be hidden, we can't be of much more help.


Report •

#21
October 21, 2010 at 13:40:35

Derby, thanks for your help. I was hoping that I could take the macro and understand it enough to modify for my needs. But unfortunately, I'm not a programmer and got a little lost. I did find another macro that works for me. :) Adding it here in case anyone else comes looking for it:

My requirement was to hide rows if values in 4 columns equaled zero. I created a column (L) to sum those 4 and than applied the macro to the sum column. You can change it to apply to whatever range you need by modifying: Range("L10:L252") in the code below. Works great if you are presenting an excel model and want to hide rows that did not have any data for the period.


Sub Hide_Rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("L10:L252")
For Each i In RngCol
i.EntireRow.Hidden = False
If i.Value = 0 Then _
i.EntireRow.Hidden = True
Next i
End Sub


Report •


Ask Question