La proprietà End

Ovvero : come reperire l'ultima cella occupata in un intervallo (in una colonna o in una riga, o in un'area).

Continuano a pervenire domande su come individuare la fine di un elenco di dati, quando si lavora con elenchi la cui lunghezza è variabile, e quindi non si conosce il riferimento alla cella finale. Su sito esistono moltissime routine che usano End per reperire i range su cui agire, ma probabilmente è opportuno evidenziare l'argomento con una pagina dedicata. Vediamo cosa dice la guida in linea:

Proprietà End (da non confondere con l'Istruzione End, che è tutta un'altra cosa)

Restituisce un oggetto Range che rappresenta la cella alla fine dell'area contenente l'intervallo di origine.

espressione Argomento necessario. Un'espressione che restituisce un oggetto nell'elenco.

Direction Argomento necessario di tipo XlDirection. Specifica la direzione dello spostamento.
XlDirection è una delle seguenti costanti:

xlDown     -   verso il basso
xlToRight -   verso destra
xlToLeft   -   verso sinistra
xlUp       -   verso l'alto

Ora vediamo di tradurre in "pellegrinese" queste istruzioni, aiutandoci con esempi. Supponiamo di avere nella colonna B dalla cella B1 fino alla cella B10 un elenco di dati: un'istruzione così compilata:

identificherebbe la cella finale dell'elenco, la B10, e la selezionerebbe. Come si nota, noi abbiamo quindi indicato nell'istruzione la cella iniziale dell'elenco (la B1), e l'istruzione, grazie all'uso di End con la direzione verso il basso (xlDown), trova la fine dell'ultima cella occupata dell'elenco. Che cosa vuol dire questo:

che quando non sappiamo quanto sarà lungo un elenco, ma vogliamo identificare l'ultima cella occupata, è sufficiente indicare la cella iniziale più End per trovare senza fallo la cella finale. Come condizione determinante per il corretto reperimento della cella finale occupata, dovremo disporre di un elenco SENZA celle vuote nel mezzo. Se nell'elenco ipotizzato prima (B1:B10) avessimo avuto la cella B8 vuota, senza dati, l'esecuzione dell'istruzione avrebbe identificato la B7 come ultima cella occupata, trascurando di proseguire nonostante la B9 e la B10 contengano dati. Chiaro?

Un altro modo di reperire l'ultima cella occupata in un elenco, è quella di usare la funzione End con col costante xlUp, cioè verso l'alto. Ovviamente si dovrà indicare come cella di origine la riga dell'ultima cella di un foglio, la riga 65536. Questa costante rintraccerà l'ultima cella occupata salendo verso l'alto, anche se tra questa e l'inizio elenco ci saranno celle vuote. Quindi, a differenza dell'uso di End con la costante verso il basso (xlDown), che si fermerebbe se esistono celle vuote tra i dati dell'intervallo, questa sicuramente restituisce l'ultima cella occupata. Da non usare però se nei nostri fogli esistono dati non riguardanti, su righe SOTTO l'esatto intervallo che ci interessa valutare.

Questo esempio sopra restituisce il numero dell'ultima riga (Row) occupata, colonna A. Utile quando si debba assegnare il numero di riga come limite superiore di un ciclo For..Next che debba scorrere un intervallo di cui non si conosce a priori la lunghezza.

