CTRL + Shift + Down in a Macro

Printable View

12-27-2019, 12:22 PM
jejmiller

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
12-27-2019, 12:29 PM
paulked

Hi and welcome to the forum.

The syntax for autoFill is:

Code:

Sub Macro1() Range("U2") = "No" Range("U2").AutoFill Destination:=Range("U2:U106"), Type:=xlFillDefault End Sub
12-27-2019, 12:44 PM
jejmiller

1 Attachment(s)

Didn't work... But I'm probably doing it wrong. Attachment 25705
12-27-2019, 12:50 PM
paulked

It does work. What is going wrong? Where have you put the code? Have you tried stepping through your code with F8?
12-27-2019, 01:02 PM
jejmiller

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

Thanks for the help.
12-27-2019, 01:09 PM
p45cal

Code:

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
12-27-2019, 01:59 PM
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
12-27-2019, 03:36 PM
p45cal

it replaces all of it… unless you want the filled in cells selected when it's finished?!
12-30-2019, 08:46 AM
jejmiller

1 Attachment(s)

Quote:

Originally Posted by p45cal

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

Attachment 25719
12-30-2019, 09:21 AM
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?
12-30-2019, 12:08 PM
SamT

Hard to tell in that screenshot, but it looks like column "S" is complete with data

Code:

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

Code:

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"

Code:

Dim LastRow As Long LastRow = Cells(Rows.Count, "X").End(xlUp).Row Range("U2:U" & LastRow) = "No"
12-30-2019, 12:19 PM
jejmiller

Quote:

Originally Posted by 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?

Column A
12-30-2019, 12:24 PM
jejmiller

[QUOTE=SamT;397990]Hard to tell in that screenshot, but it looks like column "S" is complete with data

Code:

Range(Range("S2"), Range("S2").End(xlDown)).Offset(0, 2) = "No"

This plus P45Cal's code worked! Thank you all for your help!
01-03-2020, 07:06 AM
jejmiller

[QUOTE=SamT;397990]Hard to tell in that screenshot, but it looks like column "S" is complete with data

Code:

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?
01-03-2020, 08:04 AM
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.
01-03-2020, 08:55 AM
SamT

Now, let us imagine that some other column, even in another sheet or workbook, was complete with data. Say... Column "X"

Code:

Dim LastRow As Long LastRow = Cells(Rows.Count, "X").End(xlUp).Row Range("U2:U" & LastRow) = "No"
01-03-2020, 08:57 AM
SamT

Quote:

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
01-03-2020, 08:58 AM
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!
01-03-2020, 01:42 PM
SamT

Try it and see.
01-07-2020, 07:25 AM
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.

Code:

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
01-07-2020, 08:05 AM
p45cal

Quote:

Originally Posted by jejmiller

Column A

Code:

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
01-07-2020, 08:19 AM
jejmiller

Hey P45, I'm confused as to where to place your code.
01-07-2020, 08:31 AM
p45cal

It replaces all your full code in msg#20.
01-07-2020, 08:39 AM
jejmiller

Quote:

Originally Posted by p45cal

It replaces all your full code in msg#20.

Awesome, that worked.
01-07-2020, 08:43 AM
jejmiller

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

The code is:

Code:

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?

Code:

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
01-07-2020, 08:55 AM
p45cal

Quote:

Originally Posted by jejmiller

Would my new code be this?

Code:

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):

Code:

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
01-07-2020, 11:38 AM
jejmiller

Quote:

Originally Posted by p45cal

more likely (but untested):

Code:

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.
01-22-2020, 07:37 AM
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:

Code:

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

With this:

Code:

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.

Code:

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.
01-22-2020, 08:21 AM
jejmiller

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

I replaced:

Code:

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

With this:

Code:

LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1 Range("F2").AutoFill Destination:=Range("F2:F" & LastRow) Range("F1").AutoFilter
01-22-2020, 08:58 AM
p45cal

For all of Macro4 try:

Code:

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