Help copying a formula to a whole column... how to paste only to rows w/ data

[lacholeal]

Hello all,

I am trying to work out general formatting macros which will allow me to see data the way I need it regardless of the amount of rows containing data but never to more than 400 rows.


For example:

A B

1 aaaaa =IF(A1="",<FORMULA>,"")

2 bbbbb =IF(A2="",<FORMULA>,"")

A... =IF(A...="",<FORMULA>,"")

A400 zzzzz =IF(A400="",<FORMULA>,"")


How do I copy the formula to only those rows that contain data? In this case, to skip row ... while pasting the formula down the B column.


I have run into situations that the copy function fills the 65635 cells down the column, which then does not allow me to use the 'go To -> Special -> blanks; & delete those full rows. I've tried using Paste special as value, and still the same issue; especially if the column that I am trying to refer the formula to has been used on 'paste to column' activities before.


Thanks in advance for your insights.

 

[Excel_Kid_663]

You can 'order by' on column A and that will bring all of your values to the top and all of your blanks to the bottom. Then you can un-filter to return everything to its previous state. If you don't know how to do that, insert a new a new column in place of A and give it a sequential numbering system, i.e 1 2 3 4 5 6 etc then you can always re order on that. After, hope that makes sense