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:

 

[kbishop94]

So my inexperience is showing once again.

It was my understanding, that excel will "see" hidden columns when you direct it to a range that has hidden columns in that range. If you do NOT want it to see whats in the hidden cells in that range, then you tell it specifically to IGNORE hidden cells by using the 'SPECIAL CELLS(visiblecellls)' (which i use alot in this workbook.

But when I used Ricks module, I realized that its ignoring the hidden cells and only sees the ones visible (unless I am missing something else here too... which is entirely possible(!) )

Is that what is happening?? (Im going to post a short 30 second screen grab to show you what I am talking about...)

 

[Michael M]

Can you post the workbook to dropbox, as requested previously....or is the info confidential ??

 

[kbishop94]

Can you post the workbook to dropbox, as requested previously....or is the info confidential ??

Getting right on that, sir. 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]

Here is your macro and what it returns 3 ways:
1. with the columns hidden,
2. with the columns unhidden,
3. and with that weird phantom column that appears after I remove an employee (which is removing 2 columns from the range and then deleting those empty columns)

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

Thank you, Rick.

 

[Michael M]

Youtube doesn't do much for us.....we need an upload to work with.

 

[kbishop94]

Youtube doesn't do much for us.....we need an upload to work with.

No problem, I understand. Did you get my PM? I have it in my dropbox now, Michael. Thanks.

 

[Michael M]

Ok....are you going to share the link to that dropbox file ??

 

[kbishop94]

Ok....are you going to share the link to that dropbox file ??

Here is the link to the spreadsheet in my dropbox (hope it works.)

https://www.dropbox.com/s/z7nrkfjlbemeipv/Employee Training Matrix5.xlsm?dl=0

How to replicate my issue:

1. Open the spreadsheet ("Employee Training Matrix" tab.)
2. Scroll over to last column. Take note that the last column is the name “Zamudio, Daniel”.
3. Select the bottom brown tab “remove employee form” (opens a form)
4. Select someone from the combobox box (it doesn’t matter who… just not zamudio, Daniel)
5. Click on the ‘MOVE EMPLOYEE TO INACTIVE SHEET’ command button
6. Now scroll over to the end of the spreadsheet again. (looks the same, correct?)
7. Click on the ‘reset all’ command button (in the top left hand corner on the worksheet). Check the last column 1 more time… now there is a new blank column beside that name. its empty (except for the borders) but excel sees this as the last column now (I don’t want it to, and don’t want that column there, for that matter.)

Now, close the workbook (but don’t save it!)

Now, repeat the same steps again… but this time, instead of hitting the ‘reset all’ button after you’ve removed an employee, just save & close the workbook. Now open it back up, and scroll over to the last column one more time… expected to see the ‘phantom column?’ Well now its not there at all. Everything was the same with the exception of hitting reset (and yes, you can hit reset now after you’ve saved and closed it and it still wont add the weird blank column at the end...)

Please and thank you (a million times) for anyone that can tell me what the heck is going on with this goofy code that is making this happen and tell me how I can fix it.

 

[Michael M]

I note that any of the macros that I checked give a lcol as 138, yet your reset code gives a lcol as 139.
I haven't had chance to look any further, but that might trigger something for you.
So, once an employee is removed to the Inactive, Lcol doesn't seem to be getting reset to the new lcol
I'd suggest rewriting the lcol and rcol line in the reset macro

From

    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count

To something like

l

Col = Cells(8, Columns.Count).End(xlToLeft).Column
rCol = cells(rows.count,"A").end(xlup).row

and see if that corrects the col number

 

[kbishop94]

Thank you! I will play around with it when I get home. Awesome that you found that discrepancy in the col number... def sounds like we're on the right track.