[diggeryo]

I use Excel 2007. I hope how I describe this makes sense. I am by no mean a VB authority. Much of the lines of code I created for my Excel VB macro was done though trial and error and a lot of googling. With that being said, I just can't seem to get one thing right.

I'm trying to create a section in my macro that will copy the contents of cell AE2 (which will contain a formula) into the remaining cells of column AE (AE3 through whatever) that have data in another column ©. However, the last cell in column AE changes depending upon the file I use, so the last rown is not always the same number.

I was able to edit the macro so that it copies to the entire column (all 1,048,576 cells). But this is way too much. I don't want it to copy the formula to every cell in a column, but rather every cell in a column AE that has data column C.

Any suggestions would be appreciated.

[Advertisements]

***UPDATE***

I figured out how to use the VB macro to calculate the last row in the worksheet.

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count

However, I'm not sure how to now use this variable to select the cells in the range. I tried the following, but none seem to work:

Range("AE3:AE[LR]").Select
Range("AE3:Indirect(R[LR]C)").Select
Range("AE3:Indirect(R[LR]C, FALSE)").Select

[diggeryo]

***UPDATE***

I figured out how to use the VB macro to calculate the last row in the worksheet.

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count

However, I'm not sure how to now use this variable to select the cells in the range. I tried the following, but none seem to work:

Range("AE3:AE[LR]").Select
Range("AE3:Indirect(R[LR]C)").Select
Range("AE3:Indirect(R[LR]C, FALSE)").Select

[diggeryo]

After three four days of trial and error and going to every google page imaginable, I think I finally figured it out.


Range(Cells(2, 31), Cells(LR, 31)).Select