CODEWITHSUNDEEP
excelvba
Mahdi Jazini
Mahdi Jazini

Reputation: 791

How to auto fill data only (not format)

In Cell B6 there is number 1 and its color is red. but other cells are not red and there is no any data in them. after executing the following code, from B6 to B18, will be filled with numbers 1 ~ 13 but here there is a problem! i don't want to copy red color of B6 to B7 ... B8 ... B9 ... and ... i just need to copy without format of the base cell...

    Range("B6").FormulaR1C1 = "1"
    Range("B6").AutoFill Destination:=Range("B6:B18"),Type:=xlFillSeries

Can i find something like this property: xlFillSeriesWithoutFormat or a way to do this?

Upvotes: 0

Views: 2679

Answers (1)

FreeMan
FreeMan

Reputation: 5687

According to the MS Docs*, it seems that xlFillValues would be your best bet:

Copy only the values from the source range to the target range, repeating if necessary.

All the other options explicitly state that they will copy formatting. It looks like your only options are:

  • remove the formatting from the destination cells after copying
  • not have formatting to copy by:
    1. save off the formatting
    2. remove the formatting from the source cell
    3. do the .AutoFill
    4. then restore the formatting to the source cell

*found by a Google search for "excel vba .autofill"

Upvotes: 1

Related Questions