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. 25705

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

25719

[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

[p45cal]

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

[jejmiller]

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

[p45cal]

It replaces all your full code in msg#20.

[jejmiller]

It replaces all your full code in msg#20.

Awesome, that worked.

[jejmiller]

Now I have another sheet. I will try to do this on my own here.

The code is:


Range("F2").Select Selection.AutoFill Destination:=Range("F2:F999")
Range("F2:F999").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G999")
Range("G2:G999").Select


Would my new code be this?


LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
Range("G1").AutoFilter

[p45cal]

Would my new code be this?


LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
Range("G1").AutoFilter

more likely (but untested):
LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("G2:G" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
Range("G1").AutoFilter

[jejmiller]

more likely (but untested):
LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("G2:G" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
Range("G1").AutoFilter


This worked. Thank you.

[jejmiller]

So, I not sure why I can't ever figure this out... I have a new script I'm trying to auto-fill rows to match the data in column A. Can someone explain what needs to be replaced and why?

I've tried replacing this:


Selection.AutoFill Destination:=Range("F2:F33")

With this:


Range(Selection, Selection.End(xlDown)).Selec

But no luck.

Here is the full script. There's three places where auto-fill needs to be replaced.


Sub Macro4()'
' Macro4 Macro
'


'
Sheets("AAP Data").Select
Cells.Select
Selection.Copy
Sheets("Template").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A:A,B:B,C:C,E:E,G:G").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
Range("C1").Select
ActiveCell.FormulaR1C1 = "Qty Purchased"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Qty Returned"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Eligible"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hotkey"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Purchases!C[-2]:C[-1],2,FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Returns!C[-3]:C[-2],2,FALSE)"
Range("C2:D2").Select
Selection.AutoFill Destination:=Range("C2:D33")
Range("C2:D33").Select
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G7").Select
Cells.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("E2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]-RC[-3]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E33")
Range("E2:E33").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Hotkey!C[-5],1,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F33")
Range("F2:F33").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter
End Sub





Thank you everyone for your help.

[jejmiller]

Oh, look at me, I figured it out! For those curious,

I replaced:


Selection.AutoFill Destination:=Range("F2:F33")
Range("F2:F33").Select


With this:


LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("F1").AutoFilter

[p45cal]

For all of Macro4 try:
Sub Macro4() '
Set rngSource = Sheets("AAP Data").UsedRange
Set shtTemplate = Sheets("Template")
With shtTemplate
.Range("A1").Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
.Range("A:C,E:E,G:G").Delete
.Range("A1").AutoFilter
With .AutoFilter.Sort
.SortFields.Clear
.SortFields.Add2 Key:=shtTemplate.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Rows("2:2").Delete
.AutoFilter.Sort.SortFields.Clear
.Range("C1:F1").Value = Array("Qty Purchased", "Qty Returned", "Eligible", "Hotkey")
Set rngDataBody = .AutoFilter.Range
Set rngDataBody = Intersect(rngDataBody, rngDataBody.Offset(1))
With rngDataBody
.Columns("C").FormulaR1C1 = "=VLOOKUP(RC[-2],Purchases!C[-2]:C[-1],2,FALSE)"
.Columns("D").FormulaR1C1 = "=VLOOKUP(RC[-3],Returns!C[-3]:C[-2],2,FALSE)"
.Columns("C:D").Value = .Columns("C:D").Value
shtTemplate.UsedRange.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Columns("E").FormulaR1C1 = "=RC[-1]+RC[-2]-RC[-3]"
.Columns("F").FormulaR1C1 = "=VLOOKUP(RC[-5],Hotkey!C[-5],1,FALSE)"
End With
.Cells.EntireColumn.AutoFit
.Range("F1").AutoFilter
.Range("F1").AutoFilter
End With
End Sub