Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

Autofill in Excel Macro

  • 16-08-2012 01:38PM
    #1
    xxlisaxx
    Registered Users, Registered Users 2 Posts: 4 ✭


    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?


Comments

  • #2
    [Deleted User]
    Posts: 19,205 ✭✭✭✭ [Deleted User]


    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.


  • #3
    xxlisaxx
    Registered Users, Registered Users 2 Posts: 4 ✭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


  • #4
    [Deleted User]
    Posts: 19,205 ✭✭✭✭ [Deleted User]


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

    will work


  • #5
    xxlisaxx
    Registered Users, Registered Users 2 Posts: 4 ✭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


  • #6
    [Deleted User]
    Posts: 19,205 ✭✭✭✭ [Deleted User]


    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.


  • Advertisement
  • #7
    xxlisaxx
    Registered Users, Registered Users 2 Posts: 4 ✭xxlisaxx


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


  • #8
    [Deleted User]
    Posts: 19,205 ✭✭✭✭ [Deleted User]


    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.


Advertisement