delete contents of visible cells only
 

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

delete contents of visible cells only
 

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

delete contents of visible cells only
 

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

delete contents of visible cells only
 

Stefi / Norman,

Many thanks, both work fine.

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

Regards

Michael beckinsale

delete contents of visible cells only
 

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

delete contents of visible cells only
 

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

delete contents of visible cells only
 

Norman,

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

delete contents of visible cells only
 

Hi Michael,

As far as the suggested SpecialCells method is concerned, a critical poinr
might be reached with 8192+ non-contiguous areas, which would coorespond
with a minimum of 16384 (= 8192*2) cells. - see the Microsoft KnowlegeBase
Article # 83229:

http://support.microsoft.com/kb/832293/en-us

Given your range, this should not present a problem.

However, the suggested code could be made more efficient by resticting the
area of interest to the first column of your range, i.e.:

'=============
Public Sub Tester2()
Dim CalcMode As Long

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
On Error Resume Next
Range("A1:D4000").Columns(1).SpecialCells _
(xlCellTypeVisible).EntireRow.ClearContents
On Error GoTo 0

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
'<<=============

Note that to enhance speed, the suggested code turns off, and later
restores, screen refreshing and automatic calculation.


---
Regards,
Norman



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

Norman,

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

delete contents of visible cells only
 

Hi Michael,

Replace that code with:

'=============
Public Sub Tester2()
Dim rng1 As Range, rng2 As Range
Dim CalcMode As Long

Set rng1 = Range("A1:D4000") '<<==== CHANGE

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
On Error Resume Next
Set rng2 = rng1.Columns(1).SpecialCells _
(xlCellTypeVisible)
Intersect(rng2.EntireRow, rng1.EntireColumn).ClearContents
On Error GoTo 0

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
'<<=============

--
---
Regards,
Norman



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

As far as the suggested SpecialCells method is concerned, a critical
poinr might be reached with 8192+ non-contiguous areas, which would
coorespond with a minimum of 16384 (= 8192*2) cells. - see the Microsoft
KnowlegeBase Article # 83229:

http://support.microsoft.com/kb/832293/en-us

Given your range, this should not present a problem.

However, the suggested code could be made more efficient by resticting the
area of interest to the first column of your range, i.e.:

'=============
Public Sub Tester2()
Dim CalcMode As Long

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
On Error Resume Next
Range("A1:D4000").Columns(1).SpecialCells _
(xlCellTypeVisible).EntireRow.ClearContents
On Error GoTo 0

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
'<<=============

Note that to enhance speed, the suggested code turns off, and later
restores, screen refreshing and automatic calculation.


---
Regards,
Norman



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

Norman,

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

delete contents of visible cells only
 

Norman,

Thats great. All your input is gratefully appreciated.

Regards

Michael Beckinsale

delete contents of visible cells only
 

is this a regular thing or a one-off?

if its a one-off, would it not be easier to use
Edit Goto Special Visible cells only

then delete the selected cells?

hth,

tim


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

Norman,

Thats great. All your input is gratefully appreciated.

Regards

Michael Beckinsale

delete contents of visible cells only
 

Hi Tim,

if its a one-off, would it not be easier to use
Edit Goto Special Visible cells only

If this were a one-off operation, I doubt that Michael would be seeking a
programmatic solution and it is even less likely that he would be concerned
with the speed and efficiency advantages of one coded approach over another.
Perhaps, most telling of all, however, was the decision to post the question
in the programming group.

As this is a programming group, how does:

Edit Goto Special Visible cells only

differ from the initially advocated:

Range("A1:D10").SpecialCells(xlCellTypeVisible).Cl earContents ?

Except, perhaps, that the latter additionally defines the range and deletes
the contents.


---
Regards,
Norman

delete contents of visible cells only
 

Hi Norman,

I agree with everything you've said. However, I've discovered on several
occasions I have found peoples problems to be misinterpreted (because they
fail to explain a vital bit of information) or tackled with VB because (as
you say) its been posted in a programming newsgroup (which is the natural
place to post the message when you *think* the solution must use VB).

I know this to be the case because i am one of those people that has asked
for a VBA solution to something that could have been achevied from just a
few menu options; I'm not an Excel novice, but there are still some areas
that i don't use very often, so i never think to use them and I would
always choose a built in solution over having to create VBA to do the same
job (in the right circumstances).

There is nothing wrong with the code you provided, I'm merely suggesting an
alternative that Michael may not have known existed.

Having said all that, when i inevitably post a message looking for help, I
would very thankful for any VBA code snippets you could provide! I think
this is the most amazingly useful newsgroup i have ever seen.

Regards,

Tim


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

if its a one-off, would it not be easier to use
Edit Goto Special Visible cells only

If this were a one-off operation, I doubt that Michael would be seeking a
programmatic solution and it is even less likely that he would be
concerned with the speed and efficiency advantages of one coded approach
over another. Perhaps, most telling of all, however, was the decision to
post the question in the programming group.

As this is a programming group, how does:

Edit Goto Special Visible cells only

differ from the initially advocated:

Range("A1:D10").SpecialCells(xlCellTypeVisible).Cl earContents ?

Except, perhaps, that the latter additionally defines the range and
deletes the contents.


---
Regards,
Norman