Last Row in Columns that have empty cells at the bottom.

[Mackeral]

I have ended up after a file read with cells with nothing in them at the bottom of a column of data which are included in the Last Row Calculation.
And if there is a better way to do this, let me know.
Mac


This is my solution:

Function Last__Row(Sheet_Spec, _
                    Optional Col_Rng = "All") As Long
    ' Returns the row of last cell used of the Worksheet or a column.
    ' 5/2/20 Add code to check for empty cells at bottom of column last row. WML

    ' Note: "Col_Spec" can contain the word "All", a column designator, or a column range. (See "Col_Nr" Function below)
    
    Dim SHEET As Worksheet
    
    Prog = "Last__Row"
    
    Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)
        
    If Col_Rng = "All" Or Col_Rng < -1 Then
        With SHEET.UsedRange
            Last__Row = SHEET.Rows(.Rows.Count).Row
        End With
        
    Else
        Call Text_Before_After(Col_Rng, ":", Col1, Col2)
        If Col2 = "" Then Col2 = Col1
        If Col2 < Col1 Then
            TS = Col2
            Col1 = Col2
            Col2 = TS
        End If
        
        With SHEET
            For Col = Col_Nr(Col1) To Col_Nr(Col2)
                Col_Id = Col_Ptr(Col)
                Temp = .Cells(.Rows.Count, Col_Id).End(xlUp).Row
                
                ' Actually Test bottom of Column.
                Do While Trim(Cells(Temp, Col)) = ""
                    Temp = Temp - 1
                Loop
                
                Last__Row = Maximum(Last__Row, Temp)
            Next Col
        End With
        
    End If
            
End Function ' Last__Row

Function Col_Nr(Col_Designation)
    ' Returns a Column Number from either a Number or Letters.
    ' 4/20/20 Created. WML
    
    If IsNumeric(Col_Designation) Then
        Col_Nr = Col_Designation
    Else
        Add = 0
        Col_Nr = 0
        Hold = Col_Designation
        Do
            Char = Left(Hold, 1)
            Nr = InStr(LETTERS, Char)
            Col_Nr = Col_Nr + Nr + Add
            
            Add = Add + 25
            Hold = Mid(Hold, 2, 99)
        Loop Until Hold = ""
        
    End If
    
End Function ' Col_Nr

 

[Joe4]

For rows with data, is column A always populated? If so, try this and see if it yields better results:

Last__Row = SHEET.Cells(Rows.Count,"A").End(xlUp).Row

If column A might be blank for some rows with data, but another column is always populated, just use that column letter instead of "A" in the formula above.

Note: One odd thing I notice in your code. I see you declaring the "SHEET" variable, but I do see you setting it equal to anything. Where is that done?

 

[Rick Rothstein]

Note: One odd thing I notice in your code. I see you declaring the "SHEET" variable, but I do see you setting it equal to anything. Where is that done?

It looks like he may be getting it from a call to a subroutine he did not share with us. I think that variable gets loaded up from this statement...

Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)

Another thing I noticed is that the OP's Col_Nr function cannot work correctly because he never gave a value to his LETTERS variable (which should have been the string of letters A through Z). With that said, the OP used a lot of code to do what this one-liner does...

Function Col_Nr(Col_Designation)
  Col_Nr = Cells(1, Col_Designation).Column
End Function

Of course, given the simplicity of the one-liner, I am not sure I would have created a special function to get the column number when the Cells function can return it so easily.