PDA

View Full Version : [SOLVED] Sleeper: Help with code to hide row



Phprahl
03-15-2005, 04:28 AM
Hello!

I have a small problem...

In the code below the bold part checks if the row is empty or 0 and if it is empty or 0 the row should be hidden. My problem is that I want it to check both after text and numbers. The formula works great if there is numbers. If there is anything in one of the cells, the row shall not be hidden. In each cell there is a formula that gets the cell value from another sheet.
Can the formula be changed in any way or has the entire code be rewritten?



Sub HideRows()
With Application
.ScreenUpdating = False
.EnableEvents = False
Dim NC%
With Sheets("Table")
NC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
With Range(.Cells(2, NC), .Cells(241, NC))
.Formula = "=SUM(RC2:RC8)<>0"
.Value = .Value
.Replace What:="False", Replacement:=""
On Error Resume Next
.SpecialCells(4).EntireRow.Hidden = 1
Err.Clear
End With
.Columns(NC).Clear
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


/Rgds
Phprahl

mvidas
03-15-2005, 06:40 AM
Hi Phprahl,

You can probably change the .FormulaR1C1 to include counta(rc2:rc8)>0. Also,you don't need the [a1] in the .find as it will default to the top-left most cell in the range if no After is specified:


Sub HideRows()
Dim NC As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
With Sheets("Table")
NC = .Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
With Range(.Cells(2, NC), .Cells(241, NC))
.Formula = "=OR(SUM(RC2:RC8)<>0,COUNTA(RC2:RC8)>0)"
.Value = .Value
.Replace What:="False", Replacement:=""
On Error Resume Next
.SpecialCells(4).EntireRow.Hidden = 1
Err.Clear
End With
.Columns(NC).Clear
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

The only other suggestion I could give is to include a similar .Find to find the last row used, as you have 241 hard coded right now. I thought that may have been intentional, so I didn't add that.
Let me know how it works out!
Matt

Phprahl
03-16-2005, 06:22 AM
Hello Matt,

Thank you for your response to my posting...

I'm not quite getting it to work. The revised code you posted works exactly like the original one when I put "=SUM(RC2:RC8) <>0". It works fine with numbers but not with text. It wont work at all if I run the line you wrote. Can it be because I have a swedish version of excel and that OR and COUNTA is somthing else?

Rgds
/Phprahl

mvidas
03-16-2005, 06:36 AM
I don't believe it would be because of the language difference, but I can't say for sure. The OR function will return TRUE if either or both of the two components is true. The COUNTA>0 will return TRUE if any of the cells in that range is not blank, which is how it would look for the text. I don't know why it didn't work for you, have you tried stepping through the code, and seeing if the OR function returns true or false for a line with text?

Phprahl
03-16-2005, 06:49 AM
Hi again,

How do I step through the code like you explains it? I know how to do it for a formula in a cell but can it be done in the VBA editor?

Rgds
/Phprahl

mvidas
03-16-2005, 06:59 AM
There are two ways to do it, depending on how you start the macro.
If you do it by going to the Macros menu or a button from excel, put the word "Stop" as the first line in your subroutine, and press F8 to step through the code. F8 will run only one line for each keypress.
You can also start the macro using F8, as long as the cursor in VBA is within that subroutine. Each subsequent press of F8 will move the macro along one line at a time.
Matt

Phprahl
03-16-2005, 07:22 AM
Nothing happens...That will say that I can step through the code with no compilation errors.

Where is the code returning TRUE when a row is emty?...In all the empty cells?

As you already understood I'm not very good at VBA:banghead:

mvidas
03-16-2005, 08:01 AM
Each person has their own level of VBA, the fact that you use it at all means you are at least familiar with it, a good thing :)

I suggested stepping through, so when the code passed the .FormulaR1C1 line, you could look at the spreadsheet to see what showed TRUE and what showed FALSE. Both formulae show TRUE when the row (between columns B:H) has a number in it, the formula with COUNTA should show TRUE when any cell is not blank (whether text or number be in the cell).

I'm attaching a simple spreadsheet, to show what each of the different formulae do. In cell B4 I put just the number 1. In B5 I have "a", and nothing in B6:H6.
I used columns I, J, and K to show each formula, and what is returned based on the cells. Your code takes the FALSE cells, turns them into blanks, then deletes those rows.
I am glad to help where you need it, so if any of this is confusing, please don't hesitate to ask for clarification!
Matt

Phprahl
03-17-2005, 12:30 AM
Hi Matt,

Thanks for beeing so understanding...

I think I have figured it out now. The code returns True in every row, despite if I have number or text or "nothing" in the cells, because there is a hard coded formula in every cell that gets its value from other sheets.

Any good ideas?

Rgds
/Phprahl

mvidas
03-17-2005, 06:49 AM
OK, I didn't realize the cells would be blank from a formula. You can change the


.Formula = "=OR(SUM(RC2:RC8)<>0,COUNTA(RC2:RC8)>0)"

to


.Formula = "=OR(SUM(RC2:RC8)<>0,COUNTBLANK(RC2:RC8)<7)"

And that should take care of it! It still treats ="" as blank, whereas counta treats that as non-blank.
Sorry about that!
Matt

Phprahl
03-22-2005, 06:37 AM
Hello again Matt,

I have been forced to do some other work since last time, and have not been able to test your last code until now....:wot

There is still the same problem with the cells that contains a formula. COUNTBLANK is not working because it still recognice that there is a value in the cell even if it is blank.

Any other suggestions?

Rgds
/Phprahl