Lavorare con celle e intervalli in Excel VBA (Selezionare, copiare, spostare, modificare)
Quando si lavora con Excel, la maggior parte del tempo viene trascorso nell’area del foglio di lavoro, occupandosi di celle e intervalli.
E se vuoi automatizzare il tuo lavoro in Excel usando VBA, devi sapere come lavorare con celle e intervalli usando VBA.
Ci sono molte cose diverse che puoi fare con gli intervalli in VBA (come selezionare, copiare, spostare, modificare, ecc.).
Quindi, per coprire questo argomento, spezzerò questo tutorial in sezioni e ti mostrerò come lavorare con celle e intervalli in Excel VBA usando esempi.,
Iniziamo.
Se sei interessato ad imparare VBA nel modo più semplice, dai un’occhiata alla mia formazione online Excel VBA.
Questo tutorial copre:
Selezione di una cella/intervallo in Excel utilizzando VBA
Per lavorare con celle e intervalli in Excel utilizzando VBA, non è necessario selezionarlo.
Nella maggior parte dei casi, è meglio non selezionare celle o intervalli (come vedremo).,
Nonostante ciò, è importante passare attraverso questa sezione e capire come funziona. Questo sarà fondamentale per l’apprendimento VBA e un sacco di concetti trattati qui saranno utilizzati in tutto questo tutorial.
Quindi iniziamo con un esempio molto semplice.
Selezionare una Singola Cella Utilizzando VBA
Se si desidera selezionare una singola cella nel foglio attivo (diciamo A1), quindi è possibile utilizzare il seguente codice:
Sub SelectCell()Range("A1").SelectEnd Sub
Il codice sopra è obbligatorio ” Sub ” e ” End Sub’ parte, e una riga di codice che consente di selezionare la cella A1.,
Range (“A1”) indica a VBA l’indirizzo della cella a cui vogliamo fare riferimento.
Select è un metodo dell’oggetto Range e seleziona le celle / intervallo specificato nell’oggetto Range. I riferimenti di cella devono essere racchiusi tra virgolette.
Questo codice mostrerebbe un errore nel caso in cui un foglio grafico sia un foglio attivo. Un foglio grafico contiene grafici e non è ampiamente utilizzato. Poiché non ha celle/intervalli, il codice sopra non può selezionarlo e finirebbe per mostrare un errore.
Si noti che poiché si desidera selezionare la cella nel foglio attivo, è sufficiente specificare l’indirizzo della cella.,
Ma se vuoi selezionare la cella in un altro foglio (diciamo Sheet2), devi prima attivare Sheet2 e quindi selezionare la cella al suo interno.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Allo stesso modo, puoi anche attivare una cartella di lavoro, quindi attivare un foglio di lavoro specifico al suo interno, quindi selezionare una cella.
Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Si noti che quando si fa riferimento alle cartelle di lavoro, è necessario utilizzare il nome completo insieme all’estensione del file (.xlsx nel codice sopra). Nel caso in cui la cartella di lavoro non sia mai stata salvata, non è necessario utilizzare l’estensione del file.,
Ora, questi esempi non sono molto utili, ma vedrai più avanti in questo tutorial come possiamo usare gli stessi concetti per copiare e incollare celle in Excel (usando VBA).
Proprio come selezioniamo una cella, possiamo anche selezionare un intervallo.
Nel caso di un intervallo, potrebbe essere un intervallo di dimensioni fisse o un intervallo di dimensioni variabili.
In un intervallo di dimensioni fisse, saprai quanto è grande l’intervallo e puoi usare la dimensione esatta nel tuo codice VBA. Ma con un intervallo di dimensioni variabili, non hai idea di quanto sia grande l’intervallo e devi usare un po ‘ di magia VBA.
Vediamo come farlo.,
Selezione di un intervallo di dimensioni fisse
Ecco il codice che selezionerà l’intervallo A1:D20.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
Un altro modo per farlo è usare il codice seguente:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
Il codice precedente prende l’indirizzo di cella in alto a sinistra (A1) e l’indirizzo di cella in basso a destra (D20) e seleziona l’intero intervallo. Questa tecnica diventa utile quando si lavora con intervalli di dimensioni variabili (come vedremo quando la proprietà End verrà trattata più avanti in questo tutorial).,
Se si desidera che la selezione avvenga in una cartella di lavoro diversa o in un foglio di lavoro diverso, è necessario indicare a VBA i nomi esatti di questi oggetti.
Ad esempio, il codice seguente selezionerebbe l’intervallo A1:D20 nel foglio di lavoro Sheet2 nella cartella di lavoro Book2.
Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub
Ora, cosa succede se non sai quante righe ci sono. Cosa succede se si desidera selezionare tutte le celle che hanno un valore in esso.
In questi casi, è necessario utilizzare i metodi mostrati nella sezione successiva (selezionando l’intervallo di dimensioni variabili).,
Selezione di un intervallo di dimensioni variabili
Esistono diversi modi per selezionare un intervallo di celle. Il metodo scelto dipenderà da come sono strutturati i dati.
In questa sezione, tratterò alcune tecniche utili che sono davvero utili quando si lavora con intervalli in VBA.
Selezionare Utilizzando la proprietà CurrentRange
Nei casi in cui non si sa quante righe / colonne hanno i dati, è possibile utilizzare la proprietà CurrentRange dell’oggetto Range.
La proprietà CurrentRange copre tutte le celle riempite contigue in un intervallo di dati.,
Di seguito è riportato il codice che selezionerà la regione corrente che contiene la cella A1.
Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub
Il metodo precedente è buono quando si hanno tutti i dati come tabella senza righe / colonne vuote.
Ma nel caso in cui si abbiano righe/colonne vuote nei dati, non selezionerà quelli dopo le righe / colonne vuote. Nell’immagine qui sotto, il codice CurrentRegion seleziona i dati fino alla riga 10 poiché la riga 11 è vuota.
In questi casi, è possibile utilizzare la proprietà UsedRange dell’oggetto Foglio di lavoro.,
Selezionare Using UsedRange Proprietà
UsedRange consente di fare riferimento a tutte le celle che sono state modificate.
Quindi il codice seguente selezionerebbe tutte le celle utilizzate nel foglio attivo.
Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub
Nota che nel caso in cui tu abbia una cella lontana che è stata usata, sarebbe considerata dal codice sopra e tutte le celle fino a quella cella usata sarebbero selezionate.
Seleziona usando la proprietà End
Ora, questa parte è davvero utile.
La proprietà End consente di selezionare l’ultima cella riempita., Ciò consente di imitare l’effetto del tasto freccia Control Down/Up o dei tasti Control Right/Left.
Proviamo a capirlo usando un esempio.
Supponiamo di avere un set di dati come mostrato di seguito e di voler selezionare rapidamente le ultime celle riempite nella colonna A.
Il problema qui è che i dati possono cambiare e non si sa quante celle sono riempite. Se devi farlo usando la tastiera, puoi selezionare la cella A1, quindi utilizzare il tasto Ctrl + Freccia giù, e selezionerà l’ultima cella riempita nella colonna.
Ora vediamo come farlo usando VBA., Questa tecnica è utile quando si desidera passare rapidamente all’ultima cella piena in una variabile di dimensioni colonna
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
Il codice di cui sopra, vorrei passare all’ultima cella piena nella colonna A.
allo stesso modo, è possibile utilizzare l’End(xlToRight) per passare all’ultima cella piena di fila.
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
Ora, cosa succede se si desidera selezionare l’intera colonna invece di saltare all’ultima cella piena.,
Puoi farlo usando il codice qui sotto:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub
Nel codice sopra, abbiamo usato il primo e l’ultimo riferimento della cella che dobbiamo selezionare. Non importa quante celle riempite ci sono, il codice sopra selezionerà tutto.
Ricorda l’esempio sopra in cui abbiamo selezionato l’intervallo A1:D20 usando la seguente riga di codice:
Intervallo(“A1″,”D20”)
Qui A1 era la cella in alto a sinistra e D20 era la cella in basso a destra nell’intervallo. Possiamo usare la stessa logica nella selezione di intervalli di dimensioni variabili., Ma dal momento che non conosciamo l’indirizzo esatto della cella in basso a destra, abbiamo usato la proprietà End per ottenerla.
Nell’intervallo (”A1″, Intervallo (“A1”).End(xlDown)), “A1” si riferisce alla prima cella e Intervallo(“A1”).End (xlDown) si riferisce all’ultima cella. Poiché abbiamo fornito entrambi i riferimenti, il metodo Select seleziona tutte le celle tra questi due riferimenti.
Allo stesso modo, è anche possibile selezionare un intero set di dati con più righe e colonne.
Il codice seguente selezionerebbe tutte le righe/colonne riempite a partire dalla cella A1.,
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub
Nel codice di cui sopra, abbiamo usato Range(“A1”).Fine (xlDown).End (xlToRight) per ottenere il riferimento della cella riempita in basso a destra del set di dati.
Differenza tra l’utilizzo di CurrentRegion e End
Se ti stai chiedendo perché usare la proprietà End per selezionare l’intervallo riempito quando abbiamo la proprietà CurrentRegion, lascia che ti dica la differenza.
Con la proprietà End, è possibile specificare la cella di avvio., Ad esempio, se si hanno i dati in A1:D20, ma la prima riga sono intestazioni, è possibile utilizzare la proprietà End per selezionare i dati senza le intestazioni (utilizzando il codice seguente).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub
Ma CurrentRegion selezionerebbe automaticamente l’intero set di dati, incluse le intestazioni.
Finora in questo tutorial, abbiamo visto come fare riferimento a un intervallo di celle utilizzando modi diversi.
Ora vediamo alcuni modi in cui possiamo effettivamente utilizzare queste tecniche per ottenere un po ‘ di lavoro fatto.,
Copia celle/intervalli Usando VBA
Come ho detto all’inizio di questo tutorial, la selezione di una cella non è necessaria per eseguire azioni su di essa. Vedrai in questa sezione come copiare celle e intervalli senza nemmeno selezionarli.
Iniziamo con un semplice esempio.
Copia singola cella
Se si desidera copiare la cella A1 e incollarla nella cella D1, il codice seguente lo farebbe.
Sub CopyCell()Range("A1").Copy Range("D1")End Sub
Si noti che il metodo di copia dell’oggetto range copia la cella (proprio come Control +C) e la incolla nella destinazione specificata.,
Nel codice di esempio sopra, la destinazione viene specificata nella stessa riga in cui si utilizza il metodo di copia. Se vuoi rendere il tuo codice ancora più leggibile, puoi usare il codice seguente:
Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub
I codici sopra copieranno e incolleranno il valore e la formattazione / formule in esso.
Come potresti aver già notato, il codice sopra copia la cella senza selezionarla. Non importa dove ti trovi sul foglio di lavoro, il codice copierà la cella A1 e la incollerà su D1.
Inoltre, si noti che il codice sopra sovrascriverebbe qualsiasi codice esistente nella cella D2., Se vuoi che Excel ti informi se c’è già qualcosa nella cella D1 senza sovrascriverlo, puoi usare il codice qui sotto.
Copia di un intervallo di dimensioni fisse
Se si desidera copiare A1:D20 in J1:M20, è possibile utilizzare il codice seguente:
Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub
Nella cella di destinazione, è sufficiente specificare l’indirizzo della cella in alto a sinistra. Il codice copierebbe automaticamente l’intervallo esatto copiato nella destinazione.
È possibile utilizzare lo stesso costrutto per copiare i dati da un foglio all’altro.
Il codice seguente copierebbe A1:D20 dal foglio attivo a Sheet2.,
Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
Quanto sopra copia i dati dal foglio attivo. Quindi assicurati che il foglio che contiene i dati sia il foglio attivo prima di eseguire il codice. Per sicurezza, puoi anche specificare il nome del foglio di lavoro durante la copia dei dati.
Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
La cosa buona del codice sopra è che indipendentemente dal foglio attivo, copierà sempre i dati da Sheet1 e li incollerà in Sheet2.
È anche possibile copiare un intervallo denominato utilizzando il suo nome al posto del riferimento.,
Ad esempio, se si dispone di un intervallo denominato chiamato ‘SalesData’, è possibile utilizzare il codice seguente per copiare questi dati su Sheet2.
Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub
Se l’ambito dell’intervallo denominato è l’intera cartella di lavoro, non è necessario essere sul foglio con l’intervallo denominato per eseguire questo codice. Poiché l’intervallo denominato è ambito per la cartella di lavoro, è possibile accedervi da qualsiasi foglio utilizzando questo codice.
Se si dispone di una tabella con il nome Table1, è possibile utilizzare il codice seguente per copiarlo su Sheet2.
Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub
È anche possibile copiare un intervallo in un’altra cartella di lavoro.,
Nell’esempio seguente, copio la tabella Excel (Table1), nella cartella di lavoro Book2.
Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub
Questo codice funzionerebbe solo se la cartella di lavoro è già aperta.
Copiare un intervallo di dimensioni variabili
Un modo per copiare intervalli di dimensioni variabili è convertirli in intervalli denominati o Tabella Excel e utilizzare i codici come mostrato nella sezione precedente.
Ma se non puoi farlo, puoi usare CurrentRegion o la proprietà End dell’oggetto range.
Il codice seguente copierà la regione corrente nel foglio attivo e la incollerà in Sheet2.,
Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub
Se si desidera copiare la prima colonna di dati, impostare fino all’ultima cella piena e incollarlo nel Foglio2, è possibile utilizzare il seguente codice:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub
Se si desidera copiare le righe e colonne, è possibile utilizzare il seguente codice:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub
si noti che tutti questi codici non selezionare le celle, mentre sempre eseguito. In generale, troverai solo una manciata di casi in cui è effettivamente necessario selezionare una cella/intervallo prima di lavorarci.,
Assegnazione di intervalli alle variabili Oggetto
Finora, abbiamo utilizzato l’indirizzo completo delle celle (come le cartelle di lavoro(“Book2.xlsx”).Fogli di lavoro (”Foglio1”).Gamma(“A1”)).
Per rendere il codice più gestibile, è possibile assegnare questi intervalli alle variabili oggetto e quindi utilizzare tali variabili.
Ad esempio, nel codice seguente, ho assegnato l’intervallo di origine e di destinazione alle variabili oggetto e quindi ho usato queste variabili per copiare i dati da un intervallo all’altro.
Iniziamo dichiarando le variabili come oggetti Range., Quindi assegniamo l’intervallo a queste variabili usando l’istruzione Set. Una volta che l’intervallo è stato assegnato alla variabile, puoi semplicemente usare la variabile.
Inserire i dati nella cella vuota successiva (Utilizzando la casella di input)
È possibile utilizzare le caselle di input per consentire all’utente di inserire i dati.
Ad esempio, supponiamo di avere il set di dati qui sotto e si desidera inserire il record di vendita, è possibile utilizzare la casella di input in VBA. Usando un codice, possiamo assicurarci che riempia i dati nella riga vuota successiva.,
Il codice precedente utilizza la casella di input VBA per ottenere gli input dall’utente e quindi inserisce gli input nelle celle specificate.
Nota che non abbiamo usato riferimenti di cella esatti. Invece, abbiamo usato la proprietà End e Offset per trovare l’ultima cella vuota e riempire i dati in essa contenuti.
Questo codice è tutt’altro che utilizzabile. Ad esempio, se si immette una stringa di testo quando la casella di input richiede quantità o importo, si noterà che Excel lo consente. È possibile utilizzare una condizione If per verificare se il valore è numerico o meno e quindi consentirlo di conseguenza.,
Loop attraverso celle/intervalli
Finora possiamo aver visto come selezionare, copiare e inserire i dati in celle e intervalli.
In questa sezione, vedremo come scorrere un insieme di celle/righe/colonne in un intervallo. Questo potrebbe essere utile quando si desidera analizzare ogni cella ed eseguire alcune azioni basate su di essa.
Ad esempio, se si desidera evidenziare ogni terza riga nella selezione, è necessario scorrere e verificare il numero di riga., Allo stesso modo, se si desidera evidenziare tutte le celle negative cambiando il colore del carattere in rosso, è necessario scorrere e analizzare il valore di ogni cella.
Ecco il codice che scorrerà le righe nelle celle selezionate ed evidenzierà le righe alternative.
Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub
Il codice di cui sopra utilizza la funzione MOD per controllare il numero di riga nella selezione. Se il numero di riga è pari, viene evidenziato in colore ciano.
Ecco un altro esempio in cui il codice passa attraverso ogni cella e mette in evidenza le celle che hanno un valore negativo in esso.,
Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub
Nota che puoi fare la stessa cosa usando la formattazione condizionale (che è dinamica e un modo migliore per farlo). Questo esempio ha solo lo scopo di mostrarti come funziona il looping con celle e intervalli in VBA.
Dove inserire il codice VBA
Ti chiedi dove va il codice VBA nella tua cartella di lavoro di Excel?
Excel ha un backend VBA chiamato VBA editor. È necessario copiare e incollare il codice nella finestra del codice del modulo VB Editor.
Ecco i passaggi per farlo:
- Vai alla scheda Sviluppatore.,
- Fare clic sull’opzione Visual Basic. Questo aprirà l’editor VB nel backend.
- Nel riquadro Esplora progetti nell’editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice. Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
- Vai su Inserisci e clicca su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
- Copia e incolla il codice nella finestra del modulo.,
Potrebbero piacerti anche i seguenti tutorial di Excel:
- Lavorare con i fogli di lavoro usando VBA.
- Lavorare con le cartelle di lavoro usando VBA.
- Creazione di funzioni definite dall’utente in Excel.
- Per il ciclo successivo in Excel VBA-Una guida per principianti con esempi.
- Come utilizzare la funzione Excel VBA InStr (con ESEMPI pratici).
- Excel VBA Msgbox.
- Come registrare una macro in Excel.
- Come eseguire una macro in Excel.
- Come creare un componente aggiuntivo in Excel.,
- Cartella di lavoro macro personale di Excel / Salva & Usa le macro in tutte le cartelle di lavoro.
- Excel VBA Events-Una guida facile (e completa).
- Gestione degli errori di Excel VBA.
- Come ordinare i dati in Excel utilizzando VBA (una guida passo-passo).
- 24 Esempi di macro Excel utili per principianti VBA (pronti all’uso).