Finding the first blank cell in Column A after filtering and autofill

Question

I am trying to write a sub routine in a userform button to do the following

1. Collect data from an entered text box
2. Use the text box to filter Column 6 (Production Line)
3. When filtered, use a block of code to find within column A the first cell that is empty after filtering
4. When it finds the first empty cell that is visible, attempts to autofill the cell using the rows on top (due to formulas)

Here is my code at the moment.

Private Sub GenerateNewPartID_Click()
    'grabs all the information entered by user and creates a new row and autofillsName  Current Inventory    a new ID

    'this filters field 6 which is for production line so that we can create Part ID specific to that production line
    ActiveSheet.Range("A1:L1").AutoFilter Field:=6, Criteria1:=NewProductionLine.Value

    'Finds the first empty cell in column A so that it can generate a new part ID
    Worksheets("Home Page").Range("A1").Select
    With Worksheets(1).Range("A:A")
        Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    End With
    
    Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-1, 0)).Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(3, 0)), Type:=xlFillDefault
    ActiveCell.Offset(3, 0).Select
    
    'Populates the cells with the information entered by user
    NewPartID = ActiveCell.Value
    ActiveCell.Offset(0, 1).Value = NewPartNumber
    ActiveCell.Offset(0, 2).Value = NewPartName
    ActiveCell.Offset(0, 4).Value = NewCurrentInventory
    ActiveCell.Offset(0, 5).Value = NewProductionLine
    ActiveCell.Offset(0, 6).Value = NewLocation
    ActiveCell.Offset(0, 7).Value = NewSupplier
    ActiveCell.Offset(0, 8).Value = NewPrice
    ActiveCell.Offset(0, 9).Value = NewFloat
    ActiveCell.Offset(0, 10).Value = NewPlantManual
    ActiveCell.Offset(-1, 11).Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(1, 0)), Type:=xlFillDefault

End Sub

The issue is with the code above, it will work without my AutoFilter for Field 6, i.e. it will find within Range A:A the first empty cell, like A60 if A59 is the last populated cell. But as soon as I add in my AutoFilter for field 6, the find function goes to Cell M1 instead. This throws an error for my autofill function because it is trying to select a row above Row 1 which doesn't exist.

I am wondering if the find function is malfunctioning when there is a filter applied. Is there a way to get around this issue? I really don't want to use a Do loop because I am sort of worried that this loop can cause issues.

Thanks in advance for your help

Edit: Adding the Column Headers that I am filtering and showing what the autofill does. Basically this is a database spreadsheet and because I have formula to determine shortage, as well as Production Line Specific Part ID, I use using the AutoFill function to get the Next Part ID and the Shortage formula

https://i.stack.imgur.com/aeF0g.png

Answer 1

My approach is to use helper functions to allow me to treat the data like a table. I broke the problem down to simple simple methods that handle very few task.

• wsHomePage: Reference the Home Page Worksheet
• ProductionLineRange: Define a Range that includes the Data and Header
• ProductionLineHeaderRowRange: Returns the Header Row
• ProductionLineDataBodyRange: The Range that contains the Data
• ProductionLineNewRow: Returns the next available row
• ProductionLineIDColumn: Define the ID Column
• ProductionLineNextID: Returns the ID Column max + 1
• AppendProductionLine: Fills down any formulas and appends values to a new row
• TestRanges: Prints the Address for all my range

-- Note: I use Application.Goto Range while writing Range funtions

• TestAddNewRow: Adds 20 new rows

Refactor

Private Sub GenerateNewPartID_Click()
    'grabs all the information entered by user and creates a new row and autofillsName  Current Inventory    a new ID

    'this filters field 6 which is for production line so that we can create Part ID specific to that production line
    ProductionLineHeaderRowRange.AutoFilter Field:=6, Criteria1:=NewProductionLine.Value

    Rem Display the Home Page
    Application.Goto wsHomePage.Range("A1")

    AppendProductionLine ProductionLineNextID, NewPartNumber, NewPartName, , NewCurrentInventory, NewProductionLine, NewLocation, NewSupplier, NewPrice, NewFloat, NewPlantManual

End Sub

Function wsHomePage() As Worksheet
    Set wsHomePage = ThisWorkbook.Worksheets("Home Page")
End Function

Function ProductionLineRange() As Range
    Set ProductionLineRange = wsHomePage.Range("A1").CurrentRegion
End Function

Function ProductionLineHeaderRowRange() As Range
    Set ProductionLineHeaderRowRange = ProductionLineRange.Rows(1)
End Function

Function ProductionLineDataBodyRange() As Range
    If ProductionLineRange.Rows.Count = 1 Then Exit Function
    With ProductionLineRange
        Set ProductionLineDataBodyRange = .Offset(1).Resize(.Rows.Count - 1)
    End With
End Function

Function ProductionLineNewRow() As Range
    With ProductionLineRange
        Set ProductionLineNewRow = .Rows(.Rows.Count + 1)
    End With
End Function

Function ProductionLineIDColumn() As Range
    Set ProductionLineIDColumn = ProductionLineRange.Columns(1)
End Function

Function ProductionLineNextID() As Long
    ProductionLineNextID = WorksheetFunction.Max(ProductionLineIDColumn) + 1
End Function

Sub AppendProductionLine(ParamArray Args() As Variant)
    Dim NewRow As Range
    Set NewRow = ProductionLineNewRow
    
    Dim Destination As Range
    Rem Define a Range that Extends the DataBodyRange to incude the NewRow
    Set Destination = Union(ProductionLineDataBodyRange, ProductionLineNewRow)
    ProductionLineDataBodyRange.AutoFill Destination:=Destination, Type:=xlFillDefault
    
    NewRow.Cells(1, 1).Value = ProductionLineNextID
    Dim c As Long
    For c = 0 To UBound(Args)
        If Not IsMissing(Args(c)) Then NewRow.Cells(1, c + 2).Value = Args(c)
    Next
End Sub

Sub TestRanges()
    Debug.Print "Range", ProductionLineRange.Address(0, 0)
    Debug.Print "Header", ProductionLineHeaderRowRange.Address(0, 0)
    Debug.Print "DataBodyRange", ProductionLineDataBodyRange.Address(0, 0)
    Debug.Print "ProductionLineNewRow", ProductionLineNewRow.Address(0, 0)
    Debug.Print "ProductionLineIDColumn", ProductionLineIDColumn.Address(0, 0)
    Debug.Print "Next ID", ProductionLineNextID()
End Sub

Sub TestAddNewRow()
    Dim n As Long
    For n = 1 To 20
        AppendProductionLine "NewPartNumber", "NewPartName", Now, "NewCurrentInventory", "NewProductionLine", "NewLocation", "NewSupplier", "NewPrice", "NewFloat", "NewPlantManual"
    Next
End Sub