Select last cell in a column with blanks

问题:

I am trying to find the last cell in a column that is populated with data in VBA. The problem is, in the range, there are blank cells. Is there any way to select the last cell with data if there are blanks? Any help would be greatly appreciated! I have pasted my range definitions below.

If Range("BL2") <> "" Or Range("BM2") <> "" Then
                            Set usr11 = Range("BL:BL")
                            Set usr12 = Range("BM:BM")

回答1:

Using Find

This will work on the ActiveSheet

Dim lngLastRow as Long

'If column is A
lngLastRow = Columns(1).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

回答2:

I'm not sure if this is what you're looking for

This is what I use to get the last row; just give it a column and a sheet.

Function getLastRow(sheet As String, Col As Variant) As Integer
    getLastRow = Sheets(sheet).Cells(Sheets(sheet).Rows.Count, Col).End(xlUp).row
End Function

Then you can use it like this:

Range("A" & getLastRow(ActiveSheet,"A")).Select 'Select the last row in column A

Similar function to get the last column with data:

Function getLastCol(sheet As String, row As Variant) As Integer
    getLastCol = Sheets(sheet).Cells(row, Sheets(sheet).Columns.Count).End(xlToLeft).Column
End Function

回答3:

This will work for you

Sub SelectBlank()
    Dim lastRow As Long: lastRow = Range("A1").End(xlDown).Row 'change to whatever column you have
    Dim i As Long
    For i = 1 To lastRow
        If Range("A" & i).Value = "" Then
            Range("A" & i).Select
            Exit For
        End If
    Next i
End Sub