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

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Bob" <(E-Mail Removed)> wrote in message
news:76E80FB8-823B-49A6-B118-(E-Mail Removed)...
> 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
>
>
>
>
>
>
>

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sub hiderows()
> with yoursheet
> .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> .print
> .rows.visible=true
> end with
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Bob" <(E-Mail Removed)> wrote in message
> news:76E80FB8-823B-49A6-B118-(E-Mail Removed)...
>> 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
>>
>>
>>
>>
>>
>>
>>
>
>

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
news:A45C8D72-F9D7-4741-B895-(E-Mail Removed)...
> 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
>>
>>
>>
>>
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:Ojs8v%(E-Mail Removed)...
> change to
> Sub hiderows()
> With ActiveSheet
> .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> .PrintPreview
> .Rows.Hidden = False
> End With
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Sub hiderows()
>> with yoursheet
>> .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
>> .print
>> .rows.visible=true
>> end with
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:76E80FB8-823B-49A6-B118-(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
> news:Ojs8v%(E-Mail Removed)...
> > change to
> > Sub hiderows()
> > With ActiveSheet
> > .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> > .PrintPreview
> > .Rows.Hidden = False
> > End With
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > (E-Mail Removed)
> > "Don Guillett" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Sub hiderows()
> >> with yoursheet
> >> .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> >> .print
> >> .rows.visible=true
> >> end with
> >> End Sub
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Bob" <(E-Mail Removed)> wrote in message
> >> news:76E80FB8-823B-49A6-B118-(E-Mail Removed)...
> >>> 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
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>

=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" <(E-Mail Removed)> wrote in message
> > news:Ojs8v%(E-Mail Removed)...
> > > change to
> > > Sub hiderows()
> > > With ActiveSheet
> > > .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> > > .PrintPreview
> > > .Rows.Hidden = False
> > > End With
> > > End Sub
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > (E-Mail Removed)
> > > "Don Guillett" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> Sub hiderows()
> > >> with yoursheet
> > >> .Columns(1).SpecialCells(xlBlanks).EntireRow.Hidden = True
> > >> .print
> > >> .rows.visible=true
> > >> end with
> > >> End Sub
> > >>
> > >> --
> > >> Don Guillett
> > >> SalesAid Software
> > >> (E-Mail Removed)
> > >> "Bob" <(E-Mail Removed)> wrote in message
> > >> news:76E80FB8-823B-49A6-B118-(E-Mail Removed)...
> > >>> 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
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>
> > >
> > >
> >
> >
> >