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
Thursday, February 4, 2010
Excel Autofill VBA emulation
This code emulates double click on bottom right corner of selected cell.
Ярлыки:
excel autofill double click vba
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment