Hiding rows

[datafiend]

Hi there
I've got some data in a spreadsheet in cells on different rows between Row 1 and 300 which is either letters, a zero or is empty. I'm trying to write a macro that will examine each row and if it finds only zeroes or empty cells, will hide the row, otherwise, leave it unhidden. I can get it to hide all rows, no rows but not some rows,

Is anyone able to suggest some code I might use?

Thanks very much.

[Marcol]

Have you tried AutoFilter > (Non-Blanks) ?

[Iswarya]

Hi there,
You can use IsEmpty(rowNumber) in your code to determine if a row is empty or not and then hide it.

[datafiend]

Thanks for your suggestions. I wasn't able to make them work but perhaps I didn't explain the situation clearly enough. I've also done more hunting around and come up with the following.

I have Workbook 1 which is updated with data on worksheet (“PP”) between Rows 6 to 100 and columns B:AO from Workbooks 2, 3 & 4.

Data may appear in any cell in any row. The formula for updating Workbook 1 produces a zero when there is no data. People look at the combined data in Workbook 1 and I want to hide all the rows on which there is no data. The code below seems to work to do this but it seems a very long and roundabout way of doing it.

Can anyone suggest a more elegant code to achieve my aim.

Dim rngCell As Range
Dim rng As Range

Set rng = Sheets("PP").Range("a6:a100")

For Each rngCell In rng.Rows
If Trim(rngCell.Offset(, 0) & rngCell.Offset(, 1) & rngCell.Offset(, 2)  _
 & rngCell.Offset(, 3) & rngCell.Offset(, 4) & rngCell.Offset(, 5) & rngCell.Offset(, 6)  & rngCell.Offset(, 7)  ….etc until Column AO = 0 Then
rngCell.EntireRow.Hidden = True
End If
Next rngCell

Thanks for any assistance anyone can offer.

[wamp]

one way is a search-replace for cells with "0"

Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

...and then do an isempty(rownumber)

[datafiend]

Hi Wamp
Thanks very much for your suggestion but I don't want to replace the zeroes with "" otherwise I presume I'll lose the formulae the cells contain.

Are there any other ways I can achieve what I'm trying to do???

[wamp]

this code will scan ALL cells in range B6:AO100 and hide all rows that contain nothing but zero's.
see comments in code for help.

Sub Macro1()


Dim rng As Range
Dim activerow As Integer
activerow = 5  ' set the starting row to scan minus one. (eg. this will start at row 6)


nxt:   'start of loop

If activerow = 100 Then Exit Sub  ' end this macro when scanned to row numer 100
activerow = activerow + 1 ' set next row
Set rng = Sheets("PP").Range("B" & activerow & ":AO" & activerow)  ' set the columns of the range in this codeline


For Each cell In rng  ' for each cell in range B:AO(and activerow)
If cell.Value <> "0" Then GoTo nxt ' check if the cell value is different than zero. if it is, stop scanning and go to next row
Next
' if all columns in activerow contained "0" then execute code here
Rows(activerow).EntireRow.Hidden = True ' for example hide this row
GoTo nxt
End Sub

...
i assume you may want to change the line

If cell.Value <> "0" Then GoTo nxt

to

If cell.Value <> "0" or cell.value <> vbnullstring Then GoTo nxt

to scan for both zero's and empty cells.. - you'll have to play a bit with it, to see what suits you. :-)

[datafiend]

Hey Wamp
Thanks very much for taking the time to explain so clearly. Code works fine though the part with vbNullString doesn't hide rows with nothing in them but that doesn't matter as the cells in every row will have a formula in them.

Your help is very much appreciated as I've spent hours trying to work it out myself.

Datafiend

[wamp]

you could also try

If cell.Value <> "0" or cell.value <> "" Then GoTo nxt

By doing that, it doesn't really matter if the cells has formulas, as it looks for the -value- of the formula, which I assume is either 0 or empty.

_______________________________________________
Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of their post to show your appreciation.