crobinson661
New Member
- Joined
- Nov 15, 2019
- Messages
- 11
Good Morning,
I have a macro that I am using to AutoFIll a VLOOKUP formula to the bottom of a table.
It keeps filling past the bottom of the table and creating new, expanding the table and creating blank rows, except the formula column of course has #N/A.
The issue is I am then converting this to a pivot and it picks up the blanks as a data point. :-/
I'm not sure how to prevent this. I'm familiar with how to write an autofill to max data range when a table is NOT included, but I don't see the same code and I anticipate it's because I'm using a table.
Any help would be appreciated.
I have a macro that I am using to AutoFIll a VLOOKUP formula to the bottom of a table.
It keeps filling past the bottom of the table and creating new, expanding the table and creating blank rows, except the formula column of course has #N/A.
The issue is I am then converting this to a pivot and it picks up the blanks as a data point. :-/
I'm not sure how to prevent this. I'm familiar with how to write an autofill to max data range when a table is NOT included, but I don't see the same code and I anticipate it's because I'm using a table.
VBA Code:
Range("E1").Select
ActiveCell.FormulaR1C1 = "Formula"
Range("E2").Select
Application.WindowState = xlMaximized
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[Column D]],'[ReferenceFile.xlsx]Source Data - Per Item Pricing'!C1:C2,2,0)"
Any help would be appreciated.