Consulting

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

Thread: CTRL + Shift + Down in a Macro

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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 Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    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 Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    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,952
    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,952
    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
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,167
    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 expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11
    [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!

  12. #12
    [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?

  13. #13
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,167
    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 expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #14
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    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.

  15. #15
    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

  16. #16
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    Quote Originally Posted by jejmiller View Post
    Column A
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Ctrl+Up ffrom bottom of sheet.
    'LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1. An alternative to line above if A1 is not empty.
    Range("H2").AutoFill Destination:=Range("H2:H" & LastRow)
    Range("I2:I" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-6],Blacklist!C[-8],1,FALSE)"
    Range("I1").AutoFilter
    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.

  17. #17
    Hey P45, I'm confused as to where to place your code.

  18. #18
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    It replaces all your full code in msg#20.
    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.

  19. #19
    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.

  20. #20
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,167
    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 expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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