Code Does Not Work Depending On Formula
 

If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

Sub hiderows()
with yoursheet
..Columns(1).SpecialCells(xlBlanks).EntireRow.Hidd en = True
..print
..rows.visible=true
end with
End Sub

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

change to
Sub hiderows()
With ActiveSheet
..Columns(1).SpecialCells(xlBlanks).EntireRow.Hidd en = True
..PrintPreview
..Rows.Hidden = False
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Sub hiderows()
with yoursheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.print
.rows.visible=true
end with
End Sub

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

If there is a formula in the cell, it is not empty and won't pass your test.


In fact,
=sheet2!D22 will return a zero if there is nothing in D22 of Sheet2.

Just to illustrate in the immediate window:

? Activecell.Formula
=Sheet2!D22
? Activecell.Value
0
? isempty(activecell)
False
? isempty(Range("sheet2!D22"))
True

So the test would have to be designed to account for the actual situation.


--
Regards,
Tom Ogilvy



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Because when you use the IF() statement you are actually assigning the
value
of "" (a zero length string) to the cell in column A. But when you just
use
=Sheet2!D22 and nothing is in Sheet2!D22, then there is nothing in the one
in
column A. Technically both cells are Empty - and there is a difference
between empty and containing a zero length string.

I think (haven't tested this) that if you use the
=Sheet2!D22
formula in Column A of Sheet1 then if you change your test in the VB code
from
If .Cells(rw,"A").Value = "" Then _
to
If IsEmpty(.Cells(rw, "A")) Then _

you'll get the results you are expecting. It's either that or leave the
code the way it is and continue using the IF() statement for entries in
column A.

"Bob" wrote:

If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

For the OP,
This wouldn't work for either of the formulas you show as being in column A.

A cell containing a formula is not considered blank.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
change to
Sub hiderows()
With ActiveSheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.PrintPreview
.Rows.Hidden = False
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Sub hiderows()
with yoursheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.print
.rows.visible=true
end with
End Sub

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

Everyone:
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works the other suggestions don't.

The problem is this, In sheet "Production" I am actually using:
Column A Column B,C,D,E,F
=PreProduction!$H130 =IF($A130,Forecast!$B130,0)

I am doing that because if I use:
=IF(PreProduction!H22,PreProduction!D22,"")
In column A - Column B,C,D,E,F will have an error.
But the code will work.

Are there any other suggestions?

Bob






"Tom Ogilvy" wrote:

For the OP,
This wouldn't work for either of the formulas you show as being in column A.

A cell containing a formula is not considered blank.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
change to
Sub hiderows()
With ActiveSheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.PrintPreview
.Rows.Hidden = False
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Sub hiderows()
with yoursheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.print
.rows.visible=true
end with
End Sub

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

Code Does Not Work Depending On Formula
 

=ISBLANK(Sheet2!A8)

will test if the source cell (sheet2!A8 in the example) is blank. Perhaps
you can use this knowledge to craft a solution.

--
Regards,
Tom Ogilvy


"Bob" wrote:

Everyone:
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works the other suggestions don't.

The problem is this, In sheet "Production" I am actually using:
Column A Column B,C,D,E,F
=PreProduction!$H130 =IF($A130,Forecast!$B130,0)

I am doing that because if I use:
=IF(PreProduction!H22,PreProduction!D22,"")
In column A - Column B,C,D,E,F will have an error.
But the code will work.

Are there any other suggestions?

Bob






"Tom Ogilvy" wrote:

For the OP,
This wouldn't work for either of the formulas you show as being in column A.

A cell containing a formula is not considered blank.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
change to
Sub hiderows()
With ActiveSheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.PrintPreview
.Rows.Hidden = False
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Sub hiderows()
with yoursheet
.Columns(1).SpecialCells(xlBlanks).EntireRow.Hidde n = True
.print
.rows.visible=true
end with
End Sub

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
If I use: =IF(Sheet2!D22,Sheet2!D22,"") in column A (with no value)
The code below works.
IfI use: =Sheet2!D22 in column A (with no value)
The code does not work.
Why?
Thanks,
Bob

Private Sub CommandButton1_Click()
Dim rw As Long
Application.ScreenUpdating = False
'Me is the object that owns the code
'in this case Sheet1
With Me 'Sheets("Sheet1")
For rw = 1 To 30
If .Cells(rw, "A").Value = "" Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub