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:

 

[Rick Rothstein]

Does this code select the correct column for you...

[table="width: 500"]
[tr]
	[td]Sub SelectLastColumn()
  Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Select
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

If it should be selecting column PB but isn't try putting this in an unused cell
=ISBLANK(PB1)
What does it say?

 

[kbishop94]

@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.

Yes! That is correct...there are hidden columns. They hide dates for the 'X';s that are in the corresponding non hidden column:

Here it is with the columns collapsed (hidden) (the 'RESET ALL' command button collasps the column beside the name column.

BUT... it should be noted that there is text in the hidden column. It lists the same name that is listed in its 'companion' column but with a '1' on the end. (normally the hidden column in row 1 has the font as white so that the user cannot see the name... I changed it to black just so you can see what is present in the hidden column.)

This is important to note (about the hidden column) because the code that I am asking about here in this thread says that its trying to find the first empty cell in row one starting at column 8 and all the way over to the last column with something in it.

Thank you, everyone for taking an interest in this and helping me out. I very much appreciate it. THis spreadsheet has been growing and improving for over 2 years now. And its all due to the excellent minds here on mrexecl.com

You all own a little piece of this workbook! lol

Here is a short screen grab of what the issue is and why I have to delete the last 'phantom' column that mysteriously appears when I delete a name from row one using a userform. It shows how the mysterious column appears, and why the worksheet won't function as intended until its dealt with.

https://www.youtube.com/watch?v=vJcRHhJF3jY&feature=youtu.be

 

[kbishop94]

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

I get B1

 

[kbishop94]

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

Hmmm... It wont let me do that for some reason. No matter where I have the cursor positioned, when I hit F8 it immediately takes it to this code:

Private Sub UserForm_Initialize()
Dim cNam As Range
Dim ws As Worksheet
Set ws = Worksheets("List")
For Each cNam In ws.Range("EmployeeNames")
    With Me.cboEN
        .AddItem cNam.Value
    End With
Next cNam
End Sub

no matter where I move the procedures around to it always highlights the UserForm_Initialize procedure (but I swear earlier today at work whenI tried this (im at my home now) it DID let me view that procedure using F8.) (and Im not even drunk yet, either.... )

 

[MARK858]

I get B1

You definitely need to upload the workbook for me not a youtube video, the workbook.

Unless Rick's code works for you (which I haven't seen a response to as yet or to Fluff's question).

 

[kbishop94]

Crap, I missed Fluffs post.

Going to try it now.

And I will GLADLY share my workbook with you. Thank you so much, and I wish I was smarter than I am so maybe I could somehow help you in return with some coding advice or problem solving... but... theres actually a zero chance of that so hopefully my gracious 'thank yous' will suffice.

 

[Rick Rothstein]

Unless Rick's code works for you (which I haven't seen a response to as yet or to Fluff's question).

Crap, I missed Fluffs post.

Going to try it now.

Does this exchange mean you tried my code earlier and it did not work for you?

 

[kbishop94]

If it should be selecting column PB but isn't try putting this in an unused cell
=ISBLANK(PB1)
What does it say?

If i direct it to the 'phantom' column (the one that i am trying here to get the code to delete it) it says this:

If i direct it to the cell beside it (to the left, the cell with a visible name in it) I get false.

THanks

 

[kbishop94]

Does this code select the correct column for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SelectLastColumn()
  Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Select
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

It did. (thank you) and I believe I see what is going on now...