Autofill to lastrow

Hi all,

I am sure that someone will be able to give me a very quick and simple answer on this one but I have hit a brick wall with it.

My spreadsheet is a large export from a database and I am using sort within macros to manipulate the data. So that I am able to return it to it's original sort I have inserted a column (A) and I want the numbers 1, 2, 3 etc to run down the column until the last row active in the spreadsheet.

So, my spreadsheet contains the following:
A1 = 1
A2 = 2
A3 = 3

What I want to do is autofill A1:A3 to lastrow.

how do I do this?

Thanks in advance,

Pedros.

Pedros

This will do what you require

Sub AutoFillCol()
Dim lRow As Long
lRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

Range("A1:A3").AutoFill Destination:=Range("A1:A" & lRow), Type:=xlFillDefault
End Sub

Hey,

You can simply just select the little box to the bottom right hand corner of the cells highlighted e.g a1 a2 a3 then drag all the way down.


however being as the rest of the data is sorted by a macro u can just add this code to the macro and should do the trick

Sub Macro1()
Range("A1") = 1
Range("A2") = 2
Range("A3") = 3
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A2653"), Type:=xlFillDefault
Range("A1:A65536").Select
Range("A1").Select
End Sub

Hi Pedros,

Using the last cell in column B as a marker, try the following:

Columns("A").ClearContents

Range("A1").Value = 1
Range("A2").Formula = "=R[-1]C+1"
Range("B65536").End(xlUp).Select
ActiveCell.Offset(0, -1).Formula = "*"
Range("A2").Select
Range("A2").Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A2", Range("A2").End(xlDown)).Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Select

Hope this helps.

Robert

Zygan

In your code you have used Range("A1:A2653")

Pedros required code to autofill to the last active row, your code does not identify the last active row.

Sub Macro1()
'assuming all cells are contiguous
Dim Rws As Integer
Columns("A:A").Insert Shift:=xlToRight
[a1] = "SortNo"
Rws = [a1].CurrentRegion.Rows.Count
[a2] = 1
Range(Cells(2, 1), Cells(Rws, 1)).DataSeries
End Sub

Thanks to everyone for replying!!


Mudraker and Trebor76, both of your solutions work perfectly.

I have included mudrackers in my macro and it is now working as I required.

Thanks again,

Pedros.