VBA to search range for forumlas and copy down to last row, copy down all formatting

Tybaltcap · Apr 3rd 2014

Hi Excel Gurus,

Running into a bit of trouble writing some VBA code to do 2 things:
1) search a range (the first row of my table) for all cells that have forumlas and copy them down the column to the last row of the table
2) reset all the conditional formatting for the table based on the conditional formatting in the first row of the table down to the last row of the table.

Some considerations
1) there are hidden columns (not sure if that matters)
2) Excel 2010

Background:

I have a table with data in rows B9:BE200. The first row of the table is protected to preserve the formatting and forumlas. In that first row (B9:BE9) some cells have forumlas, some have values, and some are blank. The rest of the rows in the table can be changed. Deleting a row is often necessary, as is adding a row but it creates #REF errors. I'd like to create a macro that in one click will go to the protected first row of the table and 'reset,' if you will, all the formulas down the columns (ignoring the cells that have values or are blank). After that is complete, then reset all the conditional formatting for the table based on the formatting of the first row.

I know there are some good VBA functions like '.hasformula' but am not sure how to use them in a loop that satisfies the above.

Thanks!
Tim

**Ger Plante** · Apr 3rd 2014

Re: VBA to search range for forumlas and copy down to last row, copy down all formatt

This should get you started anyway... this will fill down the formulas (only) for each row.

Code

Public Sub FillDown_And_Format()
Dim r As Range
Dim x As Range
Set r = Range("B9:BE200")
Set x = Intersect(r, r.Rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn)
x.FillDown 'fill down the formulas


r.Rows(1).Copy 'copy the header row
r.PasteSpecial xlPasteFormats 'paste the formatting only


End Sub

Display More

Note - you dont actually need to copy/paste the formatting from row 1, because when the fill down runs, it fills down the formulas AND the formatting. You WOULD need the copy/paste formatting, if you wanted to copy/paste the formatting of cells that did not contain a formula... hope that makes sense.

Ger

Tybaltcap · Apr 11th 2014

Re: VBA to search range for forumlas and copy down to last row, copy down all formatt

Thanks Ger,

Mind if I ask you a few questions to disect the code?

So when you define R - you are defining the whole table

Defining x is just the header row

Using Set X = intersect (r, r.rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn) is what is used to define that header row (row B9:BE9) right?

Then when you say x.filldown you are filling down all the forumlas
r.rows(1).Copy grabs the formatting from the first row
r.PasteSpecial xlPasteFormats then pasts down all the formatting for both the formula cells and the value cells correct?

That seems like it does the trick. I'll experiment and get back to you if I have any other questions.

Thanks!

**Ger Plante** · Apr 11th 2014

Re: VBA to search range for forumlas and copy down to last row, copy down all formatt

Thanks for coming back to me and asking questions. This is how we learn.

So I think you pretty much got it.

I basically used two ranges.
The first range r is the whole table... Everything. Defining r is easy to understand and I think you get that code. The range R is used for applying the formatting afterwards.
The second range x defines all the columns that contain formulas (and only formulas). The code for this is slightly tricikier, so let me explain that.

Using Set X = intersect (r, r.rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn)

r.rows(1) is the first row of r - so B9 to BE9
r.rows(1).SpecialCells(xlCellTypeFormulas) is ONLY the cells from B9 to BE9 that contains formulas so - for example, F9, K9 etc.
r.rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn is the same as above, but it now selects the entire column F:F, K:K

So this range is useless because it is the entire columns - from row 1 to row 1 million - and we are actually only interested in the rows in your table. Rows 9 to 200.

This is where the next piece of magic happens. I use the original table r which is b9:be200 and intersect it with the columns that will contain formulas. This is like saying what are the common ranges between b9:be200 and f:f and k:k. The common range is f9:f200 and k9:k200. --- I.e. The columns that will contain formulas

The rest of your assumptions are correct - basically fill down the forumlas, and copy paste the data across the whole table.

Hope this helps,
Ger

VBA to search range for forumlas and copy down to last row, copy down all formatting

Participate now!

Share