Thread: Copy formulas downward within a range using VBA

I found a piece of code here: http://www.ozgrid.com/forum/showthre...py+formula+vba that i modified to use for my own workbook.

The code below works great. I tried adding a few notes to try to help me understand it.

Option Explicit

Private Sub fill_formula()
'Original found at: http://www.ozgrid.com/forum/showthread.php?t=148106&highlight=copy+formula+vba

    Dim lastrow As Long, firstrow As Long
    Dim rngFormulas As Range, rngVisible As Range
    
    firstrow = 3 'First row containing forumlas/data
    
    With Sheet2 'Sheet number to fill formulas on - (not the sheet tab name)
    
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row 'Counts last cell containing data in Column A
        Set rngFormulas = .Range("G3:M3") 'Set the range of cells containing the formulas
        
        
        ' Select first column then last column of ALL data including filters
        .Range("A" & firstrow & ":M" & lastrow).AutoFilter field:=1, Criteria1:="<>""" 'Filters 1st column anything containing data (<>"")
        
        
        With .AutoFilter.Range 'Below line first sets first column containing formulas, then counts rows containing them
            Set rngVisible = .Columns(7).Resize(.Rows.Count, 7).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
        End With
        rngFormulas.Copy 'Copy Formulas
        rngVisible.PasteSpecial (xlPasteFormulas) 'Paste Formulas
    End With

    Application.CutCopyMode = False
    
    Set rngFormulas = Nothing
    Set rngVisible = Nothing
    
    'Next 3 lines resets filtering
    Sheets("Oracle_TSV").Activate
    ActiveSheet.ShowAllData
    Selection.AutoFilter
    Range("A1").Select
End Sub

What i am trying to find out is, can the above code be modified to work within a range of rows instead of looking at an entire column? For example, i want it to only work on rows 25:50 (row 25 contains the formulas i want to copy downward) instead of the entire sheet. Then i could copy the formula, call it a new name, fix the row numbers, and run it again on rows 55:72, and continue on for about 8 different ranges.

Actually after sleeping on it, i believe its as simple as changing the line:

lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

to instead read "lastrow = 50" (or whatever number i need it to stop at)

You shouldn't need code to copy formulas down, which version of Excel are you using?

2007

I am trying to save file size by only copying the formula to rows that meet a specific criteria based on cell values. The code above works fine when looking at a full column but im still having trouble looking at a range insted. It is locating and filtering the correct rows but it errors our at the line: rngVisible.PasteSpecial (xlPasteFormulas) 'Paste Formulas

I think it has something to do with the offset but not 100% sure so I am still working on a solution to it.