xlDown Not Selecting Blank Cells

Question

I am having an issue where a particular Column has blanks in the middle of it for certain rows. This is leading the code:

Range(FieldName.Offset(1), FieldName.End(xlDown)).Select

To not select all of the cells since it is stopping at the blank cells and filling in XYZ for the cells right before the blanks.

I know that xlup will remedy this issue, however, if the last cell of the field is blank then it will not change that cell and go to the next populated cell. I am not sure on how to modify my code so that it utilizes xlup and avoids if the bottom cells are blank in the column. I do have a column named "ABC" that will always have all of its rows populated that I can maybe ping off of in order to call it out as the last row of the filtered data, but I am not sure how to do this.

My Code

Sub SelectDown()

Dim FieldName As Range
Dim rng As Range, res As Variant, lrow As Long

Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
res = Application.Match("Errors", rng, 0)

'Finds the Specific Error'
rng.AutoFilter Field:=res, Criteria1:="*-SHOULD BE XYZ*"

'Only Shows rows that have something that matches the filter criteria
lrow = ActiveSheet.Cells(Rows.Count, res).End(xlUp).Row + 1

If ActiveSheet.Range(Cells(1, res), Cells(lrow, res)).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then

    Set FieldName = Range("A1:BZ1").Find("COLUMN NAME")

    'If field isnt present shows message
    If FieldName Is Nothing Then
        MsgBox "Field Name was not found."
    End If

    'Changes the Selection to XYZ if there is a change present
    Range(FieldName.Offset(1), FieldName.End(xlDown)).Select
    Selection.FormulaR1C1 = "XYZ"
    'Changes the Color of the fields changed to Yellow
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
Else
End If

End Sub

you need to put all the lines, starting with Range(FieldName.Offset(1), FieldName.End(xlDown)).Select at the Else section of If FieldName Is Nothing Then , to make sure Find method for finding FieldName was successful. — Shai Rado

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-12-07T17:29:18

You could use this code.
Use Set FieldName = Range("A1:BZ1").Find("COLUMN NAME") to find the column number (providing it's NOT NOTHING) and supply that as the Optional Col number.

Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function

xlDown Not Selecting Blank Cells

1 Answers