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