Hide Zeros

P

PaulOakley

Hello,

I need to run a macro to hide any rows that contain a zero value in a
certain column number. e.g hide all rows that contain a zero value in
column B?

Is this possible do you think?

Thanks in advance

Paul
 
B

Bob Phillips

See response in excel.misc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

RB Smissaert

Sub HideRowsWithZero()

Dim i As Long
Dim lCol As Long

'column to look for zero's
lCol = 1

Application.ScreenUpdating = False

For i = 1 To 100
If Cells(i, lCol) = 0 And _
Not IsEmpty(Cells(i, lCol)) Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS
 
M

Mike Fogleman

This should do it:

Sub HideRows()
Dim LRow As Long
Dim MyRng As Range
Dim c As Range

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRng = Range("B1:B" & LRow)

For Each c In MyRng
If c.Value = "0" Then
c.EntireRow.Hidden = True
End If
Next c
End Sub

Mike F
 
P

PaulOakley

I am having problems getting this to work.

I attach a file which perhaps will help you see my problems.

Thanks in advance

Pau

+-------------------------------------------------------------------
|Filename: Summary.zip
|Download: http://www.excelforum.com/attachment.php?postid=4570
+-------------------------------------------------------------------
 
R

RB Smissaert

Try this one.
If it doesn't work explain what is wrong, rather using attachements.


Sub HideRowsWithZero()

Dim i As Long
Dim lCol As Long
Dim LR As Long

'last row of data
LR = Cells(65536, 1).End(xlUp).Row

'column to look for zero's, in this case column B
lCol = 2

Application.ScreenUpdating = False

For i = 1 To LR
If Cells(i, lCol) = 0 And _
Not IsEmpty(Cells(i, lCol)) Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS
 
P

PaulOakley

Apoligies, thought i it might have been the easiest way to show a
problem.

Let me explain the problem further,

I have changed the style of my sheet to try and accom the macro,

I want the macro to:

Check line 25 for any zero values, if a zero value occurs to hide the
column that corresponds with that row, which could be any column from
B-indefinate.

Does this explain the dilemma any further?
 
R

RB Smissaert

that corresponds with that row ??

You must try to explain a bit more clearly.

RBS
 
P

PaulOakley

Ok, for example if e25=0, it hides column E, if f25=0 it hides colum
F.....
if g25=1 it does not hide..
 
R

RB Smissaert

Well, it will be just the same, except you will swap rows and columns like
this:

Sub HideColumnsWithZero()

Dim i As Long
Dim lRow As Long
Dim LC As Long

'last Column of data
LC = Cells(256).End(xlToLeft).Column

'row to look for zero's, in this case row 25
lRow = 25

Application.ScreenUpdating = False

For i = 1 To LC
If Cells(lRow, i) = 0 And _
Not IsEmpty(Cells(lRow, i)) Then
Cells(i).EntireColumn.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top