Fill down Error

[J.W. Aldridge]

Normally, my code works fine if I have a few rows of data. However,
I've found that if there's only information in the first two rows (a-
f), I get an error (Run Time: AutoFill Method of range class failed).
Any way to get the code to fill down to row 2 and stop if there's no
more info and not give me an error?


This is the fill down code. The part that returns the error is the
line 7 - 9.
***********************
Range("F2").Select
ActiveCell.FormulaR1C1 = "Next day"
Range("F2").Select
With ThisWorkbook.Worksheets("2mindex")
Set rngData = .Range("e2:e" & .Cells(.Rows.Count, "e").End
(xlUp).Row)
Set rngFormula = .Range("f2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

************************

Here's the entire code just in case....

Sub rearrange_data()
..
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut Destination:=Columns("B:B")
Columns("B:B").Select
ActiveWindow.SmallScroll ToRight:=0
Columns("G:L").Select
Selection.Delete Shift:=xlToLeft
Range("F1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Next day"
Range("F2").Select
With ThisWorkbook.Worksheets("2mindex")
Set rngData = .Range("e2:e" & .Cells(.Rows.Count, "e").End
(xlUp).Row)
Set rngFormula = .Range("f2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With
Columns("A:F").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

 

[Joel]

See if this works. I think the problem is you were wipping out the formula
in F2. I think you headers should be in E1 and E2. Also you may have been
referencing more thean one workbook/worksheet because the workbooks and
worksheet were no specified. this would of been a problem if you had more
than one workbook opened or the sheet you werre modifying was not the active
worksheet.



Sub rearrange_data()


With ThisWorkbook.Worksheets("2mindex")

.Columns("B:B").Insert
.Columns("G:L").Delete
.Range("E1") = "Reason"
.Range("F1") = "Next day"
LastRow = .Cells(.Rows.Count, "e").End(xlUp).Row
Set rngData = .Range("e2:e" & LastRow)
Set rngFormula = .Range("f2")
rngFormula.Copy _
Destination:=.Range("F2:F" & LastRow)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("C2"), _
Order1:=xlAscending, _
Header:=xlYes

End With
End Sub

 

[J.W. Aldridge]

Thanx for your help...

Getting error on this

Compile error : Argument not optional

LastRow =

 

[Joel]

I don't know why bu I'm not getting email when people respond to my postings.
I'm not getting the error. Not sure why. Compare my posting against you
code. I think you may not of copied the code properly.

 

[ryguy7272]

Here are some samples from my personal VBA library. Hope you find something
here that you like...

AutoFill Down:
This is how to do it if you have selected the 2 cells:
Sub Autofill1()
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))
End Sub


And this is how to do it if you don't want to select the two cells (assumes
that column A is
otherwise blank)
Sub Autofill2()
Dim myR As Range
Set myR = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Resize(2, 1)
On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Offset(0, 1).End(xlDown).Offset(0, -1))
End Sub


Fill Down in Column E, Based on Used Range in Column D:
Sub fill()
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
Range("E5").AutoFill Range("E5:E" & lastrow)
End Sub


Fill Down Values in Column E, with Some Data Already in Column E, Based on
Number of Rows Filled in Column A
Sub AutoFill()
Dim CountRows As Double
Dim Iloop As Double
'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
CountRows = Cells(Rows.Count, "A").End(xlUp).Row
For Iloop = 2 To CountRows
If IsEmpty(Cells(Iloop, "E")) Then
Cells(Iloop, "E") = Cells(Iloop - 1, "E")
End If
Next Iloop
'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Fill Down Values:
Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub


HTH,
Ryan---