CTRL + Shift + Down in a Macro

[jejmiller]

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

[paulked]

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

[jejmiller]

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

[paulked]

It does work. What is going wrong? Where have you put the code? Have you tried stepping through your code with F8?

[jejmiller]

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

Thanks for the help.

[p45cal]

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

[jejmiller]

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

[p45cal]

it replaces all of it… unless you want the filled in cells selected when it's finished?!

[jejmiller]

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

[p45cal]

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?

[SamT]

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"

[jejmiller]

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

[jejmiller]

[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!

[jejmiller]

[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?

[jejmiller]

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.

[SamT]

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"

[SamT]

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

[jejmiller]

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!

[SamT]

Try it and see.

[jejmiller]

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