Clear a Worksheet

Want to clear your current worksheet?  Use this:

'Select the whole spreadsheet
Cells.Select
'Delete Everything
Selection.ClearContents
'Make Cell A1 the Currently Selected Cell
Range("A1").Select

Quick Tip for Viewing Excel Macro's

Want to quickly view your Excel Macro's?  Click ALT-F11.  This will pull up the Microsoft Visual Basic Editor with all of your current workbooks' macros.  Quick, simple access to your macros!

Delete the Last Line of Data

I frequently import a specific data-set of text data into Excel.  After I import the data, I need to delete the last line of data. Following is a cool macro to do this.

    Range("A1").Select ‘Select cell A1
    Selection.End(xlDown).Select ‘Press the END-DOWN keys to move to the last row of data
    Selection.EntireRow.Delete ‘Delete that row

Check if a File Exists

Here is a quick script to check if a file exists.

Sub TestForFileSubroutine() 

   ‘Create a File System Object 
  Set objFSO = CreateObject("Scripting.FileSystemObject")

   ‘Set a variable that points to the File
   cFilePathAndName = “c:\TEMP\MyFileName.TXT”

   ‘Does the file exist?
   If Not objFSO.FileExists(cFilePathAndName) Then
       MsgBox "FILE DOES NOT EXIST!"
       Exit Sub
   End If
 
   ‘Do a little clean up and delete the File System Object
   Set objFSO = Nothing

End Sub

Get the Logged in Users Name

To get the name of the currently logged in user, you need to first, create a function to call a Windows API function to get the user name.  The function looks like this;

 

 'Define the Window API Function
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

 

'This is Function Returns the user name of the currently logged in user
'This works in Windows 95, XP and Vista
Function ReturnUserName() As String
Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnUserName = UCase(Trim(tString))
End Function

 

Next, within your macro code, call the function and the username will be returned in a variable.  For example;

 

cUser = ReturnUserName()

 

...will return the username in a variable called cUser.

Excel Macro to Create a Random Password

Following is a quick and dirty formula to generate a random password. This particular formula generates a password consisting of;

• 1 lowercase letter
• 1 number
• 1 lowercase letter
• 1 lowercase letter
• 1 number
• 1 lowercase letter

The password will appear in the currently selected cell.

Sub GeneratePassword() 
ActiveCell.Value = Chr(Rnd() * (122 - 97) + 97) & Chr(Rnd() * (57 - 48) + 48) & Chr(Rnd() * (122 - 97) + 97) & Chr(Rnd() * (122 - 97) + 97) & Chr(Rnd() * (57 - 48) + 48) & Chr(Rnd() * (122 - 97) + 97)
End Sub