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
, "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?