How to find next blank cell in column after autofilter-CodePudding

I am trying to find the next blank cell in column N in order to select and copy the vale in the same row but in column I after autofiltering column B.

My code seems to find the next blank cell but doesn't take the filter into consideration finding the blank cell in rows not included in the filter.

Hope this makes sense

Edit: Apologies about the shite code, I typically recycle and string it all together etc.

Sub Replanning_Lasers()

startofloop:
Dim Orderbook As String
Dim Supply As String

Orderbook = Sheet25.Range("C14")
Supply = Sheet25.Range("D14")

If Orderbook > Supply Then GoTo endofloop

***Sheets("List").Select
Range("$B$4:$BN$1533").AutoFilter Field:=3, Criteria1:="LASER"

NextFree = Range("N5:" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).row
Range("I" & NextFree).Select***

Selection.Copy
    Sheets("Planning").Select
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Call BUT_Planning_reset
Call BUT_Planning_Find_First

Range("M9").Select
    Selection.Copy
    Sheets("List").Select
Range("N" & NextFree).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
GoTo startofloop
endofloop:
End Sub

Thanks

CodePudding user response：

Maybe try something like this:

Sub Replanning_Lasers()

    Dim wsList As Worksheet, wsPlan As Worksheet, rngData As Range, rngBlanks As Range
    
    Set wsList = ThisWorkbook.Sheets("List")     'Use variables for worksheets 
    Set wsPlan = ThisWorkbook.Sheets("Planning") '   to avoid repetition
    Set rngData = wsList.Range("B4:BN1533")
    
    rngData.AutoFilter Field:=3, Criteria1:="LASER"
    
    On Error Resume Next 'ignore error if no visible blank cells
    'find any visible blank cells in ColN of the filtered data
    Set rngBlanks = rngData.EntireRow.Columns("N"). _
                         SpecialCells(xlCellTypeVisible). _
                         SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0      'stop ignoring errors
    
    If rngBlanks Is Nothing Then Exit Sub 'no visible blanks
    
    For Each c In rngBlanks.Cells   'process each blank cell in turn
        If Sheet25.Range("C14") > Sheet25.Range("D14") Then
            Msgbox "supply breach" 
            Exit For 'Orderbook > Supply ?
        End If
        
        wsPlan.Range("C9").Value = c.EntireRow.Columns("I").Value 'no need for copy/pastespecial
        
        BUT_Planning_reset       'use of Call is deprecated...
        BUT_Planning_Find_First
        
        c.EntireRow.Columns("N").Value = wsPlan.Range("M9").Value
    Next c
    
End Sub