amma.NETtami

.NET walkabout
posts - 11, comments - 18, trackbacks - 0

Monday, October 20, 2008

Contrarre una selection (range) in Excel

Semplice funzione per Excel che consente di contrarre di n righe una selection (per intenderci, un range di celle) .

Risulta utile anche per spostarsi in su od in giù di n righe se la selection corrente è una singola cella.

Function CropSelection(Rows As Long) As Boolean
 
Dim sSelection As String
Dim iDollarPosition As Long
Dim sRow As String
Dim lRow As Long

    On Error GoTo CropSelection_Error

    sSelection = Selection.Address()
    
    iDollarPosition = InStrRev(sSelection, "$")
    
    sRow = Mid(sSelection, iDollarPosition + 1)
    lRow = CLng(sRow) - Rows
    sRow = Mid(sSelection, 1, iDollarPosition - 1) & CStr(lRow)
    
    Range(sRow).Select

    On Error GoTo 0
    CropSelection = True
    Exit Function

CropSelection_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CropSelection of Modulo Modulo1"

End Function

posted @ Monday, October 20, 2008 10:42 PM | Feedback (0) | Filed Under [ VBA ]

Powered by:
Powered By Subtext Powered By ASP.NET