Uno degli utilizzi classici della proprietà End (dell'oggetto Range) è la identificazione di un'area dati quando non sappiamo quanto è ampia l'area stessa, quando aggiungendo o togliendo righe di dati (o colonne), non possiamo determinarne a priori i riferimenti. Parlo dei riferimenti alle celle che contengono tutta l'area dati, di cui abitualmente usiamo indicare la cella iniziale e la cella finale che comprendono l'area. Se abbiamo un'area che va dalla prima cella della colonna A alla 22esima cella della colonna G noi usiamo questa sintassi:

Range("A1:G22").Select - indichiamo appunto gli estremi dell'area per selezionarla.

Ma quando non conosciamo quale sarà la cella finale e vorremo usare End per reperirla, dovremo usare una sintassi diversa, legata a Range non come "oggetto" del foglio di lavoro, ma come "Insieme Range" (cioè un insieme di celle). In quest'ottica, "l'Insieme Range" possiede delle proprietà, due delle quali sono lo stesso Range (come proprietà dell'Insieme Range), e la proprietà Cells. Servono entrambi per definire le celle, e possiamo usare indifferentemente l'uno o l'altro purchè non mescolati insieme. Vediamo prima l'utilizzo della prorietà Range:

intanto vediamo che la differenza maggiore rispetto alla precedente istruzione è che non usiamo i due punti (:) come separatori tra le celle che delimitano l'area, ma una virgola; questo perchè la proprietà Range necessita di argomenti (per definire le celle), e come tali devono essere separati dalla virgola. Comunque vediamo che per identificare il secondo argomento (la cella finale), abbiamo usato End riferito alla cella iniziale della colonna G, la G1: in questo modo verrà cercata l'ultima cella occupata nella colonna G, e sarà questa cella a fornire il secondo riferimento.

Proprietà Cells
Utilizzare Cells(row, column) dove row è l'indice di riga e column è l'indice di colonna, per restituire una singola cella.
dovremmo usare questa istruzione:

dove il primo argomento va letto come cella riga 1 colonna 1 (quindi A1), e il secondo argomento come cella riga 1 colonna 7 (e quindi G1). Anche in questo caso gli argomenti sono separati da virgola, e al secondo argomento è stata "affibbiata" End per reperire l'indice riga dell'ultima cella nella stessa colonna (la 7) che conterrà i dati.

Impostando opportunamente le direzioni, potremo cercare sia celle in righe che in colonne, sia verso il basso sia verso l'alto, che a destra o a sinistra, dipenderà da come avremo impostato le nostre tabelle dati. Ciò che poi faremo con le aree identificate e selezionate non riguarda questa pagina, ma propongo un esempio, peraltro riscontrabile scaricando il file "TraduttoreFunzioni2000.zip" (vedi articolo "Traduttore Funzioni"), dove, dovendo prevedere l'inserimento di dati, nell'ordinamento alfabetico dei dati, viene usata la Proprietà End per reperire tutta l'area da ordinare. In questo caso, dovendo ordinare secondo una chiave specifica, e volendo che i dati correlati alla chiave, sulle stesse righe, si muovano insieme, vengono usate delle variabili (x e y) alle quali assegnare gli indirizzi (o riferimenti) delle celle che comprendono l'area. Alla prima variabile assegniamo il range che va dalla cella A1 alla C1, prendendo gli indirizzi (Address) ; le tre colonne che comprendono l'area dati:

con la seconda variabile reperiamo, tramite End sulla prima colonna, l'indirizzo (Address) dell'ultima cella occupata:

poi selezioniamo tutta l'area usando i riferimenti (indirizzi o Address) ora "presenti" nelle due variabili :

Se andiamo a controllare (con "imposta punto di interruzione" e successive pressioni sul tasto F8 possiamo scorrere le istruzioni riga per riga), vediamo infatti che in Range(y, x) , la y corrisponde a "$A$1:$C$1"

Sub ordinaitaliano()
Worksheets("Foglio2").Select
Dim x, y
y = Range("A1:C1").Address
x = Range("A1").End(xlDown).Address
Range(y, x).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Se invece, anche per le colonne non sapessimo quale sarà l'ultima che ci consenta di definire un riferimento preciso, potremo usare End dando come direzione xlToRight (verso destra) riferito alla cella iniziale (A1), e basterà sostituire questa riga nella routine appena vista, per ottenere la nostra selezione:

cioè, in entrambi i casi, stiamo dicendo : y è uguale al Range che parte dalla cella A1 fino all'ultima cella occupata partendo dalla A1, andando verso destra (quindi stessa riga).