Macro that extends cells to the last row

[Nett]

Hi there,

Does anyone know of any sample macro with the same logic. One that can automatically drag cells all the way down to the last row?

Example: I have created a vlookup on cell F2 and cell G2 into another worksheet such as =VLOOKUP(A2, File0705100.csv'!$A:$C,3,0) and =VLOOKUP(A2, File0705100.csv'!$A:$C,2,0) respectively.

Now i am trying to create a macro such that it extends cell F2 and G2 down to the last row so that the rest of the values will appear according. I tried recording but it takes a long time since the the number of rows can reach up to 6000.

Anyone? Thanks.

[teylyn]

why not just double click the autofill square to fill the formula down? Why the need for a macro?

[Nett]

Thanks Teylyn for the prompt reply.

The reason for creating a macro is to avoid using the fill handler to drag all the way down to the last row. Also after this step I will have to copy the entire F and G column and paste secial (Values) to remove the formulas, followed by doing a standard sort. All these are the manual work i face daily so im figuring out how to eliminate them step by step by using a macro.

Would appreciate if you can explain how autofill is able to help in this.

Thanks!

[DonkeyOte]

If we assume the auto fill range is determined by contents of A then, taking into account the requirements you mention in latter post, perhaps:

Please Login or Register  to view this content.

not clear as to the sorting requirements...
(change Sheet1 to the name of the sheet which contains these formulae)

[Nett]

I did a vlookup function on cell F2 and G2 imported from the CSV file. But how do I create an autofill of the 2 cells (containing the formulas) that goes to the last populated cells. Also the range has to be dynamic and not static(eg: F2:F100000) as what im doing now which does not allow my macro to run.

So this is what I have done so far and I need a little help here in editing and correcting my codes. Part of it is from the recorded macro that removes the formulas on columns F and G. Thanks.

Please Login or Register  to view this content.

[teylyn]

Nett,

have you even tried DonkeyOte's suggestion?

[Nett]

My apology and pls ignore my previous post.

Thanks DonkeyOte, i've applied your codes and it works! May i ask what the

Please Login or Register  to view this content.

and the

Please Login or Register  to view this content.

means which enables this macro to work?

[DonkeyOte]

The

Please Login or Register  to view this content.

is used to create the "formula" range.

The height of the formula range is first determined by the values as listed in Col A:

Please Login or Register  to view this content.

the above creates a range from A2:An where n is the last row in Col A containing data

With the Col A range established we offset from this range, to the right, by 5 columns (Col F).

We subsequently Resize that range (F2:Fn) to be two columns wide (F2:Gn)
(we then apply formulae to Columns 1 (F) & 2 (G) of that range)

In regard to:

Please Login or Register  to view this content.

R1C1 is the alternative notation style for range objects in Excel and utilises the grid nature of an Excel sheet eg: A10 is in R1C1 style R10C1 (Row 10 Column 1)

IMO applying formulae en masse is often easiest using R1C1 notation.
In terms of the notation style itself I put together a very brief (and very basic) overview on a prior post: http://www.excelforum.com/2171545-post2.html which you may (or may not) find useful.

[Nett]

Thanks DonkeyOte for the clear explanation. =)

I'm now trying to improve the macro a little by copying over the content in the CSV file to a new worksheet called 'Data' in the same workbook i am working on. Now i have added in a macro recording that applies the vlookup function on Column F and G.

Also, I added in your codes below and amended the file destination but it doesn't work. The rest of the logic still remains, only that now it does not do a vlookup to an external file. What seem to be the problem? Here are the codes below for your reference

Please Login or Register  to view this content.

[DonkeyOte]

I don't really understand the above but if as you say Data is a worksheet within the same workbook then you should be pointing to 'Data' and not 'Data.xls' in your VLOOKUP.

[Nett]

Sorry if I wasn't clear in in my explanation.

Initially, I did a vlookup function on cell F2 and G2 across an external workbook (CSV file format) where the autofill range is determined by the contents of A.

Eg in Column F

Please Login or Register  to view this content.

Eg in Column G

Please Login or Register  to view this content.

However, now I have copied the contents from File0705100.csv and paste it over in a new worksheet called 'Data' (XLS file format).

Eg in Column F

Please Login or Register  to view this content.

Eg in Column G

Please Login or Register  to view this content.

I have recoded a macro that runs the vlookup across the 'Data' worksheet. So when i tried combining the recorded macro to the code below, it only runs the vlookup on col F and col G and displays the value but does not extend the cells all the way to the last row and remove the formulas on both columns. Have I missed out something in the codes?

Please Login or Register  to view this content.

[Nett]

I hope my description this time round is very precise so as not to create more confusion that it already has.

I typed out the vlookup formula manually on cell F2 and cell G2 across another worksheet called 'Data' and ran the following macro:

Please Login or Register  to view this content.

The problem is that it does not return me any values but returns me #N/A from cell F2 downwards and cell G2 downwards.

So now instead of typing the formulas manually, I need to amend the codes such that the macro automatically does a vlookup on cell F2 across the 'Data' worksheet such as

Please Login or Register  to view this content.

. Also a vlookup on cell G2 across the 'Data' worksheet.

Please Login or Register  to view this content.

.

This macro will then be followed by the macro that copy and paste (values) on columns F and G to remove the formulas.

The sorting will be such that it recognize the header row then sort decending (Col F), followed by sorting ascending (Col C), followed by sorting header ascending (Col A).

Here are my codes and i need help for the further sorting of Column C and Column A.

Please Login or Register  to view this content.

Thanks.

[Nett]

I ommited the advanced sorting part using the vba code cause it works as well if i use the macro recording. I have a ques. Is it possible to combine both recordings and vba codes together in one Module that runs in one macro?

Let's say for example I want to run one macro that does all the following:

a) Do a vlookup acrross another worksheet that returns values to cell F2 and G2. (VBA code)
b) Autofill column F and G to last row of data (VBA code)
c) Copy and paste special(Values) on column F and G to remove the formulas (VBA code)
d) Advance sort col F (descending), then Col C (ascending), and then Col A (ascending) (Macro Recording)
e) Remove duplicated in entire worksheet (VBA code)
f) In column C, search for numbers and change it to words accordingly. Eg: 1000 represents Equity, 2000 represents Bonds, 3000 represents Swaps (VBA code)