Thursday, February 4, 2010

Excel Autofill VBA emulation

This code emulates double click on bottom right  corner of selected cell.
Sub Autofill()
    On Error Resume Next
    Dim checkRange As Range
    
    If RangeState(Selection) = 2 Then: Exit Sub
    
    selMaxRow = Selection.Row + Selection.Rows.Count - 1
    selColsCount = Selection.Columns.Count
    selRowsCount = Selection.Rows.Count
    usedRows = ActiveSheet.UsedRange.Rows.Count
    maxrow = selMaxRow
    row_offset = 0
        
    checkLeft = False
    checkRight = False
    checkCenter = False
    If Selection.Column > 1 Then
        If RangeState(Selection.Offset(0, -1).Resize(selRowsCount, 1)) = 1 Then
            checkLeft = True
        End If
    End If
    If Not checkLeft Then
        If (Selection.Column + selColsCount) < ActiveSheet.Cells.Columns.Count Then
            If RangeState(Selection.Offset(0, selColsCount).Resize(selRowsCount, 1)) = 1 Then: checkRight = True
        End If
    End If

    Set checkRange = Selection.Offset(selRowsCount).Resize(1, selColsCount)
    
    If Not checkLeft And Not checkRight Then
        If RangeState(checkRange) = 1 Then: checkCenter = True
    Else
        checkCenter = True
    End If
    
    For row_offset = 0 To usedRows - Selection.Row - selRowsCount
        If (selMaxRow + row_offset) < usedRows And checkCenter Then
            tmp = RangeState(checkRange.Offset(row_offset))
            If row_offset = 0 Then: center_state = tmp
            If tmp <> center_state Then: checkCenter = False
            If center_state = 3 Then: checkCenter = False
        End If
        
        If checkLeft And center_state = 2 Then
            left_state = RangeState(checkRange.Offset(row_offset, -1).Resize(1, 1))
            If left_state = 2 Or left_state = 3 Then: Exit For
        ElseIf checkRight And center_state = 2 Then
            right_state = RangeState(checkRange.Offset(row_offset, selColsCount).Resize(1, 1))
            If right_state = 2 Or right_state = 3 Then: Exit For
        End If
                
        If checkCenter Then: maxrow = maxrow + 1
    Next
    
    If maxrow > selMaxRow Then
        Selection.Autofill Destination:=Selection.Resize(maxrow - (Selection.Row) + 1)
        Selection.Resize(maxrow - Selection.Row + 1).Select
    End If
End Sub


Function RangeState(rng As Range)
    RangeState = 0
    If IsArray(rng.Formula) Then
        For Each Formula In rng.Formula
            If Formula <> "" Then
                RangeState = RangeState Or 1
            Else
                RangeState = RangeState Or 2
            End If
        Next
    Else
        If rng.Formula <> "" Then
            RangeState = 1
        Else
            RangeState = 2
        End If
    End If
End Function

No comments:

Post a Comment