Hi All,

I would like to loop thru a range of cells and delete the contents of
the visible cells only.

eg if the range is A1:D10 and column B is hidden all the cells will be
deleted except B1:B10

I tried the following but the code fails where indicated.

Sub testdelete()

Dim rng As Range
Dim mycell

Set rng = Sheet1.Range("A1:D10")

For Each mycell In rng
If mycell.Hidden = False Then fails here
mycell.Delete
End If
Next mycell

End Sub

Can anybody help please?

Regards

Michael Beckinsale

Sub testdelete()

Dim rng As Range
Dim mycell As Range '<--FIXED

Set rng = Sheet1.Range("A1:D10")

For Each mycell In rng
If mycell.EntireColumn.Hidden = False Then '<--FIXED
mycell.Clearcontents '<--FIXED
End If
Next mycell

End Sub

Regards,
Stefi


€žmichael.beckinsale€� ezt Ã*rta:

Hi All,

I would like to loop thru a range of cells and delete the contents of
the visible cells only.

eg if the range is A1:D10 and column B is hidden all the cells will be
deleted except B1:B10

I tried the following but the code fails where indicated.

Sub testdelete()

Dim rng As Range
Dim mycell

Set rng = Sheet1.Range("A1:D10")

For Each mycell In rng
If mycell.Hidden = False Then fails here
mycell.Delete
End If
Next mycell

End Sub

Can anybody help please?

Regards

Michael Beckinsale

Hi Michael,

Try:

'=============
Public Sub Tester()
On Error Resume Next
Range("A1:D10").SpecialCells(xlCellTypeVisible). _
ClearContents
On Error GoTo 0
End Sub
'<<=============


---
Regards,
Norman



"michael.beckinsale" wrote in message
oups.com...
Hi All,

I would like to loop thru a range of cells and delete the contents of
the visible cells only.

eg if the range is A1:D10 and column B is hidden all the cells will be
deleted except B1:B10

I tried the following but the code fails where indicated.

Sub testdelete()

Dim rng As Range
Dim mycell

Set rng = Sheet1.Range("A1:D10")

For Each mycell In rng
If mycell.Hidden = False Then fails here
mycell.Delete
End If
Next mycell

End Sub

Can anybody help please?

Regards

Michael Beckinsale

Stefi / Norman,

Many thanks, both work fine.

In terms of speed / efficiency which code is the best?

Regards

Michael beckinsale

Hi Michael,

Except for ranges with a very large number of non-contiguous visible cells,
I would use the SpecialCells method.


---
Regards,
Norman



"michael.beckinsale" wrote in message
oups.com...

Stefi / Norman,

Many thanks, both work fine.

In terms of speed / efficiency which code is the best?

Regards

Michael beckinsale

Hi Michael,

Just to add, in the case of small ranges, I would not anticipate any
perceptible difference in speed.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Michael,

Except for ranges with a very large number of non-contiguous visible
cells, I would use the SpecialCells method.


---
Regards,
Norman

Norman,

Thanks. I have approx 4000 cells in the range so l will go with
Cells.special method