Fandom

Microsoft Wikia

Excel 2010/Snippets/NamedRange

< Excel 2010 | Snippets

870pages on
this wiki
Add New Page
Talk0 Share

ListObject

  • Takes a Range Name and returns the ListObject/Table it is contained in

'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


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.

Blind Code
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


  • Sets the Value in Column 2, or specified column, of the given NamedRange with the specified Key as a reference point.

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


VLookup Replacement

  • 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.

'@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


Hide Empty Rows

  • Hides the EntireRow if the the sum of all Cells in the Row is zero (0). [1]

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
 
   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 the rows specified within the @rng parameter
  • Also allows the use of Hide Empty Rows, to hide the rows that were just emptied.

'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    
 
            '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


Delete Table Rows

First Column Index

  • This method will Delete any Row in @rng assuming that the first column in @rng is Blank

Blind Code
'@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


N-th Column Index

  • This method will Delete any Row in @rng assuming that the @ccol column in @rng is Blank [2]

Blind Code
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


Evaluate

In Line

  • This method will take the values in a specified Range and in-place Evaluate the results of a Formula in the Cells.[3]

Blind Code
Sub EvaluateRange(rng As String)
   With Range(rng).CurrentRegion
      .Cells.Copy
      .Cells.PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
End Sub


References

  1. Excel Tips - Delete Rows & Columns using VBA in Microsoft Excel
  2. Excel Tips - Delete Rows & Columns using VBA in Microsoft Excel
  3. Change Formula's to values

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.