maurogsc.eu mauro gamberini

home ==> Excel: articoli ==> Righe - Ultima riga con dati in una colonna

Righe - Ultima riga con dati in una colonna

Situazione.
In una colonna vogliamo trovare l'ultima riga in cui si trova un dato. La cosa è fattibile in diversi modi. ALT+F11 e siamo nell’editor del Visual Basic. Inseriamo un modulo standard nel nostro progetto(Inserisci-->Modulo) sul quale andremo poi a copia incollare il codice.

Nota.
Il codice è fornito *così come è* e l’autore declina ogni responsabilità su eventuali problemi causati da un suo uso improprio. Utilizzate files di test per le vostre prove. Il codice è stato testato con(e quindi valido per) Excel 2000/Xp(2002)/2003/2007/2010.

Utilizzare Range in una macro.
Semplice macro che trova l'ultima riga della colonna A del Foglio1:

Public Sub mUltimaRiga()
  
'gestione errori
On Error GoTo RigaErrore
    
    'dichiarazione variabili
    Dim sh As Worksheet
    Dim lUltRiga As Long
    
    'assegno il riferimento al Foglio1
    Set sh = Worksheets("Foglio1")
    
    With sh
        'trovo l'ultima riga con
        'dati della colonna A
        'e passo il suo numero
        'alla variabile lUltRiga
        lUltRiga = .Range( _
            "A" & Rows.Count).End(xlUp).Row
    End With
    
    'mostro la riga trovata in un MsgBox
    MsgBox CStr(lUltRiga)

'riga sempre eseguita
RigaChiusura:
    'Set a Nothing della variabile oggetto
    Set sh = Nothing
    Exit Sub
   
'in caso di errore
RigaErrore:
    'MsgBox con numero di errore e sua descrizione
    MsgBox Err.Number & vbNewLine & Err.Description
    'vado a RigaChiusura
    Resume RigaChiusura
    
End Sub

Attenzione. Il risultato sarà 1 sia che la prima cella della colonna sia l'unica cella con un valore, sia che la colonna abbia tutte le celle vuote. Se vogliamo evitare che la colonna vuota restituisca 1, possiamo modificare così la nostra macro:

Public Sub mUltimaRiga()
  
'gestione errori
On Error GoTo RigaErrore
    
    'dichiarazione variabili
    Dim sh As Worksheet
    Dim lUltRiga As Long
    
    'assegno il riferimento al Foglio1
    Set sh = Worksheets("Foglio1")
    
    With sh
        'trovo l'ultima riga con
        'dati della colonna A
        'e passo il suo numero
        'alla variabile lUltRiga
        lUltRiga = .Range( _
            "A" & Rows.Count).End(xlUp).Row
        'se il valore è 1
        If lUltRiga = 1 Then
            'se la cella non contiene un dato
            If Len(.Range("A1").Value) = 0 Then
                'restituisci 0
                lUltRiga = 0
            End If
        End If
    End With
    
    'mostro la riga trovata in un MsgBox
    MsgBox CStr(lUltRiga)

'riga sempre eseguita
RigaChiusura:
    'Set a Nothing della variabile oggetto
    Set sh = Nothing
    Exit Sub
   
'in caso di errore
RigaErrore:
    'MsgBox con numero di errore e sua descrizione
    MsgBox Err.Number & vbNewLine & Err.Description
    'vado a RigaChiusura
    Resume RigaChiusura
    
End Sub

Utlizzare Range in una Function.
Possiamo ottenere la stessa cosa tramite una Function:

Public Function fUltimaRiga( _
    ByVal sColonna As String, _
    Optional ByRef vNome As Variant) _
    As Long
    
    'se il parametro sh viene omesso
    If IsMissing(vNome) Then
        'considera il foglio attivo
        With ActiveSheet
            'la funzione restituisce il
            'numero di riga
            fUltimaRiga = _
                .Range(sColonna & _
                .Rows.Count).End(xlUp).Row
        End With
    'se presenti entrambi i parametri
    Else
        'considera il foglio passato come
        'parametro
        With Worksheets(CStr(vNome))
            'la funzione restituisce il
            'numero di riga
            fUltimaRiga = _
                .Range(sColonna & _
                .Rows.Count).End(xlUp).Row
        End With
    End If

End Function

La Function si aspetta un parametro obbligatorio(la colonna) come String e un parametro opzionale(il nome del foglio) come Variant. Il secondo parametro, per evitare problemi, va comunque passato come String.
Attenzione. Se non passo alla funzione il secondo parametro(quello relativo al foglio), mi verrà considerato il foglio attivo in quel momento. Possiamo richiamare la funzione da codice così:

Public Sub mUnParametro()
    'in un MsgBox il valore restituito
    'dalla Function
    MsgBox CStr(fUltimaRiga("A"))
End Sub

Public Sub mDueParametri()
    'in un MsgBox il valore restituito
    'dalla Function
    MsgBox CStr(fUltimaRiga("A", "Foglio1"))
End Sub

Se la nostra Function si trova dichiarata come Public in un modulo standard, possiamo utilizzarla come UDF(User Defined Function) anche dalle celle di un foglio, ad esempio:
C1: =fUltimaRiga("A")
oppure:
C1: =fUltimaRiga("A";"Foglio3")

Anche in questo caso dobbiamo considerare quanto già visto in precedenza: il risultato sarà 1 sia che la prima cella della colonna sia l'unica cella con un valore, sia che la colonna abbia tutte le celle vuote. Se vogliamo evitare che la Function restituisca 1 in caso di colonna vuota, modifichiamo il codice che richiama la Function e non la Function, ad esempio:

Public Sub mUltRiga()

    'dichiaro una variabile
    Dim lng As Long
    
    'passo alla variabile il valore
    'restituito dalla funzione
    lng = fUltimaRiga("A", "Foglio1")
    
    'se la cella è la prima della colonna
    If lng = 1 Then
        'se non contiene un valore
        If Len(Worksheets( _
            "Foglio1").Range("A1").Value) = 0 Then
            'la mia variabile avrà valore 0
            lng = 0
        End If
    End If
    
    'in un MsgBox il valore della variabile lng
    MsgBox CStr(lng)
    
End Sub

Utillare Cells in una macro.
Quanto fatto sopra utilizzando Range, possiamo farlo utilizzando Cells. Vediamo come.

Public Sub mUltimaRiga()
  
'gestione errori
On Error GoTo RigaErrore
    
    'dichiarazione variabili
    Dim sh As Worksheet
    Dim lUltRiga As Long
    
    'assegno il riferimento al Foglio1
    Set sh = Worksheets("Foglio1")
    
    With sh
        'trovo l'ultima riga con
        'dati della colonna A
        'e passo il suo numero
        'alla variabile lUltRiga
        lUltRiga = .Cells( _
            .Rows.Count, 1).End(xlUp).Row
    End With
    
    'mostro la riga trovata in un MsgBox
    MsgBox CStr(lUltRiga)

'riga sempre eseguita
RigaChiusura:
    'Set a Nothing della variabile oggetto
    Set sh = Nothing
    Exit Sub
   
'in caso di errore
RigaErrore:
    'MsgBox con numero di errore e sua descrizione
    MsgBox Err.Number & vbNewLine & Err.Description
    'vado a RigaChiusura
    Resume RigaChiusura
    
End Sub

Attenzione. Anche qui risultato sarà 1 sia che la prima cella della colonna sia l'unica cella con un valore, sia che la colonna abbia tutte le celle vuote. Se vogliamo evitare che la colonna vuota restituisca 1, possiamo modificare così la nostra macro:

Public Sub mUltimaRiga()
  
'gestione errori
On Error GoTo RigaErrore
    
    'dichiarazione variabili
    Dim sh As Worksheet
    Dim lUltRiga As Long
    
    'assegno il riferimento al Foglio1
    Set sh = Worksheets("Foglio1")
    
    With sh
        'trovo l'ultima riga con
        'dati della colonna A
        'e passo il suo numero
        'alla variabile lUltRiga
        lUltRiga = .Cells( _
            Rows.Count, 1).End(xlUp).Row
        'se il valore è 1
        If lUltRiga = 1 Then
            'se la cella non contiene un dato
            If Len(.Cells(1, 1).Value) = 0 Then
                'restituisci 0
                lUltRiga = 0
            End If
        End If
    End With
    
    'mostro la riga trovata in un MsgBox
    MsgBox CStr(lUltRiga)

'riga sempre eseguita
RigaChiusura:
    'Set a Nothing della variabile oggetto
    Set sh = Nothing
    Exit Sub
   
'in caso di errore
RigaErrore:
    'MsgBox con numero di errore e sua descrizione
    MsgBox Err.Number & vbNewLine & Err.Description
    'vado a RigaChiusura
    Resume RigaChiusura
    
End Sub

Utlizzare Cells in una Function.
Possiamo ottenere la stessa cosa tramite una Function:

Public Function fUltimaRiga( _
    ByVal lColonna As Long, _
    Optional ByRef vNome As Variant) _
    As Long
    
    'se il parametro sh viene omesso
    If IsMissing(vNome) Then
        'considera il foglio attivo
        With ActiveSheet
            'la funzione restituisce il
            'numero di riga
            fUltimaRiga = _
                .Cells(.Rows.Count, _
                    lColonna).End(xlUp).Row
        End With
    'se presenti entrambi i parametri
    Else
        'considera il foglio passato come
        'parametro
        With Worksheets(CStr(vNome))
            'la funzione restituisce il
            'numero di riga
            fUltimaRiga = _
            fUltimaRiga = _
                .Cells(.Rows.Count, _
                    lColonna).End(xlUp).Row
        End With
    End If
    
End Function

La Function si aspetta questa volta un parametro obbligatorio(la colonna) come Long e un parametro opzionale(il nome del foglio) come Variant. Il secondo parametro, per evitare problemi, va comunque passato come String.
Attenzione. Se non passo alla funzione il secondo parametro(quello relativo al foglio), mi verrà considerato il foglio attivo in quel momento. Possiamo richiamare la funzione da codice così:

Public Sub mUnParametro()
    'in un MsgBox il valore restituito
    'dalla Function
    MsgBox CStr(fUltimaRiga(1))
End Sub

Public Sub mDueParametri()
    'in un MsgBox il valore restituito
    'dalla Function
    MsgBox CStr(fUltimaRiga(1, "Foglio1"))
End Sub

Anche in questo caso è possibile utilizzare la Function come UDF:
C1: =fUltimaRiga(1)
oppure:
C1: =fUltimaRiga(1;"Foglio3")

E' possibile scaricare un file con un semplice esenpio qui. In pratica compare una InputBox nella quale immettere un dato che via via verrà inserito nella prima cella libera della colonna C del Foglio1.

Per ulteriori informazioni su Microsoft Excel ed il suo Visual Basic, utilizzate questo forum:
http://social.answers.microsoft.com/Forums/it-IT/officeexcelit/threads

Ultimo aggiornamento di questa pagina: 12/10/2009 - mauro gamberini