.activate on hidden sheet

[Guest]

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?

 

[Guest]

Have your code:

1. un-hide the sheet
2. do its thing
3. re-hide the sheet

 

[Dave Peterson]

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.

 

[Guest]

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

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.

 

[Guest]

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

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

 

[Guest]

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

 

[Dave Peterson]

Try:

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

in place of that troublesome line.

 

[Brett Jones]

You can always:

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