autofill and vba in excel 1

[SharonMee]

Can you please help me?
I am new to vba, so please bear with me.

I have the following code in my macro:

Range("J2:N2").Select
Selection.AutoFill Destination:=Range("J2:N23")

I think the first row of the code is copying the formula in row 2 of columns J to N. I think the 2nd row of the code is autofilling the cells from 2nd row to row 23.

My problem is: what if I have more rows than 23 rows, how should this code look if I want to fill the formula in row 2 to the end of the rows available.

Thanks very much for helping.


SharonMee

 

[Molby]

Sharon,

Not really the right Forum but this should help. What you need to do is find the last row that you want, you can do this in several ways. The code below goes to the last cell, as if you were using Ctrl and End. You can then use the activecell.row value to determine where the autofill should stop.

sub test
ActiveCell.SpecialCells(xlLastCell).Select
ro$ = activecell.row
Range("J2:N2").Select
Selection.AutoFill Destination:=Range("J2:N" & ro$)
End sub

 

[SharonMee]

Hi Molby,

This is what I did:

I replaced:
Range("J2:N2").Select
Selection.AutoFill Destination:=Range("J2:N23")

with:

ActiveCell.SpecialCells(xlLastCell).Select
ro$ = activecell.row
Range("J2:N2").Select
Selection.AutoFill Destination:=Range("J2:N" & ro$)

The code works okay except that it also inserts the formula in J2:N2 to row 44, but there is currently no data in that row, presently the data I am running this on has 23 rows, (I only want to do an autofill for cases when I have more or less than 23 rows) do you know why I am getting this result?

Thanks for your patience.


SharonMee

 

[Molby]

Sharon,

The problem with using ActiveCell.SpecialCells(xlLastCell).Select in your code is that it if you have deleted data on the sheet, Excel will still include it in selecting the final cell.
How do you know currently that you only have to fill 23 rows. Is this based on other data in other columns? If so, you can go to the bottom of that column, say B, and find the last row from there.

Range("b1").select
selection.end(xldown).select
ro$ = Activecell.row
etc

I guess what I saying is on what basis are you defining your autofill?

 

[SharonMee]

Thanks much, Molby.

Yes, the last row is based on the data of other columns, column B always has data in it. Thank you for providing that code, then it can work for different number of rows.


Thanks again.


SharonMee