Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 30

Thread: CTRL + Shift + Down in a Macro

  1. #1

    CTRL + Shift + Down in a Macro

    I saw a similar post on this forum, but when I applied the techniques I was unsuccessful.

    I'd like excel to start on row U2 > Type "No" > apply to all rows that have information.

    I tried adding .End(xlDown).Select but encountered some errrors..



    Range("U2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "No"
    Range("U2").Select
    Selection.AutoFill Destination:=Range("U2:U105")
    Range("U2:U105").Select

  2. #2
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    626
    Location
    Hi and welcome to the forum.

    The syntax for autoFill is:

    Sub Macro1()
        Range("U2") = "No"
        Range("U2").AutoFill Destination:=Range("U2:U106"), Type:=xlFillDefault
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    Didn't work... But I'm probably doing it wrong. Capture.PNG

  4. #4
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    626
    Location
    It does work. What is going wrong? Where have you put the code? Have you tried stepping through your code with F8?
    Semper in excretia sumus; solum profundum variat.

  5. #5
    Can you see the image I uploaded on my last post? I highlighted where I put it

    Thanks for the help.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,912
    Range("U2").Value = "No"
    Set myRng = Range("U2", Range("U2").End(xlDown))
    'Set myRng = Range("U2", Range("U2").End(xlDown).Offset(-1)) 'if it's filling too many cells try this one instead of the line above.
    Range("U2").AutoFill Destination:=myRng
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Do I replace the current code with that? If so, where should I put that? Or do I add that to the bottom of the existing code?

    Here's the current code:

    Range("U2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "No"
    Range("U2").Select
    Selection.AutoFill Destination:=Range("U2:U105")
    Range("U2:U105").Select

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,912
    it replaces all of it… unless you want the filled in cells selected when it's finished?!
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Quote Originally Posted by p45cal View Post
    it replaces all of it… unless you want the filled in cells selected when it's finished?!
    Almost got it! See attached screen shot.... It's applying to all rows, where I want it to stop, is wherever the data stops on other lines..

    In this example, the data stops on line 64 - but tomorrow the data may stop on line 50, then the next day 24. Hope that makes sense, and thank you so much for your help!!

    Capture.jpg

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,912
    Is there one column you can reliably depend on having data all the way to the bottom, without intervening blank cells? If so, which column?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    Hard to tell in that screenshot, but it looks like column "S" is complete with data
    Range(Range("S2"), Range("S2").End(xlDown)).Offset(0, 2) = "No"
    But, let us say that Column "S" had some empty cells in it, but the last cell was not empty. Then
    Range(Range("S2"), Cells(Rows.Count, "S").End(xlUp)).Offset(0, 2) = "No"
    Now, let us imagine that some other column, even in another sheet or workbook, was complete with data. Say... Column "X"
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "X").End(xlUp).Row
    Range("U2:U" & LastRow) = "No"
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  12. #12
    Quote Originally Posted by p45cal View Post
    Is there one column you can reliably depend on having data all the way to the bottom, without intervening blank cells? If so, which column?
    Column A

  13. #13
    [QUOTE=SamT;397990]Hard to tell in that screenshot, but it looks like column "S" is complete with data
    Range(Range("S2"), Range("S2").End(xlDown)).Offset(0, 2) = "No"
    This plus P45Cal's code worked! Thank you all for your help!

  14. #14
    [QUOTE=SamT;397990]Hard to tell in that screenshot, but it looks like column "S" is complete with data
    Range(Range("S2"), Range("S2").End(xlDown)).Offset(0, 2) = "No"
    Why when I change "S2" to "A2" or "B2" does the script stop working?

    I can post my entire script here...But need to know what brackets to use for this forum?

  15. #15
    I'm trying to apply all rows still, but sometimes the S column is missing data, so I want to change to a column that always has data, like A or B column.

  16. #16
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    Now, let us imagine that some other column, even in another sheet or workbook, was complete with data. Say... Column "X"
     	Dim LastRow As Long
    LastRow = Cells(Rows.Count, "X").End(xlUp).Row
    Range("U2:U" & LastRow) = "No"
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  17. #17
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    I can post my entire script here...But need to know what brackets to use for this forum?
    Hash tag #

    Select code then click the hash tag
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  18. #18
    That worked, thank you.

    Now if I'd like to implement this same logic onto different spreadsheets with different types of data - would I use the code you just provided? Or would I need that plus additional code?

    Thanks!

  19. #19
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    Try it and see.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  20. #20
    I tried implementing the same but was not successful.

    Here is the full code. I need to auto-fill H and I columns. All other columns are fully populated.

        Range("H2").Select
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("H2:H139274")
        Range("H2:H139274").Select
        Range("I2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],Blacklist!C[-8],1,FALSE)"
        Range("I2").Select
        Selection.AutoFill Destination:=Range("I2:I139274")
        Range("I2:I139274").Select
        Range("I1").Select
        Selection.AutoFilter
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •