VBA code for finding last empty cell (column) (but it ignores borders... i need it to see borders as possibly an empty cell)

[kbishop94]

my code says to find the last empty cell (in a column) but i would like it to select that cell if its empty, but still has a border around it (or possibly it wont... point is, it shouldnt matter if it has a border or not, just that if it doesn;t contain a string, then it should select that cell & column)

Private Sub cmdLastRow_Click()

Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
Dim rCell As Range

For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))

[COLOR=#0000ff][B]If rCell = "" Then
[/B][/COLOR]
Columns.EntireColumn(lCol + 1).EntireColumn.Select
Else

End If
Next rCell

End Sub

I tried

[B][COLOR=#0000ff]If rCell Is Null Then[/COLOR][/B]

but that gave me an error.

So how can i make it select the last empty (column) cell in row 1 that has a border around it, but doesn't contain any text?

Also, the cells in row 1 will always be strings, no numbers.

btw, this is what I see when i run the code and it selects the column:

 

[MARK858]

Maybe...

Private Sub cmdLastRow_Click()

    Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim rCell As Range

    For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))

        If rCell = "" Then

            Columns.EntireColumn(lCol + 1).EntireColumn.Select
        Else

        End If
    Next rCell

End Sub

 

[Michael M]

Is the title block at the top of the page with the names in it a Merged cell ??
If so, try referencing the lcol as row 8 or a row below the title block.
ry using

Dim lCol As Integer
lCol = Cells(8, Columns.Count).End(xlToLeft).Column
Dim rCell As Range
For Each rCell In Range(Cells(8, 8), Cells(8, lCol + 1))
If rCell = "" Then
Columns.EntireColumn(lCol + 1).EntireColumn.Select
Else

 

[kbishop94]

Is the title block at the top of the page with the names in it a Merged cell ??
If so, try referencing the lcol as row 8 or a row below the title block.
ry using

Dim lCol As Integer
lCol = Cells(8, Columns.Count).End(xlToLeft).Column
Dim rCell As Range
For Each rCell In Range(Cells(8, 8), Cells(8, lCol + 1))
If rCell = "" Then
Columns.EntireColumn(lCol + 1).EntireColumn.Select
Else

No, sir. No merged cells in row 1 (or anywhere in this sheet actually) Thank you.

 

[kbishop94]

Maybe...

Private Sub cmdLastRow_Click()

    Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim rCell As Range

    For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))

        If rCell = "" Then

            Columns.EntireColumn(lCol + 1).EntireColumn.Select
        Else

        End If
    Next rCell

End Sub

hmmm.... it doesnt find anything to select using this code.... WHy would that be???

 

[Michael M]

This currently finds the last col on row 1, change the 1 to 8 and see if that deos it

lCol = Cells([color=red]8[/color], Columns.Count).End(xlToLeft).Column

 

[kbishop94]

This currently finds the last col on row 1, change the 1 to 8 and see if that deos it

lCol = Cells([COLOR=red]8[/COLOR], Columns.Count).End(xlToLeft).Column

Thank you, Michael.

My complete code now looks like this:

Private Sub cmdLastRow_Click()

Dim lCol As Long
    lCol = Cells(8, Columns.Count).End(xlToLeft).Column
Dim rCell As Range
For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))
If rCell = "" Then
Columns.EntireColumn(lCol + 1).EntireColumn.Select
Else
End If
Next rCell
End Sub

but it still isnt selecting any cells/columns...

 

[Michael M]

try stepping throught the code 1 line at a time...using F8
hover the mouse over lcol when that line of code is processed, looking at your picture lcol should be 15
The code works fine for me (on my sample)
Next option would be to upload the sheet to dropbox or similar then post a luink to it back here so we can take a look

 

[MARK858]

@kbishop94, what are the results of the below (including what the message box says).

Sub zzz()
Dim LastCol As Range
Set LastCol = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
MsgBox LastCol.Address(0, 0)
LastCol.EntireColumn.Select
End Sub

Michael I think our codes are finding hidden columns.

 

[MARK858]

Also can you please report back the results of the below code please.

Sub vizCells()
    Dim myCell As Range
    For Each myCell In Rows(1).SpecialCells(12)
        If myCell.Value = "" Then
            MsgBox myCell.Address(0, 0)
            myCell.EntireColumn.Select
            Exit Sub
        End If
    Next
End Sub