Dynamic Ranges

N

ngann

New Member
Joined
Jan 18, 2018
Messages
21
I am trying to use a ctrl + shift + down arrow dynamic range but am unsure how to embed it in my current macro that has something along the lines of

Selection.autofill Destination: =Range(“J2:J330”)

please help
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi & welcome to the board
Something like
Code: 
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("J2").AutoFill Range("J2:J" & UsdRws)
 
Upvote 0
Hello and thank you!

Forgive my ignorance, but how would I write that into this formula?

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,21,FALSE)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J5000")
Range("J2:J330").Select
Range("J2").Select
Selection.Copy
 
Upvote 0
What cell are you putting that formula in?
 
Upvote 0
How about
Code: 
Sub chk()
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",21,FALSE)"
End Sub
 
Upvote 0
Sorry for all the questions. I am confident in my excel skills until it comes to macros and then I am just lost. Maybe it would help if I gave you the whole code haha.

The parts I am wanting to change are the ranges it says autofill on. These ranges vary from time to time and I would like for them to auto adjust as opposed to me changing the row count. I know it is decently lengthy code but I think only the autofill ranges would need to change.

Thanks so much for your help



Code: 
Sub PricingMacro()
'
' PricingMacro Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Sheets("PRISM History").Select
    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Sheets("S & E").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,4,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C330")
    Range("C2:C330").Select
    Range("C2").Select
    Selection.Copy
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("PRISM History").Select
    Range("U1").Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 36
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "New Unit Price"
    Range("U1").Select
    Columns("U:U").ColumnWidth = 12.86
    ActiveWindow.ScrollColumn = 2
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "=RC15/RC12"
    Range("U2").Select
    Selection.AutoFill Destination:=Range("U2:U789")
    Range("U2:U789").Select
    Sheets("S & E").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,21,FALSE)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J5000")
    Range("J2:J330").Select
    Range("J2").Select
    Selection.Copy
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,12,FALSE)"
    Range("M3").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M330")
    Range("M2:M330").Select
    Range("M2").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("T2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,3,FALSE)"
    Range("T2").Select
    Selection.Copy
    Range("U2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,2,FALSE)"
    Range("U2").Select
    Selection.Copy
    Range("V2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,11,FALSE)"
    Range("V2").Select
    Selection.Copy
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,'Lead Time Report'!R[-1]:R[1048574],12,FALSE)"
    Range("T2:X2").Select
    Selection.AutoFill Destination:=Range("T2:X330")
    Range("T2:X330").Select
    Range("X2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,'Lead Time Report'!R1:R1048576,12,FALSE)"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X330")
    Range("X2:X330").Select
    Range("X6").Select
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G330")
    Range("G2:G330").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R1524C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E330")
    Range("E2:E330").Select
    Range("E2").Select
    Sheets("Summary").Select
    Range("B1").Select
    ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
    Range("B2").Select
    With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
        .PivotItems("HS").Visible = True
    End With
    Sheets("Summary").Select
End Sub
 
Last edited by a moderator:
Upvote 0
Firstly, when posting code please use code tags (the # icon in the reply window)
Secondly try
Code: 
Sub PricingMacro()
' Keyboard Shortcut: Ctrl+h
    
   Dim UsdRws As Long
   
   Sheets("PRISM History").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Columns("C:C").Select
   Selection.Cut
   Columns("A:A").Select
   Selection.Insert shift:=xlToRight
   Range("U1").Select
   Application.CutCopyMode = False
   With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .COLORINDEX = 36
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
   With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
   End With
   ActiveCell.FormulaR1C1 = "New Unit Price"
   Range("U1").Select
   Columns("U:U").ColumnWidth = 12.86
   Range("U2:U" & UsdRws).FormulaR1C1 = "=RC15/RC12"

   Sheets("S & E").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("C2:C" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",4,FALSE)"
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",21,FALSE)"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",12,FALSE)"
   Range("T2:T" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",3,FALSE)"
   Range("U2:U" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",2,FALSE)"
   Range("V2:V" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",11,FALSE)"
   Range("X2:X" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'Lead Time Report'!R1:R" & UsdRws & ",12,FALSE)"
   Range("G2:G" & UsdRws).FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
   Range("E2:E" & UsdRws).FormulaR1C1 = "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R" & UsdRws & "C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
   
   Sheets("Summary").Select
   Range("B1").Select
   ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
   Range("B2").Select
   With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
   .PivotItems("HS").Visible = True
   End With
   Sheets("Summary").Select
End Sub
 
Upvote 0
That works great except in the vlookup formulas it is limiting the table size too narrow and not pulling the entire table. Is there a fix to this also can you explain what the R1:R is doing?

Thanks so much for your help
 
Upvote 0
My mistake try
Code: 
Sub PricingMacro()
' Keyboard Shortcut: Ctrl+h
    
   Dim UsdRws As Long
   Dim UsdRws2 As Long
   
   Sheets("PRISM History").Select
   UsdRws2 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Columns("C:C").Select
   Selection.Cut
   Columns("A:A").Select
   Selection.Insert shift:=xlToRight
   Range("U1").Select
   Application.CutCopyMode = False
   With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .COLORINDEX = 36
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
   With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
   End With
   ActiveCell.FormulaR1C1 = "New Unit Price"
   Range("U1").Select
   Columns("U:U").ColumnWidth = 12.86
   Range("U2:U" & UsdRws).FormulaR1C1 = "=RC15/RC12"

   Sheets("S & E").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("C2:C" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",4,FALSE)"
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",21,FALSE)"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",12,FALSE)"
   Range("T2:T" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",3,FALSE)"
   Range("U2:U" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",2,FALSE)"
   Range("V2:V" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",11,FALSE)"
   Range("X2:X" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'Lead Time Report'!R1:R1048576,12,FALSE)"
   Range("G2:G" & UsdRws).FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
   Range("E2:E" & UsdRws).FormulaR1C1 = "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R" & UsdRws2 & "C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
   
   Sheets("Summary").Select
   Range("B1").Select
   ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
   Range("B2").Select
   With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
   .PivotItems("HS").Visible = True
   End With
   Sheets("Summary").Select
End Sub
R1:R" & UsdRws2 Is simply limiting the Vlookup to the rows of data rather than looking at the entire sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Improving range references to Table rows (dynamic instead of fixed)
Replies
5
Views
414
RoryA
RoryA
T
  • Question Question
Apply macro to all work sheets
Replies
3
Views
341
MARK858
MARK858
B
  • Question Question
Add Dynamic Filter Criteria to Query
Replies
1
Views
118
bkg73123
B
S
  • Question Question
Help with conditional formatting using a pivot table with dynamic ranges
Replies
1
Views
96
RoryA
RoryA
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
157
Ramses505
R

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top