Copy cell only as far as the previous column cells go down in Macro

[Peter Davison]

Hi,
I have a cell F1 which I want to copy in a macro as far down the column as data goes in Column E.
The reason I can't leave a static formula in the cell is because it relates to a chart and if there is a formula in the cells lower than data in column E it will extend the width of the graph.
As I am new to VBA in Excel I'm not sure how to do this. Can you help?
Thanks

 

[Peter Davison]

It comes up with the message box saying 19 on action of this line of code
Range("G2:G" & lr).filldown

 

[Fluff]

In that case it should work. Are the cells empty after running the code?
Or is there a formula that returns ""?

 

[Peter Davison]

No the cells are empty.
The data that is in columns A:F is copied in as a value from another file as part of the macro and there is a graph also on the sheet that picks up column A:G using an offset range name so it knows how many rows to go to. There is also a pivot on the sheet for something else. None of the pivot or graph are anywhere near Columns A:G

 

[Fluff]

In that case can you post your file to a share site such as dropbox, onedrive, googledrive. Mark for sharing & then post the link here.

 

[Peter Davison]

It is a 50mb Excel Sheet with unfortunately sensitive data from a retailer I am working with, so I would be unable to do that.
How about if I just copied the sheet and that part of the macro which is not sensitive into a new file?
Would that be okay?

 

[Fluff]

That would be fine.

 

[Peter Davison]

It's really strange. When I copy the sheet into a new file, create the macro that you originally sent with the correct columns and use the macro it works okay.
I think because I have offset named ranges and also has an offset in Column G it is not happy with this.
I'm going to have another look tomorrow with a clearer head.
Thank you for all your help today. I am hoping I can crack this somehow tomorrow.

 

[vcoolio]

Hello Peter,

I'm not sure why its not working but I created a mock-up based on the data as shown in your post #5 and both the methods supplied by Fluff and myself work seamlessly.

Try this alternative method:-

Private Sub CommandButton1_Click()

Dim ws As Worksheet: Set ws = Sheets("rep writer variance report") '---->Make sure that the sheet name here _
is spelled exactly the same as the sheet tab name.

Dim lr As Long: lr = ws.Range("F" & Rows.Count).End(xlUp).Row

ws.Range("G2:G" & lr) = "=YOUR FORMULA HERE"  '---->Ensure that the "=" symbol is placed before your formula as shown.

End Sub

There's no need for the activate line of code as shown in your post #5.
Check to ensure that the ranges are correct.
Copy/paste your formula where you see "YOUR FORMULA HERE".

I hope that this helps.

Cheerio,
vcoolio.

 

[Peter Davison]

Hi,
It didn't work, but I still think there is a circular clash of some description.
I've decided to look at the source of the data coming in and used a formula in that solution so it comes into the sheet with the data already filled in.
Thanks for all your help you have been most helpful.
Regards
Peter

 

[vcoolio]

Ok Peter. Maybe give us an update sooner or later. It would be good to know how it works out.
Good luck!

Cheerio,
vcoolio.