Autofill to last cell in other column

A

Alex

How can I autofill the selected cell down in the selected column to the last
cell in the column to the right? I'd also like to exclude cells where the
cell to the right is null. Thank you very much.
 
J

John Howard

Hi Alex,

Assuming that you want to create the autofill programatically, rather than
manualy, the macro below should give you what you want, except the exluding
of null cells.

The only way to effect such an exlusion would be to delete the null rows
before the autofill.

Sub FillToLastRow()

Dim LastRow As Long
Dim FillRange As String
Dim FillColumn As Long


'###### Firstly Select the item you want to copy in the autofill column
'then run this macro

'Determines the column number of rhe selcted cell
FillColumn = ActiveCell.Column

'Determines the last used row in the
'first column to the right of the selected
'autofill column
LastRow = Cells(Rows.Count, FillColumn + 1).End(xlUp).Row

'Creates the autofill range address in string format
FillRange = Range(Cells(ActiveCell.Row, FillColumn), _
Cells(LastRow, FillColumn)).Address

'Autofills the selected column
'to the last row number, determined
'from the next column to the right
ActiveCell.AutoFill Destination:=Range(FillRange)

End Sub
 
A

Alex

This is perfect! Thank you so much.

John Howard said:
Hi Alex,

Assuming that you want to create the autofill programatically, rather than
manualy, the macro below should give you what you want, except the exluding
of null cells.

The only way to effect such an exlusion would be to delete the null rows
before the autofill.

Sub FillToLastRow()

Dim LastRow As Long
Dim FillRange As String
Dim FillColumn As Long


'###### Firstly Select the item you want to copy in the autofill column
'then run this macro

'Determines the column number of rhe selcted cell
FillColumn = ActiveCell.Column

'Determines the last used row in the
'first column to the right of the selected
'autofill column
LastRow = Cells(Rows.Count, FillColumn + 1).End(xlUp).Row

'Creates the autofill range address in string format
FillRange = Range(Cells(ActiveCell.Row, FillColumn), _
Cells(LastRow, FillColumn)).Address

'Autofills the selected column
'to the last row number, determined
'from the next column to the right
ActiveCell.AutoFill Destination:=Range(FillRange)

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top