I want to use the code below from a userform to perform the actions on a
hidden sheet. I get an error message becuase the sheet is hidden. How can I
re-write this code and not use the range.activate or range.select method?
--
Thanks
Shawn

Have your code:

1. un-hide the sheet
2. do its thing
3. re-hide the sheet
--
Gary''s Student - gsnu200730

Depending on what you're doing, you can work directly on that worksheet:

dim HWks as worksheet
set Hwks = worksheets("hiddensheetnamehere")
hwks.range("a1").value = "hi there"

But maybe more info about what you want to do would be better.



Shawn wrote:
>
> I want to use the code below from a userform to perform the actions on a
> hidden sheet. I get an error message becuase the sheet is hidden. How can I
> re-write this code and not use the range.activate or range.select method?
> --
> Thanks
> Shawn

--

Dave Peterson

I would rather not unhide the sheet. I forgot to paste the code. Here is my
code

WSSD.Range("A10").End(xlDown).Activate
Do Until ActiveCell.Value = ""
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-3, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-4, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-5, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-6, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-7, 0).Value Or _
ActiveCell.Value = ActiveCell.Offset(-8, 0).Value Then
ActiveCell.Value = ""
ActiveCell.Offset(-1, 0).Activate
Loop

--
Thanks
Shawn


"Dave Peterson" wrote:

> Depending on what you're doing, you can work directly on that worksheet:
>
> dim HWks as worksheet
> set Hwks = worksheets("hiddensheetnamehere")
> hwks.range("a1").value = "hi there"
>
> But maybe more info about what you want to do would be better.
>
>
>
> Shawn wrote:
> >
> > I want to use the code below from a userform to perform the actions on a
> > hidden sheet. I get an error message becuase the sheet is hidden. How can I
> > re-write this code and not use the range.activate or range.select method?
> > --
> > Thanks
> > Shawn
>
> --
>
> Dave Peterson
>

Try something like this:

Dim rng as Range, rng1 as Range, i as Long
With WSSD
set rng = .Range(.Cells(10,1),.Cells(10,1).End(xldown))

for i = rng(rng.count).row to 10 step -1
rng1 = .cells(i - 8,1).Resize(8,1)
if application.countif(rng1,.Cells(i,1)) > 0 then
.cells(i,1).ClearContents
end if
Next
End With

--
Regards,
Tom Ogilvy


"Shawn" wrote:

> I would rather not unhide the sheet. I forgot to paste the code. Here is my
> code
>
> WSSD.Range("A10").End(xlDown).Activate
> Do Until ActiveCell.Value = ""
> If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-2, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-3, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-4, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-5, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-6, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-7, 0).Value Or _
> ActiveCell.Value = ActiveCell.Offset(-8, 0).Value Then
> ActiveCell.Value = ""
> ActiveCell.Offset(-1, 0).Activate
> Loop
>
> --
> Thanks
> Shawn
>
>
> "Dave Peterson" wrote:
>
> > Depending on what you're doing, you can work directly on that worksheet:
> >
> > dim HWks as worksheet
> > set Hwks = worksheets("hiddensheetnamehere")
> > hwks.range("a1").value = "hi there"
> >
> > But maybe more info about what you want to do would be better.
> >
> >
> >
> > Shawn wrote:
> > >
> > > I want to use the code below from a userform to perform the actions on a
> > > hidden sheet. I get an error message becuase the sheet is hidden. How can I
> > > re-write this code and not use the range.activate or range.select method?
> > > --
> > > Thanks
> > > Shawn
> >
> > --
> >
> > Dave Peterson
> >

See error below



Dim rng as Range, rng1 as Range, i as Long
With WSSD
set rng = .Range(.Cells(10,1),.Cells(10,1).End(xldown))

for i = rng(rng.count).row to 10 step -1
rng1 = .cells(i - 8,1).Resize(8,1) <<<< runtime error 91 right here
if application.countif(rng1,.Cells(i,1)) > 0 then
.cells(i,1).ClearContents
end if
Next
> End With


--
Thanks
Shawn

Try:

Set rng1 = .Cells(i - 8, 1).Resize(8, 1)

in place of that troublesome line.

Shawn wrote:
>
> See error below
>
> Dim rng as Range, rng1 as Range, i as Long
> With WSSD
> set rng = .Range(.Cells(10,1),.Cells(10,1).End(xldown))
>
> for i = rng(rng.count).row to 10 step -1
> rng1 = .cells(i - 8,1).Resize(8,1) <<<< runtime error 91 right here
> if application.countif(rng1,.Cells(i,1)) > 0 then
> .cells(i,1).ClearContents
> end if
> Next
> > End With
>
> --
> Thanks
> Shawn

--

Dave Peterson

You can always:

Application.screenupdating = false
..
..
Do your thing
..
..
Application.screenupdating = true




On 6/17/07 4:58 AM, in article
418FEDD4-7396-407A-8395-(E-Mail Removed), "Shawn"
<(E-Mail Removed)> wrote:

> I want to use the code below from a userform to perform the actions on a
> hidden sheet. I get an error message becuase the sheet is hidden. How can I
> re-write this code and not use the range.activate or range.select method?