Excel 2010/Snippets/NamedRange

ListObject
'Get ListObject object from a Named Range 'Parameters: '@wb - Workbook object, the workbook to get ListObject from '@nm - String value, the name of the Named Range to obtain ListObject 'Return: ' - ListObject object, was able to backwards extract the ListObject associated with the Named Range ' - Nothing, there was not a ListObject associated with the Named Range. Function GetListObject(wb As Workbook, nm As String) As ListObject On Error Resume Next Dim rng As range: Set rng = wb.Names(nm).RefersToRange Dim valid As Boolean: valid = (rng.ListObject.name <> "") On Error GoTo 0 If valid Then Set GetListObject = rng.ListObject Else Set GetListObject = Nothing End If End Function
 * Takes a Range Name and returns the ListObject/Table it is contained in

Get/Set Values

 * Returns the Value in Column 2, or specified column, of the given NamedRange
 * tbl
 * String value, Range name to use. Can include Table Ranges, or just a simple non-defined Range with 2 or more columns.


 * key
 * Variant object/value, The key value within tbl to search for. Uses Column 1 of the multi-column range.


 * col
 * (Optional)Long value, the Column in tbl to return the value from. Defaults to the 2nd Column but can specify another Column index.

Function GetValue(tbl As String, key, Optional col As Long = 2) Dim rng As Excel.Range, i As Long Set rng = Range(tbl) For i = 1 To rng.Rows.Count If rng(i, 1).Value = key Then GetValue = rng(i, col).Value Exit Function End If   Next GetValue = Nothing End Function

Sub SetValue(tbl, key, val, Optional col As Long = 2) Dim rng As Range, i As Long Set rng = Range(tbl) For i = 1 To rng.Rows.Count If rng(i, 1).Value = key Then rng(i, col).Value = val End If   Next End Sub
 * Sets the Value in Column 2, or specified column, of the given NamedRange with the specified Key as a reference point.

VLookup Replacement
'@rng - String value, Range or Table value '@key - String value, The Value to lookup in @rng '@col - Long value, The Column in @rng that contains the value to be returned Function Lookup(rng As Range, key As Variant, Optional col As Long = 2) Lookup = WorksheetFunction.VLookup(key, rng, col, True) End Function
 * To help with using the VLookup Worksheet Function, without typing the Class Name (WorksheetFunction) and then the Method/Property (VLookup) every single time you need to utilize it in VBA.

Hide Empty Rows
Sub HideEmptyRows(HideRange As Range) Dim rcount As Long, r As Long If HideRange Is Nothing Then Exit Sub If HideRange.Areas.Count > 1 Then Exit Sub
 * Hides the EntireRow if the the sum of all Cells in the Row is zero (0).

With HideRange rcount = .Rows.Count For r = rcount To 1 Step -1 If .Rows(r).Hidden = False Then If Application.CountA(.Rows(r)) = 0 Then .Rows(r).EntireRow.Hidden = True End If        End If      Next r   End With End Sub

Empty Rows
'Empty all rows within a ListObject (Table) 'Parameters: '@rng  - Range object, '@rstart - Long value, First row in the ListObject to start emptying row/col contents '@ccol - Long value, The Control column to validate if it is empty before processing other columns '@hide - Boolean value, Whether empty rows should be hidden Sub EmptyRows(rng As Range, Optional rstart As Long = 1, Optional ccol As Long = 1, Optional hide As Boolean = False) 'If 'rng' object is null then prematurely exit sub If rng Is Nothing Then Exit Sub Dim rcount As Long, r As Long With rng rcount = .Rows.Count For r = rstart To rcount str = r & "/" & rcount 'Check if row is hidden or not If .Rows(r).Hidden Then 'Unhide Row .Rows(r).Hidden = False End If        'Obtain value of the current cell, by the control column, for checking Dim val As String: val = .Cells(r, ccol).Value 'Check if value is Null, after trimming all blank or empty spaces If Not IsNull(Trim(val)) Then
 * Empty the rows specified within the @rng parameter
 * Also allows the use of Hide Empty Rows, to hide the rows that were just emptied.

'Check if the Row contains more than 0 cells with value If Application.CountA(.Rows(r)) > 0 Then .Rows(r).ClearContents    'Clear Contents of row End If        End If         Next End With If hide Then HideEmptyRows rng   'Hide excess rows from view End If End Sub

First Column Index
'@rng - Range object '@ccol - Long value 'Notes: ' - BLIND METHOD, Deletes Table Rows using @ccol as a validation column. '      * Any cell in @ccol that is Blank/Empty will constitue a deletion '          of the EntireRow, irregardless if there is Data in the other '          columns of the Row. Sub DeleteTableRows(rng As Range, Optional ccol As Long = 1) If rng Is Nothing Then Exit Sub If rng.Areas.Count > 1 Then Exit Sub If Application.CountA(rng.Columns(ccol)) = rng.Rows.Count Then Exit Sub On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End Sub
 * This method will Delete any Row in @rng assuming that the first column in @rng is Blank

N-th Column Index
Sub DeleteEmptyRows(rng As Range, Optional ccol As Long = 1) If rng Is Nothing Then Exit Sub If rng.Areas.Count > 1 Then Exit Sub ' Deletes all empty rows in Rng ' Processes Rows in a Backward approach ' Example: DeleteEmptyRows Selection ' Example: DeleteEmptyRows Range("A1:D100") Dim col As Range: Set col = rng.Columns(ccol) On Error Resume Next col.SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End Sub
 * This method will Delete any Row in @rng assuming that the @ccol column in @rng is Blank

In Line
Sub EvaluateRange(rng As String) With Range(rng).CurrentRegion .Cells.Copy .Cells.PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub
 * This method will take the values in a specified Range and in-place Evaluate the results of a Formula in the Cells.