Autofill in Excel Macro

xxlisaxx

Hi there, I used to have an account on here a long time ago but can't for the life of me remember my username.
I am drawing a blank while writing a macro.

I have a column of values (J) and in column K I am multiplying some of the amounts by -1 in order to show a negative if that meets the criteria in column H. Formula =if(H2="rp",J2*(-1),J2).

Now I need to carry this down through column K. What I wrote was
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K65")
Range("K2:K65").Select
But some days there is more data and some days there is less. So 2-65 is a bit worthless
I'm sure there is a way to tell it to go as far as the last row, but I can't work out how to do it.

Any ideas?

xxlisaxx

xxlisaxx wrote: »

Hi there, I used to have an account on here a long time ago but can't for the life of me remember my username.
I am drawing a blank while writing a macro.

I have a column of values (J) and in column K I am multiplying some of the amounts by -1 in order to show a negative if that meets the criteria in column H. Formula =if(H2="rp",J2*(-1),J2).

Now I need to carry this down through column K. What I wrote was
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K65")
Range("K2:K65").Select
But some days there is more data and some days there is less. So 2-65 is a bit worthless
I'm sure there is a way to tell it to go as far as the last row, but I can't work out how to do it.

Any ideas?

Range("k2").Select
Range(Selection, Selection.End(xlDown)).Select
Set rgrange = Selection
Range("K2").Select
Selection.AutoFill Destination:=Range(rgrange)

xlDown will make a selection to the next blank value.

xxlisaxx

Thanks a million for your reply..


It's stopping with "Method "Range of object '_global' failed

The below line is highlighted in the VB editor

Selection.AutoFill Destination:=Range(rgrange)

I'm sorry, I'm suffering from baby brain and I think I am over-thinking this so can't work it out myself

Sub filldown()
Application.ActiveWorkbook.Sheets("Sheet1").Activate
Range("k2").Select
Range(Selection, Selection.End(xlDown)).filldown
End Sub

will work

xxlisaxx

Sorry to be a pain. I'm now getting an error "Compile Error: Expected End Sub"

The line before the Sub is highlighted. I know I'm doing something ridiculous

xxlisaxx

xxlisaxx wrote: »

Sorry to be a pain. I'm now getting an error "Compile Error: Expected End Sub"

The line before the Sub is highlighted. I know I'm doing something ridiculous

No idea. the above works fine for me.

just copy this bit into your existing macro, (clearing the existing code first)

Application.ActiveWorkbook.Sheets("Sheet1").Activate
Range("k2").Select
Range(Selection, Selection.End(xlDown)).filldown

and compile.

note - this code assumes a sheet called Sheet1 to be the sheet you're using.

xxlisaxx

I knew I was doing something ridiculous. my worksheet is called "Deals" it workd now.. Thanks a million

glasso

glasso wrote: »

No idea. the above works fine for me.

just copy this bit into your existing macro, (clearing the existing code first)

Application.ActiveWorkbook.Sheets("Sheet1").Activate
Range("k2").Select
Range(Selection, Selection.End(xlDown)).filldown

and compile.

note - this code assumes a sheet called Sheet1 to be the sheet you're using.

or go to macro new macro and paste the above into the macro 1 sub and end sub statements and compile.