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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)

Code:
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

Code:
[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:

28ksdcn.jpg
 
Does this code select the correct column for you...
Code:
[table="width: 500"]
[tr]
	[td]Sub SelectLastColumn()
  Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Select
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
If it should be selecting column PB but isn't try putting this in an unused cell
=ISBLANK(PB1)
What does it say?
 
Upvote 0
@kbishop94, what are the results of the below (including what the message box says).

Code:
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:

25r2ty8.jpg



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


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.)
30ms685.jpg


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
icon14.png
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
 
Upvote 0
Also can you please report back the results of the below code please.

Code:
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
 
Upvote 0
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:
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 :confused: (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....:p )
 
Last edited:
Upvote 0

You definitely need to upload the workbook for me :biggrin: 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).
 
Upvote 0
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. ;)
 
Upvote 0
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:

22k9j.jpg


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

THanks
 
Upvote 0
Does this code select the correct column for you...
Code:
[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...
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top