Posted on Leave a comment

How to view more than two places in a Worksheet

The easiest solution is to just open additional windows for the worksheet. Display the View tab of the ruler and click New Window in the Window group (or choose Window | New Window if you have a version prior to Excel 2007).
image
Excel opens additional windows that contain the exact same worksheet. You can then click Arrange All in the Window group of the ribbon to arrange the windows any way desired (or choose Window | Arrange for earlier Excel versions).
image
When you open additional windows in this manner, the windows are independent of each other, meaning that you can scroll them independently. If you make a change in one window, the same change is made in all the windows. (This makes sense, since they all display the same data.)
Once the windows are situated the way you want them, you could save the arrangement as a view – Custom Views in the Workbook Views group of the ruler (or View | Custom Views for earlier versions). That way you could quickly recall the appearance of your windows any time you desire.
image

Posted on Leave a comment

How to delete empty rows

Use the following VB Macro to delete empty rows:

‘/============================/
‘ Sub Purpose: Delect all blank ROWS within the active cell’s
‘ Used Range

Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range
On Error Resume Next
‘only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange
‘calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count ‘# of rows in used area
dbMinRow = rng.Cells(1, 1).Row ‘1st row in used area
dbMaxCol = rng.EntireColumn.Count ‘# of columns in used area
For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i – 1, 0).EntireRow.Delete
End If
End If
Next i
Set rng = Nothing
End Sub
‘/============================/

Posted on 1 Comment

Windows Explorer Send To Mail Recipient always sends the email in plain text

Send email as attachment

Whenever you select to send a file by email  using Windows Explorer Send To Mail option (right click on it and select Send To | Mail recipient) the email is automatically in plain text and additional text is included:

image

If you want to send the email in HTML automatically and remove the additional text from the email body you can use the solution offered by Microsoft “Outlook: Send To Mail Recipient opens an Outlook plain text message”. Unfortunately this solution allows you to attach only one file – too bad.
I had searched for a fix to this problem and I stumbled upon Stephen’s Scribble Pad describing how to replace the default MAPIMail link with a visual basic script file. Additionally the script includes the default signature:

 

Create a new text document and paste the above code and save the file. Rename the file to Mail Recipient.vbs and then create a shortcut and rename it to Mail Recipient or whatever name you like. Customize the shortcut’s icon (I use the default Outlook 2007 icon, found in Program Files) and move your new shortcut to your “Send to” folder (i.e. for Windows 7 C:\Users\Nicu\AppData\Roaming\Microsoft\Windows\SendTo).

Rather don’t delete the original MAPIMail link, just change it’s attributes to hidden (you may want it back again later).