come svuotare in automatico tutte le celle non bloccate di un fogl

[misunenbun]

Ho creato un prospetto in excel nel quale si alternano celle di inserimento e
celle con formule che ho bloccate.
Vorrei con un comando poter pulire tutte le celle non bloccate per poter
riusare il prospetto per altri dati.
Chi mi aiuta a risolvere il problema?

[Norman Jones]

Ciao Misunebun,

=============

=============

Prova:

Seleleziona una sola cella |
F5 | Speciale | Costanti | OK |
Del

---
Regards,
Norman

[Norman Jones]

Ciao Misunebun,

=============
> Ho creato un prospetto in excel nel quale si alternano celle di inserimento e
> celle con formule che ho bloccate.
> Vorrei con un comando poter pulire tutte le celle non bloccate per poter
> riusare il prospetto per altri dati.
> Chi mi aiuta a risolvere il problema?

Prova:

Seleleziona una sola cella |
F5 | Speciale | Costanti | OK |
Del

=============

Per automatissare la soluzione, in un
modulo standard (vedi di sotto),
incolla:

'============>>
Option Explicit

Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("Pippo.xls") '<<=== da CAMBIARE
Set SH = WB.Sheets("Foglio1") '<<=== da CAMBIARE

On Error Resume Next
SH.Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0

End Sub
'<<============

Per utilizzare questa routine:

Alt-F11 per aprire l'Editor di VBA
Menu | Inserisci | Modulo
Incolla il suddetto codice
Alt-F11 per tornare in Excel
Alt-F8
Seleziona "Tester"
Esegui

---
Regards,
Norman

[Norman Jones]

Ciao Misunebun,

=============
Ho creato un prospetto in excel nel quale si alternano celle di inserimento e
celle con formule che ho bloccate.
Vorrei con un comando poter pulire tutte le celle non bloccate per poter
riusare il prospetto per altri dati.
Chi mi aiuta a risolvere il problema?

=============

Come approcchio diverso, hai pensato
a creare un modello, utilizzando una
copia del file che contiene soltanto le
formule?

In tal modo, ogno volta che si apre un
nuovo file (utilizzando) il modello ci
saranno unicamente delle formule.

Per creare il modello:

