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:

 

[Michael M]

I changed this line in the reset code

Range(Cells(1, 1), Cells(rCol, lcol)).Borders.Weight = xlThin

To

Range(Cells(1, 1), Cells(rCol, [color=red]137[/color])).Borders.Weight = xlThin

And the extra column was clear.

I still believe the prob is the lcol in the reset macro

 

[Fluff]

Try

    lCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column

 

[kbishop94]

Try

    lCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column

Ok, here is the update you all have been waiting for (yea, right )

Here is the file that I should of uploaded yesterday instead of that overly massive one with all kinds of code and different (unecessary) worksheets (this one is simple and is just the basics of what is needed to test the code.)

UPDATED WORKBOOK:
https://www.dropbox.com/s/g06zbob4a68kgjh/NEW Employee Training Matrix w NEWEST FIXES.xlsm?dl=0

So... the code above is what I started with when i created a fresh new workbook, and copied over only about 50 or so names from the main workbook, and only the other necessary columns/rows and pasted everything from teh original workbook to my new one as text or as values (but still changed dates to dates.) I left all the conditional formatting BS and all the 'bells and whistles' OUT so I (we) are only looking at the responsible code that has been buggering up my life with its crazy weird column I cannot seem to get rid of.

So here is what it did: First, the column still appears (using my orginal code for " lCol " in the new workbook and under the same condition (which was acutally really good because it confirmed for me that I wasnt dealing with a bug, glitch or some other deep hidden code i forgot about somewhere in the workbook that was the culprit.)

I have all 3 " lCol's " in the code like this:

Dim lCol As Long


[COLOR=#008000][B]    'Fluffs code:[/B][/COLOR]
[COLOR=#008000][B]        ' lCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
[/B][/COLOR]
[COLOR=#008000][B]    'Michaels code:[/B][/COLOR]
        lCol = Cells(8, Columns.Count).End(xlToLeft).Column

[COLOR=#008000][B]    'My original Code:[/B][/COLOR]
[COLOR=#008000][B]        ' lCol = ActiveSheet.UsedRange.Columns.Count
[/B][/COLOR]
    rCol = ActiveSheet.UsedRange.Rows.Count


For Each Cell In Range(Cells(4, 8), Cells(4, lCol))


If Cell = "a" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = False
Next

So when you remove the ' from one (and replace the other one with the ' ) and run it each time, the following happens.

First, after opening the spreadsheet, unhide the columns so you see BOTH the Name of the person, and the 'empty' cell in the column to the immediate right. (its not empty tho... it has the same name as its column buddy but with a "1" on the end and in white font). Select a name (the entire column) from the list, and also select its column-cousin to the right, and delete both columns.

Now click the "reset" command button.

Using my original code, you get the extra phantom column on the end. (I hate that column.)

Change the code to Michaels... btw, nice try there, Michael. I thought you nailed it. At first. Then I clicked the reset button again a second time, and the phantom column appeared after the second click.
And thats what your code does, Michael. It just 'toggles' back and forth between having the extra column there and not having it.

Lastly (small insignificant drum roll).... ACtivcate Fluff's code, and (cue the church bells) it works!!! you can remove a name (and the column next to it) and then click reset and all is perfect in (my) world.

I wish I could buy y'all a beer (or 30.)

Cheers!!

Keith

 

[Michael M]

Fluff's code was recommended way back in Post #11 by Rick Rothstein
So, why have the "helper" column for each staff member. Is that a start date ??
If so, why not have a Staff page with that stuff in it, years of service, leave taken, etc ??

 

[kbishop94]

Fluff's code was recommended way back in Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=11]#11 [/URL] by Rick Rothstein
So, why have the "helper" column for each staff member. Is that a start date ??
If so, why not have a Staff page with that stuff in it, years of service, leave taken, etc ??

Heres a lengthy and probably much more detailed response then you care to see…buthere it is anyway.

So, why have the "helper" column for each staff member. Is that a start date ??

My supervisors use this (or they will) and the training dates that are listed in the 'helper column' are important because they need to seehow long its been since the employee was last trained, or, more importantly,when whether a SOP (or any other document I can have in there... work instructions, safety training, PPE training and compliance, etc) is still current. Column D lists the documents latest revision date that are listed in column B. Any training dates listed in the helper column that are dated before the date in column D will automatically be shaded red indicating that that person need to be re-trained on that specific document oritem.

WHenever a new employee is added (which is very frequently,actually) and using the 'add employee form', when it puts the name in the name column, it also puts in the same name in the helper column but with a 1 one on the end. THe reason for this is because the worksheet needs to be able to sort the names 2 ways; by name and by seniority. By names was easy when i thought oh having the same name beside it but with the 1 on the end. (in order to keep both columns together when the sort it executed.)

.Cells(1, lCol + 1).Value = Me.txtEmployeeName.Value.Cells(1,lCol + 2).Value = Me.txtEmployeeName.Value & "1"

Seniority was a little trickier. I have it take the start date field (again, from the 'add new employee' form), and take the date, change it (fomat it) to date & time, and then add 1 second to the date entered and put it in the cell in the helper column in a specific cell (I hovered the cursor over it when I took the screen shot so you can see where it is) so that when the sort for 'hire date' is executed it looks first at the date time in the main column,and then sees the same date but 1 second more and sorts accordingly. Kind of funky that way, but it works. (oh, and I have 2 columns that serve as place holders (E & F) that are blank (and usually hidden ) but contain all the conditional formatting that the other cells need so that when a specific person's training is expired on a SOP it automatically lights up as needed.After a sort is done it copies formatting for these 2 cells and pastes them onto all the other name (and helper) columns.

Code for taking the date entered on the form and putting itwhere it belongs, and taking the same date, adding 1 second and putting it in the 'helper' column.

.Cells(8, lCol + 2).Value = Me.DTPicker1.Value
.Cells(8, lCol + 1).Value = "X"
.Cells(6, lCol + 2).Value = Me.DTPicker1.Value + 1 / 86400'Add 1 second to the time value.
.Cells(6, lCol + 1).Value = Me.DTPicker1.Value

f so, why not have a Staff page with that stuff in it,years of service, leave taken, etc ??

I would of done that very thing for sure, but since all the companys supervisors will be using this, they need to know that content too.

As for:

Fluff's code was recommended way back in Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=11]#11[/URL]

[COLOR=#222222][FONT=Verdana]by Rick Rothstein[/FONT][/COLOR]

Well... I missed that.

 

[Michael M]

OK.....at least you got it sorted...
Post back when you want to have more "fun" !!