Learn the solution to last week's Excel VBA challenge and test your skills with this week's new Excel challenge.
A user complains that Excel's Hidden feature is broke. After assigning the Hidden attribute to a cell and protecting the sheet, the cell contents are still visible. How do you respond?Last week we asked…
What VBA formula would you use to select the first empty cell in an Excel spreadsheet? Wow! I am impressed with the creativity and knowledge you guys have! You guys are always up for the challenge and you certainly leave me hopping sometimes! Most of you responded with similar code, with a few different strokes here and there. Aikimark was the first to post a solution that's closest to the one I use, at least conceptually:
Be careful with this formula because Excel has a problem remembering the "last cell"—and that extends to the xlLastCell constant. If you delete data from the last cell, you'd think the last cell would change, right? Unfortunately, Excel continues to remember the previously used but now empty cell. Try it yourself:
- Open a blank workbook, enter data in cell C3 and save the workbook.
- Delete the data in cell C3.
- Select any other cell (so you can see the selection move in the next step).
- Open the VBE and execute the following statement in the Immediate window:
- Return immediately to the workbook and you should find that Excel has selected C3, not A1 as you might have expected and this behavior persists through Excel 2010.
There are ways around this behavior, but I have an easier solution that avoids the problem altogether. Now, here's what I use:
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
where ws is a Worksheet object. This formula returns the row number, which I plug into data entry formulas. For instance, the following snippet would enter data into the first empty cell in column A, copying the contents of a text box on a userform (the code would be in the userform's module):
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheetname")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(lRow, 1).Value = Me.txtboxname.Value
The first part of the formula, ws.Cells(Rows.Count, 1).End(xlUp), selects the last row in the sheet in column A and then moves up to the first non-empty cell. The second part of the formula, Offset(1, 0).Row, selects the next row down.
I didn't come up with this formula and I wish I could credit the person who did, but unfortunately, I don't remember who passed this elegant solution on to me. I've been using it faithfully for a long time.
Of course, an automated data entry solution won't always be this cut and dry—assuming that the first empty cell in column A is the place to start. I believe this formula could be easily adapted to return a column instead of a row, but I've never had a need for that.
There really isn't a single "right" answer to this challenge—as you can see from the responses. A lot depends on the specific circumstances of the data entry task. What would be interesting, is to see if any of you could break the formula I recommend. So far, it has served me well.