IALweb Homepage
Forum Home Forum Home > MS Office > Microsoft Office > Microsoft Excel
  New Posts New Posts RSS Feed - [RISOLTO] Cancellazione valori multipli
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

REGISTRATEVI su IALWeb forum! E' facile e veloce! Potrete consultare tutte le sezioni del forum senza restrizioni e scrivere per dare o richiedere aiuto.
Votaci in Net-Parade

[RISOLTO] Cancellazione valori multipli

 Post Reply Post Reply
Author
Message
il_betto View Drop Down
Veterano
Veterano
Avatar

Joined: 13/Giu/2011
Status: Offline
Points: 2262
Post Options Post Options   Thanks (0) Thanks(0)   Quote il_betto Quote  Post ReplyReply Direct Link To This Post Topic: [RISOLTO] Cancellazione valori multipli
    Posted: 09/Gen/2017 at 12:07
Ciao a Tutti,

e Buon Anno !!!

E' bello iniziare con nuove sfide !! LOL

Confused Dovrei cancellare sui primi 12 fogli di lavoro range di celle multiple.

Ho provato a fare cosi' ma ottengo il seguente errore:
Metodo Range dell' oggetto '_Global' non riuscito

Sub Clear_Sheets()
    
    Dim conta As Integer
    Dim intervallo As String
   
    For conta = 1 To 12
        intervallo = Range("B11,B13,B15,B17,B19,B21,B23,B25,B28:B30," & _
                           "D8,D9,D11:D26,D28:D30," & _
                           "E8,E9,E11,E13,E15,E17,E19,E21,E23,E25,E28:E30," & _
"F8,F9,F11,F13,F15,F17,F19,F21,F23,F25,F28:F30,G8,G9,G11,G13,G15,G17,G19,G21,G23,G25,G28:G30,H8,H9,H11,H13,H15,H17,H19,H21,H23,H25,H28:H30,I8,I9,I11,I13,I15,I17,I19,I21,I23,I25,I28:I30," & _
"J8,J9,J11,J13,J15,J17,J19,J21,J23,J25,J28:J30,K8,K9,K11,K13,K15,K17,K19,K21,K23,K25,K28:K30,L8,L9,L11,L13,L15,L17,L19,L21,L23,L25,L28:L30,M8,M9,M11,M13,M15,M17,M19,M21,M23,M25,M28:M30," & _
"N8,N9,N11,N13,N15,N17,N19,N21,N23,N25,N28:N30,O8,O9,O11,O13,O15,O17,O19,O21,O23,O25,O28:O30,P8,P9,P11,P13,P15,P17,P19,P21,P23,P25,P28:P30,Q8,Q9,Q11,Q13,Q15,Q17,Q19,Q21,Q23,Q25,Q28:Q30," & _
"R8,R9,R11,R13,R15,R17,R19,R21,R23,R25,R28:R30,S8,S9,S11,S13,S15,S17,S19,S21,S23,S25,S28:S30,T8,T9,T11,T13,T15,T17,T19,T21,T23,T25,T28:T30,U8,U9,U11,U13,U15,U17,U19,U21,U23,U25,U28:U30," & _
"V8,V9,V11,V13,V15,V17,V19,V21,V23,V25,V28:V30,W8,W9,W11,W13,W15,W17,W19,W21,W23,W25,W28:W30,X8,X9,X11,X13,X15,X17,X19,X21,X23,X25,X28:X30,Y8,Y9,Y11,Y13,Y15,Y17,Y19,Y21,Y23,Y25,Y28:Y30," & _
"Z8,Z9,Z11,Z13,Z15,Z17,Z19,Z21,Z23,Z25,Z28:Z30,AA8,AA9,AA11,AA13,AA15,AA17,AA19,AA21,AA23,AA25,AA28:AA30,AB8,AB9,AB11,AB13,AB15,AB17,AB19,AB21,AB23,AB25,AB28:AB30," & _
"AC8,AC9,AC11,AC13,AC15,AC17,AC19,AC21,AC23,AC25,AC28:AC30,AD8,AD9,AD11,AD13,AD15,AD17,AD19,AD21,AD23,AD25,AD28:AD30,AE8,AE9,AE11,AE13,AE15,AE17,AE19,AE21,AE23,AE25,AE28:AE30," & _
"AF8,AF9,AF11,AF13,AF15,AF17,AF19,AF21,AF23,AF25,AF28:AA30,AG8,AG9,AG11,AG13,AG15,AG17,AG19,AG21,AG23,AG25,AG28:AG30,AH8,AH9,AH11,AH13,AH15,AH17,AH19,AH21,AH23,AH25,AH28:AH30," & _
                           "AM8:AM11,AM13,AM15,AM17,AM19,AM21,AM23,AM25,AM28:AM30," & _
                           "AN8:AN30").Value
        Sheets(conta).Range(intervallo).ClearContents
    Next conta
    
End Sub

Come si vede dall' esempio i valori da cancellare sono tanti:
avrei voluto raggruppare le righe cosi' per maggior chiarezza ed omogeneitÓ di contenuti ma mi e' stato detto dall' Editor che c' erano troppi ritorni a capo:

