Can you do a macro that drags a formula from multiple columns until the corresponding cell in column B is blank?
For example, if I add a row between 14/15, i have to drag all my formulas. What I was hoping to do is identify with the macros which columns have formulas and then when i run it, it drags it down until B is blank. I have formulas starting in row 5 in columns A, C,E,F,G,H,J. I would want it to drag until it hits the first blank cell in column B. is that possible?
.
let C2 Autofill the column C until the last populated cell in column B: .... you can edit the code to fit :
https://stackoverflow.com/questions/...n-empty-column
Please Login or Register to view this content.
yes you can, I don't have the code at hand but the quickest way is to record a macro and do just that select the cell in column C and copy it down the last filled row in column B
stop the recorder and check the code, you van then edit it to do that for columns A, C,E,F,G,H,J. I, you can also edit and union a range and do that.
You determine the last filled row in column B place this in a variable and use this to limit the copy down.
If I find the code, I have it in one of my code samples I'll post it later but you can start on this and also past your recorded code and I'll help edit it
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Not sure, but not working for me.
Please Login or Register to view this content.
The reason I don't want to do a record is because column B is forever expanding. My recorded macro would only go to the cell that i recorded too i believe.
I found it but you'll need to edit it
and then more editngPlease Login or Register to view this content.
That is why i said edit the macro !!!!!
.
Option Explicit
'paste this in Routine Module
Sub dragformula()
With Sheets("Sheet1")
.Range("C2").AutoFill .Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With
End Sub
See attached (NOTE you will need to edit sheet name to fit) :
.
This is not the prettiest method but it works :
Please Login or Register to view this content.
I modified Logit's code so that it cover all the columns you want
Please Login or Register to view this content.
Just thinking about your question.
If this is a table you have and you want to copy the formula down when a row is added, you could also convert the range to a table.
Then all formats, formulas and row banding etc is 'inherited. the moment you add a row or column to the table.
Checkout working with ListObjects (google for it The Spreadsheet Gurus) , I learned a lot there about Listbojects, makes (Excel) life a whole lot easier when working with table ranges
When running this, i'm getting "AutoFill method of Range class failed". Any idea?
Its flagg on the "C.AutoFill Destination" line.
Please Login or Register to view this content.
Figured it out, works perfect, thank you.
Just for fun, modified it a little on the columns with a yellow header permit values
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks