Questo articolo dimostrerà come creare un intervallo dinamico in Excel VBA.
Dichiarare un intervallo specifico di celle come variabile in Excel VBA ci limita a lavorare solo con quelle celle particolari. Dichiarando gli intervalli dinamici in Excel, otteniamo molta più flessibilità rispetto al nostro codice e alle funzionalità che può eseguire.
Riferimento a intervalli e celle
Quando facciamo riferimento all'oggetto Range o Cell in Excel, normalmente ci riferiamo ad essi codificando nella riga e nelle colonne richieste.
Proprietà dell'intervallo
Utilizzando la proprietà Range, nelle righe di codice di esempio seguenti, possiamo eseguire azioni su questo intervallo come cambiare il colore delle celle o rendere le celle in grassetto.
| 12 | Intervallo("A1:A5").Font.Color = vbRedIntervallo("A1:A5").Font.Bold = True |
Proprietà delle celle
Allo stesso modo, possiamo usare la proprietà Cells per fare riferimento a un intervallo di celle facendo riferimento direttamente alla riga e alla colonna nella proprietà delle celle. La riga deve essere sempre un numero, ma la colonna può essere un numero o una lettera racchiusa tra virgolette.
Ad esempio, l'indirizzo di cella A1 può essere referenziato come:
| 1 | Celle(1,1) |
o
| 1 | Celle(1, "A") |
Per utilizzare la proprietà Cells per fare riferimento a un intervallo di celle, è necessario indicare l'inizio dell'intervallo e la fine dell'intervallo.
Ad esempio per fare riferimento all'intervallo A1: A6 potremmo usare questa sintassi di seguito:
| 1 | Intervallo(Celle(1,1), Celle(1,6) |
Possiamo quindi utilizzare la proprietà Cells per eseguire azioni sull'intervallo secondo le righe di codice di esempio seguenti:
| 12 | Intervallo(Celle(2, 2), Celle(6, 2)).Font.Color = vbRedIntervallo(Celle(2, 2), Celle(6, 2)).Font.Bold = True |
Gamme dinamiche con variabili
Poiché la dimensione dei nostri dati cambia in Excel (cioè usiamo più righe e colonne degli intervalli che abbiamo codificato), sarebbe utile se anche gli intervalli a cui ci riferiamo nel nostro codice dovessero cambiare. Utilizzando l'oggetto Range sopra, possiamo creare variabili per memorizzare i numeri massimi di riga e colonna dell'area del foglio di lavoro Excel che stiamo utilizzando e utilizzare queste variabili per regolare dinamicamente l'oggetto Range mentre il codice è in esecuzione.
Per esempio
| 1234 | Dim lRiga come numero interoDim lCol come interolRiga = Intervallo("A1048576").Fine(xlUp).RigalCol = Range("XFD1").End(xlToLeft).Column |
Ultima riga in colonna
Poiché ci sono 1048576 righe in un foglio di lavoro, la variabile lRow andrà in fondo al foglio e quindi utilizzerà la combinazione speciale del tasto Fine più il tasto Freccia su per andare all'ultima riga utilizzata nel foglio di lavoro - questo ci darà il numero della riga di cui abbiamo bisogno nel nostro intervallo.
Ultima colonna nella riga
Allo stesso modo, lCol si sposterà nella colonna XFD che è l'ultima colonna in un foglio di lavoro, quindi utilizzerà la combinazione di tasti speciale del tasto Fine più il tasto Freccia sinistra per andare all'ultima colonna utilizzata nel foglio di lavoro - questo ci darà il numero della colonna di cui abbiamo bisogno nel nostro intervallo.
Pertanto, per ottenere l'intero intervallo utilizzato nel foglio di lavoro, possiamo eseguire il seguente codice:
| 1234567891011 | Sub GetRange()Dim lRow As IntegerDim lCol As IntegerDim rng come intervallolRiga = Intervallo("A1048576").Fine(xlUp).Riga'usa lRow per aiutare a trovare l'ultima colonna nell'intervallolCol = Range("XFD" & lRow).End(xlToLeft).ColumnImposta rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox per mostrarci la gammaMsgBox "L'intervallo è " & rng.AddressFine sottotitolo |
SpecialCells - LastCell
Possiamo anche usare il metodo SpecialCells dell'oggetto Range per ottenere l'ultima riga e colonna utilizzata in un foglio di lavoro.
| 123456789101112 | Sub UseSpecialCells()Dim lRow As IntegerDim lCol As IntegerDim rng come intervalloDim rng Inizia come intervalloImposta rngBegin = Intervallo ("A1")lRow = rngBegin.SpecialCells(xlCellTypeLastCell).RowlCol = rngBegin.SpecialCells(xlCellTypeLastCell).ColumnImposta rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox per mostrarci la gammaMsgBox "L'intervallo è " & rng.AddressFine sottotitolo |
UsatoRange
Il metodo dell'intervallo utilizzato include tutte le celle che contengono valori nel foglio di lavoro corrente.
| 123456 | SottoutilizzatoRangeEsempio()Dim rng come intervalloImposta rng = ActiveSheet.UsedRange'msgbox per mostrarci la gammaMsgBox "L'intervallo è " & rng.AddressFine sottotitolo |
Regione attuale
La regione corrente differisce da UsedRange in quanto guarda le celle che circondano una cella che abbiamo dichiarato come un intervallo di partenza (cioè la variabile rngBegin nell'esempio sotto), e poi guarda tutte le celle che sono "attaccate" o associate a quella cella dichiarata. Se si verifica una cella vuota in una riga o colonna, CurrentRegion smetterà di cercare ulteriori celle.
| 12345678 | Sottoregione attuale()Dim rng come intervalloDim rng Inizia come intervalloImposta rngBegin = Intervallo ("A1")Imposta rng = rngBegin.CurrentRegion'msgbox per mostrarci la gammaMsgBox "L'intervallo è " & rng.AddressFine sottotitolo |
Se utilizziamo questo metodo, dobbiamo assicurarci che tutte le celle nell'intervallo richiesto siano collegate senza righe o colonne vuote tra di loro.
Gamma denominata
Possiamo anche fare riferimento a Named Ranges nel nostro codice. Gli intervalli con nome possono essere dinamici nella misura in cui quando i dati vengono aggiornati o inseriti, il nome dell'intervallo può cambiare per includere i nuovi dati.
Questo esempio cambierà il carattere in grassetto per il nome dell'intervallo "Gennaio"
| 12345 | Nomeintervallo secondarioEsempio()Dim rng come intervalloImposta rng = Intervallo ("Gennaio")rng.Font.Bold = = VeroFine sottotitolo |
Come vedrai nell'immagine qui sotto, se una riga viene aggiunta al nome dell'intervallo, il nome dell'intervallo si aggiorna automaticamente per includere quella riga.
Se dovessimo quindi eseguire nuovamente il codice di esempio, l'intervallo interessato dal codice sarebbe C5:C9 mentre in prima istanza sarebbe stato C5:C8.
Tabelle
Possiamo fare riferimento alle tabelle (fare clic per ulteriori informazioni sulla creazione e la manipolazione di tabelle in VBA) nel nostro codice. Quando i dati di una tabella in Excel vengono aggiornati o modificati, il codice che fa riferimento alla tabella farà quindi riferimento ai dati della tabella aggiornati. Ciò è particolarmente utile quando si fa riferimento a tabelle pivot connesse a un'origine dati esterna.
Usando questa tabella nel nostro codice, possiamo fare riferimento alle colonne della tabella dalle intestazioni in ogni colonna ed eseguire azioni sulla colonna in base al loro nome. Man mano che le righe nella tabella aumentano o diminuiscono in base ai dati, l'intervallo della tabella si adatterà di conseguenza e il nostro codice continuerà a funzionare per l'intera colonna della tabella.
Per esempio:
| 123 | Sub DeleteTableColumn()ActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").ListColumns("Fornitore").EliminaFine sottotitolo |