Range("B11,B13,B15,B17,B19,B21,B23,B25,B28:B30, & _
       D8,D9,D11:D26,D28:D30, _
       E8,E9,E11,E13,E15,E17,E19,E21,E23,E25,E28:E30, _
       F8,F9,F11,F13,F15,F17,F19,F21,F23,F25,F28:F30, _
       G8,G9,G11,G13,G15,G17,G19,G21,G23,G25,G28:G30, _
       H8,H9,H11,H13,H15,H17,H19,H21,H23,H25,H28:H30, _
       I8,I9,I11,I13,I15,I17,I19,I21,I23,I25,I28:I30, _
       J8,J9,J11,J13,J15,J17,J19,J21,J23,J25,J28:J30, _
       K8,K9,K11,K13,K15,K17,K19,K21,K23,K25,K28:K30, _
       L8,L9,L11,L13,L15,L17,L19,L21,L23,L25,L28:L30, _
       M8,M9,M11,M13,M15,M17,M19,M21,M23,M25,M28:M30, _
       N8,N9,N11,N13,N15,N17,N19,N21,N23,N25,N28:N30, _
       O8,O9,O11,O13,O15,O17,O19,O21,O23,O25,O28:O30, _
       P8,P9,P11,P13,P15,P17,P19,P21,P23,P25,P28:P30, _
       Q8,Q9,Q11,Q13,Q15,Q17,Q19,Q21,Q23,Q25,Q28:Q30, _
       R8,R9,R11,R13,R15,R17,R19,R21,R23,R25,R28:R30, _
       S8,S9,S11,S13,S15,S17,S19,S21,S23,S25,S28:S30, _
       T8,T9,T11,T13,T15,T17,T19,T21,T23,T25,T28:T30, _
       U8,U9,U11,U13,U15,U17,U19,U21,U23,U25,U28:U30, _
       V8,V9,V11,V13,V15,V17,V19,V21,V23,V25,V28:V30, _
       W8,W9,W11,W13,W15,W17,W19,W21,W23,W25,W28:W30, _
       X8,X9,X11,X13,X15,X17,X19,X21,X23,X25,X28:X30, _
       Y8,Y9,Y11,Y13,Y15,Y17,Y19,Y21,Y23,Y25,Y28:Y30, _
       Z8,Z9,Z11,Z13,Z15,Z17,Z19,Z21,Z23,Z25,Z28:Z30, _
       AA8,AA9,AA11,AA13,AA15,AA17,AA19,AA21,AA23,AA25,AA28:AA30, _
       AB8,AB9,AB11,AB13,AB15,AB17,AB19,AB21,AB23,AB25,AB28:AB30, _
       AC8,AC9,AC11,AC13,AC15,AC17,AC19,AC21,AC23,AC25,AC28:AC30, _
       AD8,AD9,AD11,AD13,AD15,AD17,AD19,AD21,AD23,AD25,AD28:AD30, _
       AE8,AE9,AE11,AE13,AE15,AE17,AE19,AE21,AE23,AE25,AE28:AE30, _
       AF8,AF9,AF11,AF13,AF15,AF17,AF19,AF21,AF23,AF25,AF28:AA30, _
       AG8,AG9,AG11,AG13,AG15,AG17,AG19,AG21,AG23,AG25,AG28:AG30, _
       AH8,AH9,AH11,AH13,AH15,AH17,AH19,AH21,AH23,AH25,AH28:AH30, _
       AM8:AM11,AM13,AM15,AM17,AM19,AM21,AM23,AM25,AM28:AM30, _
       AN8:AN30").Select

infatti, per ogni foglio: dal primo al dodicesimo,
devo cancellare il seguente range di valori nelle celle:

colonna B - valori in celle 11, 13, 15, 17, 19, 21, 23, 25, 28:30
colonna D  - valori in celle 8, 9, 11:26, 28:30
colonne da E a AH - valori in celle 8, 9, 11, 13, 15, 17, 19, 21, 23, 25, 28:30
colonna AM - valori in celle 8:11, 13, 15, 17, 19, 21, 23, 25, 28:30
colonna AN - valori in celle 8:30

Grazie mille in anticipo !!!!



Edited by il_betto - 09/Gen/2017 at 15:47
Back to Top
locate View Drop Down
Utente Senior
Utente Senior


Joined: 08/Ott/2009
Status: Offline
Points: 266
Post Options Post Options   Thanks (0) Thanks(0)   Quote locate Quote  Post ReplyReply Direct Link To This Post Posted: 09/Gen/2017 at 13:45
ciao


da provare

Option Explicit
Sub Elimina_range()
Dim i As Long, o As Long
For i = 1 To 12
 Sheets(i).Range("B11, B13, B15, B17, B19, B21, B23, B25, B28:B30").ClearContents
 Sheets(i).Range("D8, D9, D11:D26, D28:D30 ").ClearContents
  For o = 5 To 34
'Range("8, 9, 11, 13, 15, 17, 19, 21, 23, 25, 28:30 ") E:AH
   With Sheets(i)
     .Cells(8, o).ClearContents
     .Cells(9, o).ClearContents
     .Cells(11, o).ClearContents
     .Cells(13, o).ClearContents
     .Cells(15, o).ClearContents
     .Cells(17, o).ClearContents
     .Cells(19, o).ClearContents
     .Cells(21, o).ClearContents
     .Cells(23, o).ClearContents
     .Cells(25, o).ClearContents
     .Cells(28, o).ClearContents
     .Cells(29, o).ClearContents
     .Cells(30, o).ClearContents
    End With
  Next o
 Sheets(i).Range("AM8:AM11, AM13, AM15, AM17, AM19, AM21, AM23, AM25, AM28:AM30 ").ClearContents
 Sheets(i).Range("AN8:AN30 ").ClearContents
 Next i
End Sub


ciao da locate
excel 2007/13


Back to Top
il_betto View Drop Down
Veterano
Veterano
Avatar

Joined: 13/Giu/2011
Status: Offline
Points: 2262
Post Options Post Options   Thanks (0) Thanks(0)   Quote il_betto Quote  Post ReplyReply Direct Link To This Post Posted: 09/Gen/2017 at 15:46
Grazie mille locate,

problema risolto !!!

Usando il tuo codice, ho lanciato la seguente macro:

Sub Clear_Sheets()

    Dim i As Long, o As Long
    
    For i = 1 To 12
        Sheets(i).Range("B11,B13,B15,B17,B19,B21,B23,B25,B28:B30").ClearContents
        Sheets(i).Range("D12:AH12,D14:AH14,D16:AH16,D18:AH18,D20:AH20,D22:AH22,D24:AH24,D26:AH26").ClearContents
        
        For o = 4 To 34
            With Sheets(i)
                           .Cells(8, o).ClearContents
                           .Cells(9, o).ClearContents
                           .Cells(11, o).ClearContents
                           .Cells(13, o).ClearContents
                           .Cells(15, o).ClearContents
                           .Cells(17, o).ClearContents
                           .Cells(19, o).ClearContents
                           .Cells(21, o).ClearContents
                           .Cells(23, o).ClearContents
                           .Cells(25, o).ClearContents
                           .Cells(28, o).ClearContents
                           .Cells(29, o).ClearContents
                           .Cells(30, o).ClearContents
            End With
        Next o
        
        Sheets(i).Range("AM8:AM11,AM13,AM15,AM17,AM19,AM21,AM23,AM25,AM28:AM30").ClearContents
        Sheets(i).Range("AM11").Value = "S"
        Sheets(i).Range("AN8:AN30").ClearContents
        
    Next i
    
End Sub

e si e' cancellato cio' che volevo. Clap Clap

la riga in blu che vedi l' ho dovuta aggiungere in quanto i valori da cancellare fanno parte di celle condivise.

Nota bene: la procedura ci ha impiegato 5 minuti di orologio: non importa ... pero' pensavo ci impiegasse meno tempo ... LOL

Grazie ancora !!!


Back to Top
locate View Drop Down
Utente Senior
Utente Senior


Joined: 08/Ott/2009
Status: Offline
Points: 266
Post Options Post Options   Thanks (0) Thanks(0)   Quote locate Quote  Post ReplyReply Direct Link To This Post Posted: 09/Gen/2017 at 16:07
riciao

penso che al suo interno ci sono tante funzioni, quindi dobbiamo inibirle momentaneamente
aggiungi queste righe di controllo alla tua sub()
Sub Clear_Sheets()
Dim i As Long, o As Long
''''''''''''''''''''''''''''
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
''''
'qui tutto il ciclo for
'''''''''
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Calculate
End Sub

ciao
Back to Top
dodo47 View Drop Down
Moderatore
Moderatore
Avatar

Joined: 29/Dic/2008
Location: Italy
Status: Offline
Points: 8087
Post Options Post Options   Thanks (0) Thanks(0)   Quote dodo47 Quote  Post ReplyReply Direct Link To This Post Posted: 09/Gen/2017 at 16:24

Ciao

...non ho resistito: non fai prima a cancellare tutto e scrivere quello che non va cancellato?...(battutaccia!!) eh...eh...


saluti



Edited by dodo47 - 09/Gen/2017 at 16:26
domenico
win 10- office 2010
Back to Top
il_betto View Drop Down
Veterano
Veterano
Avatar

Joined: 13/Giu/2011
Status: Offline
Points: 2262
Post Options Post Options   Thanks (0) Thanks(0)   Quote il_betto Quote  Post ReplyReply Direct Link To This Post Posted: 09/Gen/2017 at 16:32
Ciao dodo,

Buon anno !!!

Purtroppo non posso cancellare a mano perche' ci sono altre cose in celle che 'e difficile poi riprodurre.

Cmq locate, e' diventata una "bomba" la macro: in 2 sec. ora fa tutto.

Bravissimo !!!!!!! Clap Clap Clap
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 10.17
Copyright ©2001-2013 Web Wiz Ltd.

This page was generated in 0,156 seconds.