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 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]

> 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
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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.


---
Regards,
Norman



"michael.beckinsale" <[email protected]> wrote in message
news:[email protected]...
>
> Stefi / Norman,
>
> Many thanks, both work fine.
>
> In terms of speed / efficiency which code is the best?
>
> Regards
>
> Michael beckinsale
>

[Norman Jones]

Hi Michael,

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


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> 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]

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("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" <[email protected]> wrote in message
news:[email protected]...
>
> Norman,
>
> Thanks. I have approx 4000 cells in the range so l will go with
> Cells.special method
>

[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("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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Norman,
>>
>> Thanks. I have approx 4000 cells in the range so l will go with
>> Cells.special method
>>
>
>

[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


"michael.beckinsale" <[email protected]> wrote in message
news:[email protected]...
>
> Norman,
>
> Thats great. All your input is gratefully appreciated.
>
> Regards
>
> Michael Beckinsale
>

[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("A1:D10").SpecialCells(xlCellTypeVisible).ClearContents ?

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


---
Regards,
Norman

[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


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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).ClearContents ?
>
> Except, perhaps, that the latter additionally defines the range and
> deletes the contents.
>
>
> ---
> Regards,
> Norman
>