Function to list row height

This week I successfully tested a function which accurately shows the height of rows in Excel. I spot checked row heights dozen of times, and can confirm that it's accurate.

Press ALT + F11 to enter Visual Basic, and in the project list to the left right click on your worksheet and create a new module to enter this vba code in:

Function RowH(r As Range)

RowH = r.RowHeight

End Function

The vba code will allow you to enter a formula - RowH - that can reference a cell and return the number of points of the row height.

Note that the result will not automatically update when rows are re-sized.

checking for overlapping deposition designations

If you have a list of page and line deposition designations and want to see not only where they match marked page and lines from a previous set, but also where they overlap, you can use the Excel spreadsheet that I have posted below.

1. Begin by placing the page and line ranges for your new set of designations in columns A to D.

2. Put the page and line ranges of the designations from a different set that you want to compare the new set against in columns M to P.

3. In column E, enter a LOOKUP formula to find where the page listed in column A falls between the page ranges in columns M and O.

It is set to lookup where the value in A falls between the values in column M and O, and then return the value in column M.

4. The formula is repeated in column F, but set to return the line number in column N where the page in A falls between M and O.

5. The LOOKUP formula is then modified in column G to search for where the ending page range in the new set falls with the page ranges of the old set. We can fhus find both where a range in the new set begins within a range from the old set, falls entirely within it, or ends before the range from the old set does.

6. Repeat the formula from column G, in column H so that it returns the ending line number from the corresponding range in the old set: =LOOKUP(2,1/((C2>=M:M)*(A2<=O:O)),P:P)

7. In column I a IF . . . THEN formula is used to check if the page in A is greater than the returned range from the old set, and so then must be within the old range, or if the line number at the beginning of the new range is greater or equal than that in the corresponding range from the old set.

8. The formula in column J checks where the line number from the new range is less than or equal to that of the corresponding old range, if the page number is not less than the ending page number in the range from the old set.

Where a range from the new set falls entirely within a range from the old set, 'WITHIN RANGE' will appear in both column I and J. Where a range from the new set begins within a range from the old set, but continues after it, 'WITHIN RANGE' will only appear in column I.

Where the range from the new set does not begin within a range from the old set, but ends within it, 'WITHIN RANGE' will only appear in column J.

Note that this spreadsheet will not show instances where a range from the new set begins before and ends after a range from the old set. To find these instances, swap the old and new ranges.

Overlapping Deposition Designations

.xlsx

Download XLSX • 12KB

Making Multiple Selections from an Excel Drop Down List

It's widely known that Excel's Data Validation tool can be used to create a drop down list for all cells in a selected range. You'll simply be able to click on an down arrow next to a cell and select the entry. This feature can speed up a lengthy manual review of documentation tracked on a spreadsheet. Tonight's tip will show how you can use VBA code to change this tool to allow for multiple entries from the list to be saved in a cell.

To get started follow these steps:

1. Select the data range you want the 'pick list' to be available for.

2. Go to Data . . . Data Validation

3. In the dialog box on the Settings tab, choose 'List' from the Allow menu. Then click on the arrow next to the Source box to select the data range which will contain the entries you want to appear in the drop down list. The entries can be listed on a different worksheet.

If you have to add more items to the list as you go along, check off the box labeled, 'Apply these changes to all other cells with the same settings', and then expand the source range.

4. This will give you a drop down list that will let you select any one of the entries for the cell. If you select a second entry, the first entry will be overwritten.

You can use VBA code posted here, and modified below to make it possible to select multiple entries. The modified version lets you clear the cell after entries have been made (thanks to Susan Lynn for her suggestion); leaves the first entry you select at the beginning of the cell; and puts each new entry on a new line.

On this line of the VBA code you can set the delimiter you want to use between the entries.

xStrNew = " " & Target.Value & Chr(10)

In my version of the code I've entered the Chr(10) reference to put each new entry on a separate line. If 'Chr(10)' is changed to "; " each new entry will be separated with a semi-colon.

In order to clear a cell, select it and choose 'Clear Contents' from the right click menu.

Private Sub Worksheet_Change(ByVal Target As Range)

'UpdatebyExtendoffice20180510

Dim I As Integer

Dim xRgVal As Range

Dim xStrNew As String

Dim xStrOld As String

Dim xFlag As Boolean

Dim xArr

On Error Resume Next

Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)

If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub

If Intersect(Target, xRgVal) Is Nothing Then Exit Sub

Application.EnableEvents = False

xFlag = True

xStrNew = " " & Target.Value & Chr(10)

Application.Undo

xStrOld = Target.Value

If InStr(1, xStrOld, xStrNew) = 0 Then

xStrNew = xStrOld & xStrNew

Else

xStrNew = ""

End If

Target.Value = xStrNew

Application.EnableEvents = True

End Sub