relative reference to replace fixed reference in code

[j3andc]

I have a macro with the following line of code

Selection.AutoFill Destination:=Range("E2:E390")

The problem is that the number of rows will not aways be the same. How do I change the code so that it finds the bottow rather than me telling it where the bottom is.

Thanks in advance for any help.

 

[buxtongt]

Is the column next to this one (ie. column D) the correct length?

 

[j3andc]

Yes, all of the columns contain the same amount of data.

in the code, column E is created new and then a formula is populated into the new column for every row that contains data.

 

[buxtongt]

You could use something like this:

Range(Selection, Selection.End(xlDown)).Select

in your code to just move to the end. You could also make the formula leave the cell blank when nothing should be displayed there and then copy and paste values for the entire column after that. Hope this helps.

 

[jim may]

Sub Foo()
Set SourceRange = Worksheets("Sheet1").Range("E1")
LR = Range("A" & Rows.Count).End(xlUp).Row
Set fillRange = Worksheets("Sheet1").Range("E1:E" & LR)
SourceRange.AutoFill Destination:=fillRange
End Sub

 

[j3andc]

With this command (Range(Selection, Selection.End(xlDown)).Select), the selection range goes well beyond the last row of data.

 

[buxtongt]

Select the top cell in the column before that line. It's directly out of the macro recorder, so it can be cleaned up some as far as the VB goes, but it should do what you need.