.End(xlDown) selecting last nonblank cell incorrectly

Question

Writing VBA code to copy a dynamic range to a new worksheet. The code is supposed to first define a range which is the range to be copied. It does this by starting in the upper left hand corner of where the range will begin, then using Range.End(xlDown) to find the last entry. The offset then finds the bottom right hand corner of the range, and the range is set to span from the upper left hand corner to the lower right hand corner. That's how it is supposed to work, and how it does work for a verbatim sub, where the only changes are in the variable names for clarity.

Here's where it goes south. The Range.End(xlDown) is indicating that the last non-blank cell in the column is the very, very bottom cell on the worksheet (Like row 40,000 something). This is clearly not true, as the last non-blank cell is three rows down from the range I am looking at. Thus, rather than getting a 4x5 size range, I get one that spans nearly the entire height of the sheet. I have also tried clearing all the formatting of the column in case something was lingering, but to no avail. The code is below.

    Sub Copy_Starters_ToMaster()

    Dim MasterIO As Worksheet, IOws As Worksheet
    Set MasterIO = Worksheets("Master IO Worksheet")
    Set IOws = Worksheets("IO Worksheet")

    'Sets a range to cover all VFDs entered for an enclosure.
    Dim EnclosureStarters As Range, BottomLine As Range
    Set BottomLine = IOws.Range("$Z$6").End(xlDown).Offset(0, 3)
    Set EnclosureStarters = IOws.Range("$Z$6", BottomLine)

    'Finds first blank line in Master VFD table
    Dim myBlankLine As Range
    Set myBlankLine = MasterIO.Range("$AB$6")

    Do While myBlankLine <> vbNullString
       Set myBlankLine = myBlankLine.Offset(1, 0)
    Loop

    'Copies over enclosure list of VFDs, pastes into the master at the bottom of the list.
    EnclosureStarters.Copy
    myBlankLine.PasteSpecial
    Dim BottomInEnclosure As Range, currentStarterRange As Range, EnclosureNumber As Range

    'Indicates which enclosure each VFD copied in belongs to, formats appropriately.
    Set BottomInEnclosure = myBlankLine.End(xlDown)
    Set currentStarterRange = Range(myBlankLine, BottomInEnclosure).Offset(0, -1)
        For Each EnclosureNumber In currentStarterRange
            With EnclosureNumber
                .Value = Worksheets("Math Sheet").Range("$A$11").Value
                .BorderAround _
                ColorIndex:=1, Weight:=xlThin
                .HorizontalAlignment = xlCenter
            End With
        Next EnclosureNumber

    End Sub

Any advice on this would be much appreciated, it is endlessly frustrating. Please let me know if I need to post photos of the errors, or any further code, etc.

Answer 1

I think the answer here is to use xlUp and a generic lastrow formula such as:

Set BottomLine = IOws.Cells(Rows.Count, "Z").End(xlUp).Offset(0, 3)

That gives the last used cell in column Z, offset by 3

Answer 2

Cleaner again to use Find rather than rely on the xlUp type shortcuts, something like this (which also caters for the column being empty, which is a common error when setting ranges):

Dim rng1 As Range
Set rng1 = IOws.Range("Z:Z").Find("*", IOws.[z1], xlFormulas, , xlPrevious)
If Not rng1 Is Nothing Then Set Bottomline = rng1.Offset(0, 3)
`if rng1 is Nothing then the area searched is blank