Apply formula to all visible cells in column CK, occasionally there will be no rows-CodePudding

I have a filter applied to column CK, I am able to select the next visible row from the header by using the following, which also applies a formula into that active cell.

How do I fill that formula down to the bottom, without affecting the hidden rows?

Occasionally there will be no data, so it's just applying a formula to a blank row..

   range("CK1").Select
        ActiveSheet.range("$A$1").AutoFilter Field:=89, Criteria1:="0"
        
    ' Add if formula to find missing carriers based on patterns
    
            Do
            ActiveCell.Offset(1, 0).Select
        Loop While ActiveCell.EntireRow.Hidden = True

ActiveCell.Formula2R1C1 = _
        "=IFS(AND(LEN(RC[1])=18,LEFT(RC[1],2)=""1Z""), ""UPS"", AND(LEN(RC[1])=12,ISNUMBER(RC[1])),""FedEx"",AND(LEN(RC[1])=10,ISNUMBER(RC[1])),""DHL"",AND(LEN(RC[1])=11,LEFT(RC[1],2)=""06""),

CodePudding user response：

It would be great if you could refrain from selecting cells or activating sheets or workbooks like you do. The only time it is fine to have Excel change its selection on screen with VBA is if you want it to.

For your problem, a simple loop will do. Example with CK1 and all the cells below it:

Dim topCell As Range, bottomCell As Range
Set topCell = Range("CK1")
Set bottomCell = topCell.end(xlDown)

'Next test is optional, although recommended (is there no cell filled under CK1?)
If bottomCell.Row >= 1048576 Then 'Current maximal row; you may change the threshold if desired.
    Exit Sub
    'Alternatively: Exit Function
    'Other alternative example: Set bottomCell = Range("CK1000")
End If

Dim c As Range
For Each c In Range(topCell, bottomCell)
    If Not c.EntireRow.Hidden Then
        c.Formula2R1C1 = "" '<place your formula here>
    End If
Next c