Autofill in Macro

[koobro]

All,

I have this code in a macro:
Selection.AutoFill Destination:=Range("E2:E463")
Range("E2:E463").Select

But E463 changes constantly. How can I modify the code to look for end of record.

Koobro

 

[kjv1611]

This is something I've used recently for what I think is the same thing you are trying to do (finding the last filled cell):

Sub SelectCells()
'This code is to automatically "activate" each cell in the Complaint Audit in the "Date Entered" column
'The reason for doing this is that for some reason, some of the entries are comming accross in the
'wrong format.  This process takes a few seconds, and seems slow, but it appears to work correctly.
' SelectCells Macro
' Macro recorded 10/30/2006 by mename
'

'
    Dim x As Long 'Row
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Summary" Then
        Else
            ws.Activate
            x = 21
            For x = 21 To 500
                [highlight]If Cells(x, 2).Formula = vbNullString Then
                    If Cells(x - 1, 2).Formula = vbNullString Then Exit For
                End If[/highlight]
                ws.Cells(x, 2).Select
                ws.Cells(x, 2).Formula = ws.Cells(x, 2).Formula
            Next x
            Cells(21, 2).Select
        End If
    Next ws
    
'    Range("A1").Select
'    ActiveCell.FormulaR1C1 = "d"
'    Range("A2").Select
End Sub

So, I first recorded a macro of selecting a cell just to make sure that I had that part down right. Then, I took out the definte cell selection, and used variables instead. In my case, I wanted to loop through several cells on several sheets, so I used a couple of loops for the whole deal. I've highlighted the section in the code that would apply to what I think you'd want.

Also, in my usage, I checked for 2 consecutive blank cells in the same row, as this particulare workbook had some empty records in between the records on purpose (it was for a coworker).

HTH,

kjv

 

[AnotherHiggins]

Koobro,

FYI, for future reference, macro questions should be posted in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

There are many techniques used to find the last row. See faq707-2115 or faq707-2112 for some ideas.

You can probably find a faster way than to loop through - no offense, kjv1611. Also, you should avoid using Select whenever possible as it also will slow down your code.

Let's say, for example, that you want to put a formula in column E as far down as column D is populated. Then this will work:

Range("E2:E" & Range("D" & ActiveSheet.Rows.Count).End(xlUp).Row) = "=RC[-1]+RC[-2]"

NOTE: That formula adds the cell one column to the left to the cell two columns to the left.

Or is you really want to autofill, then this will work:

Range("E2").AutoFill Destination:=Range("E2:E" & Range("D" & ActiveSheet.Rows.Count).End(xlUp).Row)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kjv1611]

anotherhiggens, thanks for the post. I agree, that in the event that the Select is not needed, it DEFINITELY should not be used. It will terribly slow things down. In my particular use, some data being pasted into Excel just would not format correctly without manually going through and selecting each cell. So, as appsoed to racking over my brains and others, I just coded the macro to purposely loop through and find every cell, and select it - to sort of "trick" Excel into auto-formatting the values. It was a strange one, and maybe one day, I'll actually take the time to fix the real problem, but for now, it seemed easiest to do it that way.

koobro, you'll probably get faster results from the methods of which anotherhiggens mentioned by far. It is only when needing to actually look at every cell, and select the data therein, that you would need to use my method as posted.