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, Dec 07 '16 at 17:17

score 1 · Accepted Answer · answered Dec 07 '16 at 17:29

1

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

answered Dec 07 '16 at 17:29

Darren Bartrup-Cook

18,362
1
23
45

Hi, I am not sure where to insert this into the existing code and if this is selecting all in the Column(Field) including blanks to the end of the column? – MrLockett Dec 07 '16 at 18:20
Add the code as a new function. Create a range variable in the `SelectDown` procedure - e.g. `Dim rLastCell as Range`. Replace the `End If` after your `MsgBox` with `Else` and the `Else` at the bottom of your code with an `End If`. Now, after the `Else` statement add this line of code: `Set rLastCell = LastCell(ActiveSheet, FieldName.Column)` and change `Range(FieldName.Offset(1), FieldName.End(xlDown)).Select` to `Range(FieldName.Offset(1), rLastCell.Row).Select`. I won't go into the whole avoid using `Select` thing. – Darren Bartrup-Cook Dec 08 '16 at 09:35
Hello, I have changed the code as such and now I am getting a Runtime error 1004: Method Range of Object Global Failed. for the line `Range(FieldName.Offset(1), rLastCell.Row).Select` I put the function first then a break then the Sub follows that. I made the changes with the else and the code and this is what happens. Not sure what I may be doing incorrect. – MrLockett Dec 08 '16 at 15:10
Sorry - rushed my response a bit as was due in a meeting. Try changing the line to `Cells(rLastCell.Row,FieldName.Column).Select` – Darren Bartrup-Cook Dec 08 '16 at 15:20
Ok, changed that, essentially the action now is that it goes to the last cell in the Column and makes the change to that cell. Is there a way to make that go all the way to the top minus the header, even if there are blanks and select that entire range of that column so that I can make the change? Thank you for all your help. – MrLockett Dec 08 '16 at 15:43
Ah, ok. Get you now. So to select the visible cells from row 2 to the bottom row you'd use `Range(Cells(2, FieldName.Column), Cells(rLastCell.Row, FieldName.Column)).SpecialCells(xlCellTypeVisible).Select`. I'd also strongly suggest looking into avoiding using `Select`, using `With...End With` and qualifying range references. – Darren Bartrup-Cook Dec 08 '16 at 15:49
Works Perfect, I am fairly new to this, if you have any links with insight on why not and also what to do in place of that, I am all for it, always trying to grow. Thank you so much again!! – MrLockett Dec 08 '16 at 16:01
Qualifying range references: http://stackoverflow.com/questions/25871416/excel-vba-fully-qualifying-range-cells-cells. Avoid using select: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/28700020 With...End With: https://msdn.microsoft.com/en-us/library/wc500chb.aspx – Darren Bartrup-Cook Dec 08 '16 at 16:18

xlDown Not Selecting Blank Cells

1 Answers1