delete contents of visible cells only

[michael.beckinsale]

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 A110 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("A110")

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

 

[Guest]

Sub testdelete()

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

Set rng = Sheet1.Range("A110")

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:

 

[Norman Jones]

Hi Michael,

Try:

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

 

[michael.beckinsale]

Stefi / Norman,

Many thanks, both work fine.

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

Regards

Michael beckinsale

 

[Norman Jones]

Hi Michael,

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

 

[Norman Jones]

Hi Michael,

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

 

[michael.beckinsale]

Norman,

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

 

[Norman Jones]

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("A14000").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.

 

[Norman Jones]

Hi Michael,

Replace that code with:

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

Set rng1 = Range("A14000") '<<==== 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
'<<=============

 

[michael.beckinsale]

Norman,

Thats great. All your input is gratefully appreciated.

Regards

Michael Beckinsale

 

[Tim Marsh]

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

 

[Norman Jones]

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("A110").SpecialCells(xlCellTypeVisible).ClearContents ?

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

 

[Tim Marsh]

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