vba help - formula fill down

[Mallesh23]

Hi Team,

I am using below code to fill down existing formula,
its very lenghty code can we shorten below code, by using any alternate Way

With ws_Master

    With .Range(.Cells(2, 4), .Cells(lr_new, 4))
        .FillDown
    End With

    With .Range(.Cells(2, 5.Cells(lr_new, 5))
        .FillDown
    End With

    
    With .Range(.Cells(2, 7), .Cells(lr_new, 7))
        .FillDown
    End With

    
    With .Range(.Cells(2, 9), .Cells(lr_new, 9))
        .FillDown
    End With


    With .Range(.Cells(2, 11), .Cells(lr_new, 11))
        .FillDown
    End With

    With .Range(.Cells(2, 14), .Cells(lr_new, 14))
        .FillDown
    End With

    With .Range(.Cells(2, 15), .Cells(lr_new, 15))
        .FillDown
    End With

    With .Range(.Cells(2, 18), .Cells(lr_new, 18))
        .FillDown
    End With


    With .Range(.Cells(2, 20), .Cells(lr_new, 20))
        .FillDown
    End With

end with

Thanks
mg

 

[My Aswer Is This]

You did not show all your code:
Like you did not define what
lr_new

is.

 

[Fluff]

How about

With ws_Master
   .Range("D2:E" & lr_new).FillDown
   .Range("G2:G" & lr_new).FillDown
   .Range("I2:I" & lr_new).FillDown
   .Range("K2:K" & lr_new).FillDown
   .Range("N2:O" & lr_new).FillDown
   .Range("R2:R" & lr_new).FillDown
   .Range("T2:T" & lr_new).FillDown
End With

 

[Mallesh23]

Hi Fluff,

Thanks for your help, this also help in reducing some code length.
is it possible to use loop here, as I have to work on 15 Columns.



Thanks
mg

 

[Fluff]

How about

Sub Mallesh()
   Dim Ary As Variant
   Dim i As Long, lr_new As Long
   
   Ary = Array("D2:E", "G2:G", "I2:I", "K2:K", "N2:O")
   With ws_master
      For i = 0 To UBound(Ary)
         .Range(Ary(i) & lr_new).FillDown
      Next i
   End With
   
End Sub

 

[Mallesh23]

Hi Fluff,

Superb ! Thank you so much for you help,
I have used match function to find header, it may change,

Nice code it worked in both situation ! Thanks

Arys = Array(2, 4, 6 etc)
   
   With ws_Master
      For i = 0 To UBound(Arys)
        .Range(.Cells(2, Arys(i)), .Cells(lr_new, Arys(i))).FillDown
      Next i
   End With

Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.