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
476
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
 
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...)
 
Last edited:
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Can you post the workbook to dropbox, as requested previously....or is the info confidential ??
 
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]

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.
icon14.png
 
Upvote 0
Youtube doesn't do much for us.....we need an upload to work with.
 
Upvote 0
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.
 
Upvote 0
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
Code:
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count

To something like

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

and see if that corrects the col number
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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