Edit Recorded Macro script to a Dynmaic Range

[Melimob]

Hi there

I'm trying to create a Dynamic Range within a recorded Macro by editing the VBA that's already there.

I've searched around but none of the tutorials seem to fit my needs.

Am confused as to whether I need an 'offset' or DIM or End(xlUp) function and how to use it.

I simply need to add a VLKUP starting in cell Z13 but autofilled down to whichever row the data ends in Column Y. At the moment it adds it down to row 512 as that's where the original data ended upon recording. When I add data next time though, there may be more or less rows so I need to make this dynamic!

Here's my basic script..
Sub Clean1()
'
' Clean1 Macro
'
'
Range("Z12").Select
ActiveCell.FormulaR1C1 = "Destination Region"
Range("Z13").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-23],LKUP!C[-24]:C[-23],2,0)"
Selection.AutoFill Destination:=Range("Z13:Z512")
Range("Z13:Z512").Select
Range("Y12").Select
End Sub

Many thanks in advance!
Melissa

 

[par60056]

I would clean it a little more. Avoid selecting cells in macros if you can.

Sub Clean1()
'
' Clean1 Macro
'
'
Range("Z12") = "Destination Region"
Range("Z13").FormulaR1C1 = "=VLOOKUP(C[-23],LKUP!C[-24]:C[-23],2,0)"
Range("Z13").AutoFill Destination:=Range("Z13:Z" & Range("Y13").End(xlDown).Row)
End Sub

I think this will do what you want.

 

[32CARDS]

I was going to suggest the same, to clean or what I do is "specify" after a recording, depends.
But I have added some "trick code" for arrow key moves.

Sub macro_xyz()

Sheets("Sheet3").Select

Sheets("Sheet3").Range("Z12").Select

Sheets("Sheet3").Range("Z12") = "Destination Region"

Selection.Offset(1, 0).Select '*Down

Sheets("Sheet3").Range("Z13").FormulaR1C1 = _
"=VLOOKUP(C[-23],LKUP!C[-24]:C[-23],2,0)"

Selection.AutoFill Destination:=Range("Z13:Z512")

Sheets("Sheet3").Range("Z13:Z512").Select

End Sub


'------------------------>
'ARROW KEY CODES

'Selection.Offset(0, 1).Select '*Right* 1 cell
'Selection.Offset(0, 2).Select '*Right, 2 cells.
'
'Selection.Offset(0, -1).Select '*Left*
'
'Selection.Offset(-1, 0).Select '*Up*
'
'Selection.Offset(1, 0).Select '*Down*

'Selection.Offset(2, 0).Resize(5, 8).Select '*Down*

 

[Melimob]

Hi Both, thank you so so much for your replies. I cannot tell you what a god send you both are!!

par60056 - that worked great however I tried to be clever and add more to the macro by recording the next step then copying and pasting the two but it didn't work. I now want to filter on column Z and delete any rows which equal NA or LAC. Red text is what I have entered and substitued your range with what was in there...can you tell me where I am going wrong please?:

Sub Clean1()
'
' Clean1 Macro
'
'
Range("Z12") = "Destination Region"
Range("Z13").FormulaR1C1 = "=VLOOKUP(C[-23],LKUP!C[-24]:C[-23],2,0)"
Range("Z13").AutoFill Destination:=Range("Z13:Z" & Range("Y13").End(xlDown).Row)

Range("Z13:Z" & Range("Y13").End(xlDown).Row).AutoFilter Field:=26, Criteria1:="=LAC", _
Operator:=xlOr, Criteria2:="=NA"
Rows("13:13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp

End Sub

32Cards - thank you too, I think I will definatley make use of your arrow keys they are special!!

Melissa

 

[Melimob]

OK...I think this has worked but concerned whether the absolute cell reference will affect the data in future?

I've just tested it with more and less rows and its worked but not sure how?

Sub Clean1()
'
' Clean1 Macro
'
'
Range("Z12") = "Destination Region"
Range("Z13").FormulaR1C1 = "=VLOOKUP(C[-23],LKUP!C[-24]:C[-23],2,0)"
Range("Z13").AutoFill Destination:=Range("Z13:Z" & Range("Y13").End(xlDown).Row)
Range("$A$12:$Z$512").AutoFilter Field:=26, Criteria1:="=LAC", _
Operator:=xlOr, Criteria2:="=NA"
Rows("13:13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp

Range("C12").Select
Selection.AutoFilter

End Sub

Many thanks

 

[par60056]

I have never tried to autofilter anything but the full sheet. To get the range for the autofilter I would do the same thing as the destination range on the autofill.

 

[32CARDS]

Melimob

When I try to test your code, it opens a file box, and then Excel tries to upgrade to another add-on, FrontPage in fact.

So I'm not sure what to suggest.
Is it suppose to open another file or get information from another file?

As with Absolute Formula,$, if you record a macro and on a test sheet and in cell B1, type in =$A$1+$A$2 then press Enter, Record Off, then look at your code, VBA does not seem to have a need to use the $ as a syntax.

Hope it gets solved.

Thanks.

 

[par60056]

32cards,
I see nothing in the code that should open the file box, or reference any add-on. It does reference another sheet to lookup data on.

 

[32CARDS]

par60056,
I should have updated,
correct, it's not in the code, seems to be another new problem on my side of the street. It's doing it with another program totally alien to excel.