Hi Folks,

I have a formula that counts how many rows there are in cell A, then fills the rest of the columns down to match. It works using the "selectionchange" part of the worksheet, and executes perfectly 99% of the time.

Unfortunately, there is a program that fills the data in column A and sometimes the rows in A decrease, but the macro still fills down to the old number of rows, causing excel to divide by zero a few dozen times and crash.

So is there a way that I can have it fill down ONLY if the row is not empty? The code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rngFormula As Range
    Dim lRowData As Long, lRowFormula As Long
     
    With ThisWorkbook.Worksheets("Monthly Raw")
        lRowData = .Range("A" & .Rows.Count).End(xlUp).Row                  'get the last data row from column A
        lRowFormula = .Range("F" & .Rows.Count).End(xlUp).Row               'get the last            formula row from column F
        Set rngFormula = .Range("F" & lRowFormula & " :Q" & lRowFormula)
        rngFormula.AutoFill Destination:=.Range("F" & lRowFormula & ":Q" & lRowData)
    End With

End Sub
I think I need something just before the 

 rngFormula.AutoFill Destination:=.Range("F" & lRowFormula & ":Q" & lRowData)
line but I'm not sure what.

Can I nestle an IF function in there? Or should I end the with... one line early, run the IF, and then finish off the IF function?

Many thanks for your help,

AR