CTRL + Shift + Down in a Macro

**p45cal** · 01-07-2020, 08:05 AM

Originally Posted by jejmiller

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

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

**p45cal** · 01-07-2020, 08:31 AM

It replaces all your full code in msg#20.

**jejmiller** · 01-07-2020, 08:39 AM

Originally Posted by p45cal

It replaces all your full code in msg#20.

Awesome, that worked.

**jejmiller** · 01-07-2020, 08:43 AM

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

Originally Posted by jejmiller

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

Originally Posted by p45cal

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

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

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** · 01-22-2020, 08:58 AM

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

Thread: CTRL + Shift + Down in a Macro

Thread Tools

Display

Posting Permissions