AutoFill to Last Row in Column A

[longbow007]

Hello, could some one please help modify the code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1

If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...

Any assistance is greatly appreciated.

Kind regards,

Chris

Sub Data_to_Text()
'
' Data to Text Macro
' Macro recorded 9/02/2010
'

'
    Dim myRange As Range
    Dim lr As Long

    
    Set myRange = Selection
    
    Sheets("Data").Select
    
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
    
    Selection.Copy
    
    Sheets("Data to Text").Select
    
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
    Application.CutCopyMode = False
    
    Selection.Insert Shift:=xlToRight
    
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
               
    lr = Cells(Rows.Count, "a").End(xlUp).Row
    
    Range("A1:A2").Select
    
    Range(Cells(lr, "a"), Cells(lr, "a")). _
    AutoFill Range(Cells(lr, "a"), Cells(lr + 2, "a"))
           
    
End Sub

[longbow007]

Hello, I thought I would do a screen-capture of "dummy-data" to help in getting a solution.

The attached screen-capture shows the end-result:

Where the user made multiple selection of data on the worksheet: Data

and the macro then:

Copied 'n' pasted the selected multiple data into the worksheet: Data to Text.

A new column was inserted in column A.

We manually entered the numbers 1, 2, & 3 in column A.

We need to AutoFill column A to the last row in worksheet: Data to Text.

Hope this helps,

Kind regards,

Chris

[longbow007]

Hello again,

I have been searching the Internet and believe that I may have a solution to my problem as shown below

Sub Data_to_Text()
'
' Data to Text Macro
' Macro recorded 9/02/2010
'

'
    Dim myRange As Range
    Dim LastRow As Long
       
    Set myRange = Selection
    
    Sheets("Data").Select
    
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
    
    Selection.Copy
    
    Sheets("Data to Text").Select
    
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
    Application.CutCopyMode = False
    
    Selection.Insert Shift:=xlToRight
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row

    Range("A2").AutoFill Destination:=Range("A2:A" & LastRow)
       
End Sub