AutoFill Macro

[eyeman6513_2000]

I thought that i posted this yesterday but I can't find the posting so
if this is a double post, please point me in the direction of the
original post and I will go from there.

I have the following Code:

Sub Autofill()
'
'
Range("B1").Select
Selection.Copy
Selection.AutoFill Destination:=Range("B1:B10")
Range("B1:B10").Select
Range("G17").Select
End Sub

I looked at previous postings on this group and found this topic
addressed before and I found code on how to do this so I attempted to
apply the following code:

Sub Test()
'
' Test Macro
'
Range("B1").Select
Dim endRow As Long
endRow = Cells(Rows.Count, ("B1:B")).End(xlUp).Row
ActiveCell.AutoFill Destination:=Range("B1:B")
End Sub

However, it keeps errorring out, any suggestions?

Thank you.

Richard

 

[Ron de Bruin]

Use it like this for B1 in Sheet1
Note: it overwrite the data that is in B2:B ?

Sub test2()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

 

[Joergen Bondesen]

Hi Richard

Try below, please.

Sub test2()

Dim endRow As Long
endRow = Cells(Rows.Count, 2).End(xlUp).Row

Range("B1").Autofill Destination:=Range("B1:B" & endRow)
End Sub

 

[Don Guillett]

try

br = Cells(Rows.Count, "b").End(xlUp).Row
Cells(1, "b").autofill Destination:=Range(Cells(1, "b"), Cells(br, "b"))

 

[eyeman6513_2000]

I tried the one:

Sub test2()

Dim endRow As Long
endRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1").Autofill Destination:=Range("B1:B" & endRow)
End Sub

And I get the following error message:

Autofill method of range class failed.

When I tried:

Sub test2()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

I get the same message, what am I missing?

Thank you.

Richard

 

[Ron de Bruin]

You have only data in B1 of the sheet named Sheet1

LastRow = .Cells(Rows.Count, "B").End(xlUp).Row

This also find B1 now

 

[eyeman6513_2000]

Ron,

Not clear on your last message, my macro reads:

Sub test2()
Range("B1").Select
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

Since my data is only in B1, that is what I want to autofill from, I am
missing something basic, what is it?

 

[Ron de Bruin]

Hi

Since my data is only in B1, that is what I want to autofill from, I am
missing something basic, what is it?

The code is looking for the last row with data in column B

LastRow = .Cells(Rows.Count, "B").End(xlUp).Row

It use that row now to AutoFill

.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault

If you only have data in B1 it try to AutoFill B1 to B1 (error)

You can use another column to look for the last row
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

Or if you know the last row use that in the code

Sub test2()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = 20
.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

 

[Joergen Bondesen]

Hi Richard

Sorry about my bad programming.
I hope below will suits you better.


Option Explicit

Const Startcell As String = "B1"
Const SheetName As String = "Test1"

'----------------------------------------------------------
' Procedure : FillDown
' Date : 20060819
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Fill down Startcell to lastrow if Startcell
' <> empty.
' Note : Change Const: 'Startcell' and 'SheetName'
'----------------------------------------------------------
'
Sub FillDown()

Dim WS As Worksheet
Dim Srange As Range
Dim Lastrow As Long

Set WS = Worksheets(SheetName)
Set Srange = WS.Range(Startcell)

With WS
'// Empty Startcell
If Srange = vbNullString Then End

'// Lastrow
Lastrow = .Cells(.Rows.Count, _
Srange.Column).End(xlUp).Row
'// Startrow = Lastrow
If Lastrow = Srange.Row Then End

'// Fill down
.Range(Startcell).AutoFill _
Destination:=.Range(.Cells(Srange.Row, _
Srange.Column), _
.Cells(Lastrow, Srange.Column))
End With

Set WS = Nothing
Set Srange = Nothing
End Sub