Apri il file interessato
Imposta i settaggi che vuoi
Salva il file:
- come tipo: Modello
- con iuno nome adatto (ad esempio:
Prospetto.xlt

Chiudi il file.

---
Regards,
Norman

[misunenbun]

Con F5, speciale, costanti trovo tutte le celle che contengono costanti,
anche quelle formattate come bloccate invece io devo cancellare solo quelle
formattate come sbloccate.
Ho trovato però un altro sistema:
comando Trova da Modifica: Trova: (vuoto) > formato > numero
protezione
(sbloccate)
Sostituisci > Sostituisci con:
(vuoto) > nessun formato
> Sostituisci tutto
Funziona! Basta inserirlo in una macro e tutto è automatizzato.
Grazie ancora comunque per i consigli "Norman Jones"
misunenbun

"Norman Jones" ha scritto:

>====================

[Norman Jones]

Ciao Misunebun,

=============

Con F5, speciale, costanti trovo tutte le celle che contengono costanti,
anche quelle formattate come bloccate invece io devo cancellare solo quelle
formattate come sbloccate.
Ho trovato però un altro sistema:
comando Trova da Modifica: Trova: (vuoto) > formato > numero
protezione
(sbloccate)
Sostituisci > Sostituisci con:
(vuoto) > nessun formato
> Sostituisci tutto
Funziona! Basta inserirlo in una macro e tutto è automatizzato.
Grazie ancora comunque per i consigli "Norman Jones"

=============

Prova la seguente versione:

'============>>
Option Explicit

Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Dim rCell As Range

Set WB = Workbooks("Pippo.xls") '<<=== da CAMBIARE
Set SH = WB.Sheets("Foglio1") '<<=== da CAMBIARE

On Error Resume Next

Set Rng = SH.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell
If Not .Locked Then
.ClearContents
End If
End With
Next rCell
End If
End Sub
'<<============

---
Regards,
Norman

[gianf...@gmail.com]

Io ho lo stesso problema,ma su tutti i fogli,cioe devo azzerare tutti i valori dal foglio madre di tutte le celle non bloccate di tutti gli altri fogli,per poter rimettere i valori a mio piacimento,spero che qualcuno possa darmi una macro,quella di Norman va bene ma funziona su un solo foglio,spero che Norman mi aiuti, grazie

[Bruno Campanini]

gianf...@gmail.com has brought this to us :

Public Sub ClearSheets()
Dim i
For Each i In Sheets
i.Cells.Clear
Next
End Sub

Bruno

[Bruno Campanini]

Bruno Campanini was thinking very hard :

> Public Sub ClearSheets()
> Dim i
> For Each i In Sheets
> i.Cells.Clear
> Next
> End Sub

Non avevo letto bene la domanda.
Come non detto.

Bruno

[Bruno Campanini]

Bruno Campanini pretended :

Questa dovrebbe essere congruente con la domanda:

======================================
Public Sub Clear()
Dim i, j

For Each i In Sheets

For Each j In i.UsedRange.Cells
If Not j.Locked Then
j.ClearContents
End If
Next
Next
End Sub
========================================

Bruno

[Norman Jones]

On 13/01/2014 01:46, Bruno Campanini wrote:
> Questa dovrebbe essere congruente con la domanda:
>
> ======================================
> Public Sub Clear()
> Dim i, j
> For Each i In Sheets
> For Each j In i.UsedRange.Cells
> If Not j.Locked Then
> j.ClearContents
> End If
> Next
> Next
> End Sub
> ========================================

Ciao Bruno,

Chiedo scusa se ficco il naso nella tua discussione; lo faccio perchￜ
vedo che Gianni ha invocato il mio nome.

Anche se la tua macro dovrebbe funzionare solitamente, si potrebbe
incontrare problemi e il codice si bloccarebbe se la cartella di lavoro
dovessi contenere, ad esempio, dei fogli grafici. Pertanto, piￜ
resistente sarebbe il seguente adattamento leggero:

'=============>>
Public Sub Clear()
Dim i As Worksheet, j As Range
For Each i In Worksheets

For Each j In i.UsedRange.Cells
If Not j.Locked Then
j.ClearContents
End If

Next j
Next i
End Sub
'<<=============

Avendo cominciato a vagare su questa strada, un po' piￜ veloce potrebbe
essere il seguente adattamento leggero:

'=============>>
Public Sub Clear()
Dim i As Worksheet, j As Range
For Each i In Worksheets
On Error Resume Next
For Each j In i.UsedRange.SpecialCells(xlCellTypeConstants)

If Not j.Locked Then
j.ClearContents
End If

Next j
On Error GoTo 0
Next i
End Sub
'<<=============

Infine, per dimostrare oltre *ogni* dubbio che io sono pignolo (ma
assolutamente non con l'intenzione di incazzarti ), io preferirei:

'=============>>
Public Sub MyClear()
Dim SH As Worksheet, rCell As Range
For Each SH In ActiveWorkbook.Worksheets
On Error Resume Next
For Each rCell In SH.UsedRange.SpecialCells(xlCellTypeConstants)

With rCell
If Not .Locked Then
.ClearContents
End If
End With
Next rCell

On Error GoTo 0
Next SH
End Sub
'<<=============



===
Regards,
Norman

[Marcello Vulcano]

Buongiorno!
Scusatemi se mi inserisco, ma oserei chiedere una integrazione a questo problema.

ovvero...
prima di cancellare i dati nelle celle non protette, come fare per archiviare i dati (delle celle protette e non protette) riga per riga su un nuovo foglio?

In sintesi devo archiviare periodicamente i dati elaborati e poi cancellare i dati delle celle non protette delle righe che sono state elaborate completamente x inserire nuovi dati.

*********
Grazie.
Marcello
*********

[Bruno Campanini]

It happens that Marcello Vulcano formulated :

> Buongiorno!
> Scusatemi se mi inserisco, ma oserei chiedere una integrazione a questo
> problema.
>
> ovvero...
> prima di cancellare i dati nelle celle non protette, come fare per archiviare
> i dati (delle celle protette e non protette) riga per riga su un nuovo
> foglio?

Si fa cosￜ a dialogare su un NG.

> In sintesi devo archiviare periodicamente i dati elaborati e poi cancellare i
> dati delle celle non protette delle righe che sono state elaborate
> completamente x inserire nuovi dati.

Non come fai tu, che costringi il corrispondente a scorrere l'intero
thread.

Bruno

[Norman Jones]

On 16/01/2014 13:30, Marcello Vulcano wrote:
> Buongiorno!
> Scusatemi se mi inserisco, ma oserei chiedere una integrazione
> a questo problema.
>
> ovvero...
> prima di cancellare i dati nelle celle non protette, come fare
> per archiviare i dati (delle celle protette e non protette) riga
> per riga su un nuovo foglio?
>
> In sintesi devo archiviare periodicamente i dati elaborati e poi
> cancellare i dati delle celle non protette delle righe che sono
> state elaborate completamente x inserire nuovi dati.
>
> *********
> Grazie.
> Marcello
> *********

Ciao Marcello,

Prova qualcosa del genere:

'=============>>
Public Sub MySaveCopyAndClear()
Dim WB As Workbook

Dim SH As Worksheet, rCell As Range

Dim sFilename As String
Dim sName As String
Dim sStr As String
Dim i As Long

Set WB = ThisWorkbook
sFilename = WB.Name
sStr = Split(sFilename, ".")(0)
sName = sStr & " Archivio" & Format(Date, "yyyymmdd") & ".xlsm"

WB.SaveCopyAs sName

For Each SH In WB.Worksheets

On Error Resume Next
For Each rCell In SH.UsedRange.SpecialCells(xlCellTypeConstants)
With rCell
If Not .Locked Then
.ClearContents
End If
End With
Next rCell
On Error GoTo 0
Next SH
End Sub
'<<=============

Supponiamo che il nome del file sia Pippo.xlsm (xlsm perche' contiene
delle macro!), una copia del file sara' salvato nela stessa cartella
(directory) con il nome Pippo Archivio yyyymmdd.xlsm. dove yyyymmdd
sara' la data.


===
Regards,
Norman

[Norman Jones]

On 17/01/2014 19:57, Norman Jones wrote:
> nela stessa cartella (directory)

nella ...



===
Regards,
Norman

[Marcello Vulcano]

Buongiorno e grazie!
Perfetto!
Questa tua soluzione è ancora più efficace di quanto io pensassi, non avevo infatti considerato la cronologia che nel mio caso risulta invece determinante.

Solo un dubbio: questa soluzione copia il file così come è, archiviandolo; giusto?
Se così fosse, considerando una notevole quantità di dati (formule comprese), risulterebbero copie di dimensioni ragguardevoli... Mi puoi indicare per piacere come salvarlo semplicemente eliminando tutte le formule conservando sol il risultato sul "foglio1" che è il principale?
Finora abbiamo seguito il classico <<rimuovi protezione-foglio-crea una copia>> poi nel foglio copiato <<seleziona tutto-copia-incolla speciale-solo valori-proteggi foglio>>...
Dovendo lasciare questo lavoro anche ad altri operatori, purtroppo non sempre molto pratici, ho avuto qualche problema (risolto certamente...) ma che preferirei evtare.

In ogni caso già così com'è mi semplifica di molto il lavoro!

Grazie e, come sempre da anni, complimenti!

*********
Grazie.
Marcello
*********

[Norman Jones]

On 18/01/2014 10:21, Marcello Vulcano wrote:
> Buongiorno e grazie!
> Perfetto!

> Questa tua soluzione � ancora pi� efficace di quanto io pensassi,

> non avevo infatti considerato la cronologia che nel mio caso risulta
> invece determinante.
>

> Solo un dubbio: questa soluzione copia il file cos� come �,
> archiviandolo; giusto?
> Se cos� fosse, considerando una notevole quantit� di dati

> (formule comprese), risulterebbero copie di dimensioni ragguardevoli...
> Mi puoi indicare per piacere come salvarlo semplicemente eliminando

> tutte le formule conservando sol il risultato sul "foglio1" che � il

> principale?
> Finora abbiamo seguito il classico <<rimuovi protezione-foglio-crea
> una copia>> poi nel foglio copiato <<seleziona tutto-copia-incolla
> speciale-solo valori-proteggi foglio>>...
> Dovendo lasciare questo lavoro anche ad altri operatori, purtroppo
> non sempre molto pratici, ho avuto qualche problema (risolto
> certamente...) ma che preferirei evtare.
>

> In ogni caso gi� cos� com'� mi semplifica di molto il lavoro!

>
> Grazie e, come sempre da anni, complimenti!
>
> *********
> Grazie.
> Marcello
> *********

Ciao Marcello,

Se vuoi archiavare un singolo foglio del workbook, prova qualcosa del
genere:

'=============>>
Public Sub MarcelloSaveCopyAndClear()
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim aSH As Worksheet
Dim rCell As Range
Dim Rng As Range

Dim sFilename As String
Dim sName As String
Dim sStr As String

Const PWord As String = "ApraMi" '<<==== CAMBIA

Set WB = ThisWorkbook
Set aSH = WB.Sheets("Foglio2") '<<==== CAMBIA

sFilename = WB.Name
sStr = Split(sFilename, ".")(0)

sName = sStr & "---" & aSH.Name _
& " Archivio" _
& Format(Date, "yyyymmdd") & ".xlsx" '//(no macros!)

aSH.Copy
Set WB2 = ActiveWorkbook
With WB2
With .Worksheets(1)
.Unprotect Password:=PWord
Set Rng = .UsedRange
With Rng
.Copy
.PasteSpecial xlPasteValues
End With
.Protect Password:=PWord
End With
.SaveAs Filename:=sName, FileFormat:=xlOpenXMLWorkbook
.Close Savechanges:=False
End With

For Each SH In WB.Worksheets

With SH
On Error Resume Next
.Unprotect Password:=PWord
For Each rCell In .UsedRange.SpecialCells(xlCellTypeConstants)

With rCell
If Not .Locked Then
.ClearContents
End If
End With
Next rCell

.Protect Password:=PWord
On Error GoTo 0
End With

Next SH
End Sub
'<<=============

Se per�, non vuoi condividere la password con 'altri operatori', potrei
postare una versione aggiornata di questo codice.



===
Regards,
Norman

[Marcello Vulcano]

Il giorno sabato 18 gennaio 2014 15:30:01 UTC+1, Norman Jones ha scritto:
> On 18/01/2014 10:21, Marcello Vulcano wrote:
>
> > Buongiorno e grazie!
>
> > Perfetto!
>

> > Questa tua soluzione � ancora pi� efficace di quanto io pensassi,

>
> > non avevo infatti considerato la cronologia che nel mio caso risulta
>
> > invece determinante.
>
> >
>

> > Solo un dubbio: questa soluzione copia il file cos� come �,
>
> > archiviandolo; giusto?
>
> > Se cos� fosse, considerando una notevole quantit� di dati

>
> > (formule comprese), risulterebbero copie di dimensioni ragguardevoli...
>
> > Mi puoi indicare per piacere come salvarlo semplicemente eliminando
>

> > tutte le formule conservando sol il risultato sul "foglio1" che � il

>
> > principale?
>
> > Finora abbiamo seguito il classico <<rimuovi protezione-foglio-crea
>
> > una copia>> poi nel foglio copiato <<seleziona tutto-copia-incolla
>
> > speciale-solo valori-proteggi foglio>>...
>
> > Dovendo lasciare questo lavoro anche ad altri operatori, purtroppo
>
> > non sempre molto pratici, ho avuto qualche problema (risolto
>
> > certamente...) ma che preferirei evtare.
>
> >
>

> > In ogni caso gi� cos� com'� mi semplifica di molto il lavoro!

> Se per�, non vuoi condividere la password con 'altri operatori', potrei

>
> postare una versione aggiornata di questo codice.
>
>
>
>
>
>
>
> ===
>
> Regards,
>
> Norman

Grazie Norman!

> Se per�, non vuoi condividere la password con 'altri operatori', potrei

> postare una versione aggiornata di questo codice.

Sì te ne sono grato! Preferisco evitare di far capitare disastri accidentali!


Ora vado ad inserire il codice!

*********
Grazie.
Marcello
*********

[Norman Jones]

On 18/01/2014 17:48, Marcello Vulcano wrote:
>> Se per�, non vuoi condividere la password con 'altri operatori', potrei

>> >postare una versione aggiornata di questo codice.

> S� te ne sono grato! Preferisco evitare di far capitare disastri accidentali!

>
>
> Ora vado ad inserire il codice!

Ciao Marcello,

Nel modulo ThisWorkbook del file interessato, incolla:

'=============>>
Option Explicit

'------------->>
Private Sub Workbook_Open()
Dim aName As Name
Dim aStr As String
Const sStr As String = "Marcello"

aStr = StrReverse(sStr) '// La password diventa: ollecraM
On Error Resume Next
Set aName = Me.Names("LetMeIn")
If Err.Number <> 0 Then
Set aName = Me.Names.Add(Name:="LetMeIn", _
RefersTo:=aStr, _
Visible:=False)
End If
On Error GoTo 0
End Sub
'<<=============

In un modulo standard, incolla il seguente codice:

'=============>>
Option Explicit

'------------->>

Public Sub MarcelloSaveCopyAndClear()
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim aSH As Worksheet
Dim rCell As Range
Dim Rng As Range
Dim sFilename As String
Dim sName As String
Dim sStr As String

Dim PWord As Variant

Set WB = ThisWorkbook
With WB
PWord = Evaluate(.Names("BepeGrillo").RefersTo)

Set aSH = WB.Sheets("Foglio2") '<<==== CAMBIA

End With

===
Regards,
Norman

[Marcello Vulcano]

Il giorno lunedì 20 gennaio 2014 10:20:56 UTC+1, Norman Jones ha scritto:
> On 18/01/2014 17:48, Marcello Vulcano wrote:
>

> >> Se per�, non vuoi condividere la password con 'altri operatori', potrei

>
> >> >postare una versione aggiornata di questo codice.
>

> > S� te ne sono grato! Preferisco evitare di far capitare disastri accidentali!

Grazie ancora Norman!
Provo subito il nuovo codice che mi hai inviato!
Posso chiederti perché va inserita la prima parte del codice in "ThisWorkbook" e l'altra in "modulo standard"? Non avendo una conoscenza adeguata per il VBA che cerco di colmare, sto cercando di decifrare il codice e le possibilità che si aprono.

*********
Grazie per tutto!
Marcello
*********

[Norman Jones]

On 20/01/2014 13:29, Marcello Vulcano wrote:
> Grazie ancora Norman!
> Provo subito il nuovo codice che mi hai inviato!

> Posso chiederti perch� va inserita la prima parte del codice in "ThisWorkbook"

> e l'altra in "modulo standard"? Non avendo una conoscenza adeguata per il VBA

> che cerco di colmare, sto cercando di decifrare il codice e le possibilit� che
> si aprono.

Ciao Marcello,

Ho deciso, in questo caso, di utilizzare un nome nascosto per 'ospitare'
la tua password.

Ho poi sfuttato il codice di evento Workbook_Open per verificare che sia
stato creato il nome (che ho nominato LetMeIn) e, in caso negativo di
crearlo.

Comunque, sarebbe possible invece cancellare la procedura Workbook_Open
ed utilizzare il seguente codice in un modulo standard:

'=============>>
Option Explicit

'------------->>
Public Sub CreateName()

Dim aName As Name
Dim aStr As String
Const sStr As String = "Marcello"

aStr = StrReverse(sStr)

On Error Resume Next
Set aName = Me.Names("BepeGrillo")

If Err.Number <> 0 Then
Set aName = Me.Names.Add(Name:="LetMeIn", _
RefersTo:=aStr, _
Visible:=False)
End If
On Error GoTo 0

End Sub
'<<=============

Tuttavia, io preferisco l'approccio di verificare l'esistenza del nome
nascosto all'apertura del workbook.

Detto questo, c'era un errore nella procedura MarcelloSaveCopyAndClear:
la riga

> PWord = Evaluate(.Names("BepeGrillo").RefersTo)

avrebbe dovuto essere

PWord = Evaluate(.Names("LetMeIn").RefersTo)


===
Regards,
Norman

[Norman Jones]

On 20/01/2014 15:51, Norman Jones wrote:
> Set aName = Me.Names("BepeGrillo")

Anche nella procedura CreateName,

la riga qui sopre avrebbe dovuto essere

Set aName = Me.Names("LetMeIn")

Nonostante tutti i segni del contrario, ti assicuro che io non soffro di
una fissazione di Beppe Grillo! :-)


===
Regards,
Norman

[Marcello Vulcano]

Circa "Beppe Grillo"...
:D Norman,
mi era quasi venuto il dubbio!!! :D


Chiara ed esplicativa la tua risposta!
Mi puoi suggerire un approccio "passo passo" al VBA che si utilizza in questi casi?!
Ho studiato a suo tempo un po' cosa vuol dire programmare ma dall'approccio preliminare alla stesura di codice, che possa dirsi tale, il passo non è breve ma la curiosità è tanta!

*********
Grazie!
Marcello
*********

[Norman Jones]

On 20/01/2014 22:53, Marcello Vulcano wrote:
> Chiara ed esplicativa la tua risposta!
> Mi puoi suggerire un approccio "passo passo" al VBA che si utilizza
> in questi casi?!
> Ho studiato a suo tempo un po' cosa vuol dire programmare ma
> dall'approccio preliminare alla stesura di codice, che possa dirsi
> tale, il passo non è breve ma la curiosità è tanta!

Ciao Marcello,

Se la lingua Inglese non presenti problema, vorrei suggerire, e
consigliare vivamente, i libri di John Walkenbach:

Excel VBA Programming For Dummies
e (poi)
Excel 20?? Power Programming with VBA (qualsiasi versione)

C'è una pletora di corsi on-line, sia in italiano che in inglese, ma
non posso parlare per la loro qualità.

Vorrei anche suggerirti di seguire sia il codice VBA postato qui e
anche nel forum:

http://answers.microsoft.com/it-it/office/forum/office/


===
Regards,
Norman

[Marcello Vulcano]

Il giorno martedì 21 gennaio 2014 08:51:53 UTC+1, Norman Jones ha scritto:
> On 20/01/2014 22:53, Marcello Vulcano wrote:
>
> > Chiara ed esplicativa la tua risposta!
>
> > Mi puoi suggerire un approccio "passo passo" al VBA che si utilizza
>
> > in questi casi?!
>
> > Ho studiato a suo tempo un po' cosa vuol dire programmare ma
>
> > dall'approccio preliminare alla stesura di codice, che possa dirsi
>

> > tale, il passo non � breve ma la curiosit� � tanta!

>
>
>
> Ciao Marcello,
>
>
>
> Se la lingua Inglese non presenti problema, vorrei suggerire, e
>
> consigliare vivamente, i libri di John Walkenbach:
>
>
>
> Excel VBA Programming For Dummies
>
> e (poi)
>
> Excel 20?? Power Programming with VBA (qualsiasi versione)
>
>
>

> C'� una pletora di corsi on-line, sia in italiano che in inglese, ma
>
> non posso parlare per la loro qualit�.

>
>
>
> Vorrei anche suggerirti di seguire sia il codice VBA postato qui e
>
> anche nel forum:
>
>
>
> http://answers.microsoft.com/it-it/office/forum/office/
>
>
>
>
>
> ===
>
> Regards,
>
> Norman

Ciao Norman,
grazie per le indicazioni per link e testi(che mi procurerò quanto prima) nonché per il codice.
Sei, come sempre, cortese e preciso.
A presto!

*********
Grazie!
Marcello
*********

[Marcello Vulcano]

Norman,
a seguito di quanto da te suggeritomi purtroppo ho dovuto rivedere un po' tutto il progetto.
In ogni caso grazie per quanto mi hai indicato e... mi suggerirai! ;)

*********
Grazie!
Marcello
*********

[Marcello Vulcano]

Apro un nuovo argomento, per non divagare oltre in questo già discusso, perché non ho ancora ben chiare le idee e non vorrei lasciare nulla di imprevisto... o quasi!

*********
Grazie!
Marcello
*********