Find last FILLED Cell/Row/Column in a worksheet

If you need to find last filled cell (entry) located beyond your current view, one efficient way to do that is by using macros, like these three:

Sub FindLastCell()
'Search for any entry, by searching backwards by Rows, then by Columns.
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
 If WorksheetFunction.CountA(Cells) > 0 Then
  LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    MsgBox Cells(LastRow, LastColumn).Address
    Range(Cells(LastRow, LastColumn).Address).Select
 End If
End Sub

Sub FindLastRow()
'Search for any entry, by searching backwards by Rows.
Dim LastRow As Long
 If WorksheetFunction.CountA(Cells) > 0 Then
  LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox LastRow
    Range(Cells(LastRow, "A").Address).Select
 End If
End Sub

Sub FindLastColumn()
'Search for any entry, by searching backwards by Columns.
Dim LastColumn As Integer
Dim ColNum As Long
Dim ColLett As String
 If WorksheetFunction.CountA(Cells) > 0 Then
  LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  ColNum = LastColumn
  ColLett = Split(Cells(1, ColNum).Address, "$")(1)
    MsgBox "Col " & ColNum & "=Column " & ColLett
    Range(Cells("1", LastColumn).Address).Select
 End If
End Sub

You can assign each of the macros to separate Control buttons. If you need help with that, see this link: How to add a button and assign a macro .

To make things easier I've combined all three buttons into one, by moving and grouping them together like you see in this figure:

To find position (address) of my last filled cell, row, or column in my worksheet I just click on appropriate line of text on the button.

Solve quadratic equations, fast

Have lots of equations to solve? It's easy and can be done at lightening speed.

First, add a Command button on your worksheet: on the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button. Next, click the worksheet location at which you want the upper-left corner of the command button to appear and adjust its size and properties, so it looks similar to this presented here:

Click View Code in the Controls group. This launches the Visual Basic Editor. Add the following VBA macro code in the window: 

Sub SolveQuadraticEquation()
'QuadraticEquationSolver
Dim a, b, c, det, root1, root2 As Single
On Error GoTo ErrHandler
coef = InputBox("Enter a b c coefficientss separated by slash(/)", "Quadratic Equation Solver", "1.5/-2/3")
a = Left(coef, Application.WorksheetFunction.Search("/", coef, 1) - 1)
b = Left(Right(coef, Len(coef) - Application.WorksheetFunction.Search("/", coef, 1)), Application.WorksheetFunction.Search("/", Right(coef, Len(coef) - Application.WorksheetFunction.Search("/", coef, 1)), 1) - 1)
c = Right(coef, Len(coef) - Len(a) - Len(b) - 2)
det = (b ^ 2) - (4 * a * c)
If det > 0 Then
root1 = (-b + Sqr(det)) / (2 * a)
root2 = (-b - Sqr(det)) / (2 * a)
outp = MsgBox("Root1: " & root1 & "  Root2: " & root2, , "Roots of the equation")
ElseIf det = 0 Then
root1 = (-b) / 2 * a
outp = MsgBox("Root1: " & root1 & "  Root2: " & root1, , "Roots of the equation")
Else
outp = MsgBox("NO ROOT", , "Roots of the equation")
End If
ErrHandler:
End Sub

Close the Visual Basic Editor, and click Design Mode to ensure design mode is off.When you click the button, the macro will run and show the following window:

Enter the three coefficients of your equation, separated by slash and click OK button. The result will be displayed immediately.
 

Time, and time again

When you organize your work, in Excel or elsewhere, at some point you may want to keep track of your activities in a worksheet/workbook. Here you'll find two helpful ways of recording time spent on whatever you do. The first one allows you to record just a point in time, like a stamp of current time. The second one shows you elapsed time, difference between starting of some activity and then stopping it.

Time stamp

To create a time stamp similar to this one: 

 

• Insert a Command Button in your worksheet, by clicking Developer > Insert > Command Button (ActiveX Control).
• Right-click the button and select Properties to change its Caption to "Time Stamp"; change any other properties as needed.
  
• Right click the Command Button again, then click View Code from the context menu. When VBA window pops up, enter the following VBA script:

Sub TimeStamp()
    ActiveCell.Value = Time
    ActiveCell.NumberFormat = "hh:mm:ss AM/PM"
End Sub

• Now press the Alt + Q keys simultaneously to close the VBA window.

Select a cell where you want to place the current time stamp, then click the Command Button. You've got your stamp placed in the selected cell.

Time watcher

To create a timer (to get elapsed time), kind of time watcher, similar to this one:

• Select some range of cells for your timer (3 rows by 3 columns).
• Insert two Command Buttons (side by side), by clicking Developer > Insert > Command Button (ActiveX Control). Format them to your liking.
• Right-click one of them and select Properties to change its caption to "Start"; change any other properties, as needed. Repeat that for the second button and change its caption to "Stop".
• Right click any of the Command Buttons and click View Code from the context menu. When VBA window pops up, enter the following VBA scripts:

Private Sub StartNow()
    Dim Rcell As Range     'Replace "C25" below with your selected range   
    Range("C25").NumberFormat = "dd/mm/yyyy  hh:mm:ss"
    Set Rcell = Range("C25")
    Rcell.Value = Now()
End Sub

Private Sub StopNow()
    Dim Rcell As Range    'Replace "C25","D25","E25" below with your selected ranges
    Range("D25").NumberFormat = "dd/mm/yyyy  hh:mm:ss"
    Set Rcell = Range("D25")
    Rcell.Value = Now()
    Range("E25").Select
    Selection.NumberFormat = "d"" day(s) &"" hh:mm:ss"
    Selection.Value = Range("D25").Value - Range("C25").Value
End Sub

Private Sub CommandButton1_Click()    'Replace "1" with actual number, if necessary
StartNow
End Sub

Private Sub CommandButton2_Click()    'Replace "2" with actual number, if necessary
StopNow
End Sub

• Now press the Alt + Q keys simultaneously to close the VBA window.
  
• In your worksheet, format (optional) the cells with "Timer" and "Elapsed Time" text.

That's it. Now you can use your time watcher as needed. Click on "Start" button to start timing. Click on "Stop" button to end timing, whenever needed (even after days, weeks, months). Elapsed time will be displayed in cell E25 (in this example), including the number of days passed.

You can also show elapsed time without any VBA coding, by using a simple formula. However, prior to that you need to format cells A1:C1 according to your needs (any custom format) and enter Start and Stop times in required format. Assuming that you store Start time in A1, and Stop time in B1 (which can be entered with help of formula =NOW() ), enter in C1 the formula: =B1-A1. This will show the elapsed time.