Excel and clear cells....

  • Thread starter Thread starter Øyvind Granberg
  • Start date Start date
Ø

Øyvind Granberg

Hi....

I have this large spreadsheet showing 52 weeks, and for each week 10 areas
not adjacent to each other, all of wich I need to be cleared every year. In
between areas needed to be cleared there are some hidden cells, rows and
columns.

Is there a way to easily clear these areas, without clearing hidden and
locked areas?



--
 
You can highlight disjointed areas of cells using the Ctrl key while
you click and drag.

For example, click cell A1 and drag to cell B2 to highlight 4 cells.
Now hold down the Ctrl key and click cell A8 and drag to cell B9. You
now have 8 cells selected. If you press the Del or Delete key, you'll
clear the contents of all 8 cells.

You can write a macro to do this, then attach the macro to a certain
keyboard action (Ctrl-D, for example). Record the macro and you'll
see the syntax for everything. If necessary, change the code to
select the cell relative to the position of your cell selection at the
time you run the macro, if each of your 52 blocks need to be cleared
separately.

If you can be more specific about the structure of your worksheet, or
e-mail it to me (tsides at intelligentsystemsconsulting dotcom) , I
might have time to post the necessary macro code.

ALTERNATIVE: If cells are hidden because they are helper cells, then
move them somewhere out of the way so that your important areas are
not disjointed. However, if they are for use by some people but not
others, then I could understand that they may need to stay where they
are.
 
It is possible to assign one range name to a multi-area range. Then you
could Goto (F5) that range and press Del.

It is not always easy to apply one name are a large number of areas so, as
an alternative, you could assign a sequence of names to each area like
ClearRg1, ClearRg2, etc., and then clear them with a macro:

Sub ClearInputRange()
Dim Counter As Integer
For Counter = 1 To 10
Range("ClearRg" & Counter).ClearContents
Next
End Sub


--
Jim
| Hi....
|
| I have this large spreadsheet showing 52 weeks, and for each week 10 areas
| not adjacent to each other, all of wich I need to be cleared every year.
In
| between areas needed to be cleared there are some hidden cells, rows and
| columns.
|
| Is there a way to easily clear these areas, without clearing hidden and
| locked areas?
|
|
|
| --
| ---
| rgs
| Zadig Galbaras
| (nick)
| www.tresfjording.com
|
 
I used this ove and it work fine, slowly, very slowly, but fine!


Dim ws As Worksheet
Dim cl As Range

For Each ws In ActiveWorkbook.Sheets
For Each cl In Sheets(ws.Name).UsedRange.Cells
If cl.Locked = False Then
cl.ClearContents
End If
Next cl
Next ws


you find more at http://www.mrexcel.com/forum/showthread.php?t=321440

Google is a much, much better F1-option than the embedded one in Excel.
When I press F1 and type in words, it never happens that what I am looking
for is popping up on the first page.
MS has really messed this up!

But problem solved....

--
 
slowly, very slowly

Yes that's not the way I'd go but to each his own.

--
Jim
|I used this ove and it work fine, slowly, very slowly, but fine!
|
|
| Dim ws As Worksheet
| Dim cl As Range
|
| For Each ws In ActiveWorkbook.Sheets
| For Each cl In Sheets(ws.Name).UsedRange.Cells
| If cl.Locked = False Then
| cl.ClearContents
| End If
| Next cl
| Next ws
|
|
| you find more at http://www.mrexcel.com/forum/showthread.php?t=321440
|
| Google is a much, much better F1-option than the embedded one in Excel.
| When I press F1 and type in words, it never happens that what I am looking
| for is popping up on the first page.
| MS has really messed this up!
|
| But problem solved....
|
| --
| ---
| rgs
| Zadig Galbaras
| (nick)
| www.tresfjording.com
|
| "Jim Rech" <[email protected]> skrev i nyhetsmeldingen:
| [email protected] ...
| > It is possible to assign one range name to a multi-area range. Then you
| > could Goto (F5) that range and press Del.
| >
| > It is not always easy to apply one name are a large number of areas so,
as
| > an alternative, you could assign a sequence of names to each area like
| > ClearRg1, ClearRg2, etc., and then clear them with a macro:
| >
| > Sub ClearInputRange()
| > Dim Counter As Integer
| > For Counter = 1 To 10
| > Range("ClearRg" & Counter).ClearContents
| > Next
| > End Sub
| >
| >
| > --
| > Jim
| > | > | Hi....
| > |
| > | I have this large spreadsheet showing 52 weeks, and for each week 10
| > areas
| > | not adjacent to each other, all of wich I need to be cleared every
year.
| > In
| > | between areas needed to be cleared there are some hidden cells, rows
and
| > | columns.
| > |
| > | Is there a way to easily clear these areas, without clearing hidden
and
| > | locked areas?
| > |
| > |
| > |
| > | --
| > | ---
| > | rgs
| > | Zadig Galbaras
| > | (nick)
| > | www.tresfjording.com
| > |
| >
| >
 
Back
Top