AWE_Sheet
Updated 1/14/2024
Are you tired of spending hours writing complicated VBA code to locate and manipulate data on your Excel worksheet? Are you concerned that any updates to the sheet might break your code? Look no further! AWE_Sheet is a FREE, open-source VBA Class Module that you can add to your Excel workbook. It simplifies finding, retrieving, and manipulating data on your worksheet, reducing pages of tedious code to just a few calls. With AWE_Sheet, you can manually add or move columns and rows of data on your sheet without the need to update your macros. It's the perfect answer for creating efficient business solutions with minimal code. Boost your productivity with AWE_Sheet!
Find Cells
Worksheets - Initialize (open) a Worksheet as ReadOnly or Writable, located anywhere in your network (SharePoint, Teams, hard drive...).
Flexible - AWE_Sheet.FindCells replaces Worksheet.Cells, but with greater functionality. Find one or more Cells by column name, number, or range; and the column's rows by search pattern, number, or range.
Easy - AWE_Sheet takes care of the hard work, allowing you to focus on solving your business problem.
The below macro shows how to declare and initialize AWE_Sheet, locate columns based on their names, and search for cells based on data patterns. The sheet and its column headers are defined during the Initialize call. After that, the FindCells function is utilized to find data ranges by specifying the column name and cell patterns.
Public Function FindCells()
Dim aweSh As New AWE_Sheet, rEmpNm As Range
'--- Initialize AWE_Sheet ---
aweSh.Initialize TableOrSheetNmOrObj:="Timecard", HdrRowNb:=3, WBFullFileNmOrObj:=Nothing, WBReadOnly:=False
'--- Find Cells by Column Name and a Search pattern: unknown chars (*), hyphen (-), 2 chars (??), ending in 01 ---
For Each rEmpNm In aweSh.FindCells("*-??01", "Employee Name", True)
Debug.Print rEmpNm.Value
'--- Use the Employee Name's range to find the row's associated column (TaskID) values ---
Debug.Print aweSh.FindCells(rEmpNm, "TaskID").Value
Next rEmpNm
End Function
Filter Rows
Worksheet Extensibility - Use AWE_Sheet in combination with any Worksheet function that uses a range (filters, sum)...
AWE_ColHdr and AWE_ColNbr Retrieve a column's range and number (respectively).
DataColumns - Retrieve one or more data columns (Union together) by column name, number or range.
FindCells - As described above, retrieves a cell's range by column name on the given row.
Dynamic - Use column names and search values allowing you to insert or delete columns and rows with minimal impact to your code.
The below macro combines the power of AWE_Sheet to find column ranges with the Worksheet Function to Filter over a million rows and cells of data in less than a second. Say goodbye to worrying about tedious column numbers and named ranges forever.
Public Function FilterRows()
Dim aweSh As New AWE_Sheet, rRate As Range
'--- Initialize the AWE_Sheet ---
aweSh.Initialize TableOrSheetNmOrObj:="Timecard", HdrRowNb:=3
'--- Create Filter using an Array ---
aweSh.ColHdr("TaskID").AutoFilter field:=aweSh.ColNbr("TaskID"), _
Criteria1:=Array("Task-127", "Task-145"), Operator:=xlFilterValues
'--- Add a From and To Date to the Filter ---
aweSh.ColHdr("Date").AutoFilter field:=aweSh.ColNbr("Date"), Operator:=xlAnd, _
Criteria1:=">=" & CDate("1/1/2021"), Criteria2:="<=" & CDate("12/31/2021")
'--- Loop through each found row and calculate\update its revenue ---
For Each rRate In aweSh.DataColumns("Rate").SpecialCells(xlCellTypeVisible)
rRate.Value = 227.5
aweSh.FindCells(rRate, "Revenue").Value = rRate.Value * aweSh.FindCells(rRate, "Hours").Value
Next rRate
'--- Sum Revenue for all filtered rows ---
Debug.Print WorksheetFunction.Sum(aweSh.DataColumns("Revenue").SpecialCells(xlCellTypeVisible))
End Function
Implementation
Copy AWE_Sheet to a Class Module in your Workbook. See How to Create a Class Module for more information.
Function definitions, parameters and examples are included in the comments before each property or function.
Visual Basics Class IntelliSense will provide a code completion aid for the AWE_Sheet Class Module by listing its functions and parameters as you type your code.
Implement AWE_Sheet into your Workbook by copying the below code into a Class Module called, AWE_Sheet. Once implemented, begin simplifying your code so that you can spend more time focusing on solving your business problem.
'*****************************************************************************************************
'* AWE_Sheet: AWE_Sheet include prewritten functions that allow you to find, retrieve, and *
'* manipulate data on your sheet with just a few calls so you can accomplish your *
'* business solution with less code. *
'* Author: Mike Libby *
'* Website: AutomationWithExcel.com *
'*****************************************************************************************************
Option Explicit
Private m_Worksheet As Worksheet '--- The Worksheet object ---
Private m_HdrRowNb As Long '--- The column header row number ---
Private m_HdrArr As Variant '--- An Array of column header names ---
'----------------------------------------------------------------------------------------------------
' Initialize - Initialize the AWE_Sheet object
' Parameters:
' TableOrSheetNmOrObj as Variant - A Worksheet name (String), Worksheet Object, or Table ListObject
' Optional HdrRowNb as Long - The header's row number. Default = 1
' Optional WB as Workbook - The Worksheet's Workbook Object or FullFileName. Default = ThisWorkbook.
'
' Examples:
' ActiveSheet - Dim AWESh as new AWE_Sheet: AWESh.Initialize ActiveSheet, 3
' Sheet name - Dim AWESh as new AWE_Sheet: AWESh.Initialize "Worksheet Name", 3
' Table Name - Dim AWESh as new AWE_Sheet: AWESh.Initialize ActiveSheet.ListObjects("TableNm")
' Read only Workbook - Dim AWESh as new AWE_Sheet: AWESh.Initialize "ShNm", 3, "FullFileNm", True
'----------------------------------------------------------------------------------------------------
Public Function Initialize(TableOrSheetNmOrObj As Variant, Optional HdrRowNb As Long = 1, _
Optional WBFullFileNmOrObj As Variant = Nothing, _
Optional WBReadOnly As Boolean = False)
'--- Workbook ---
Dim wb As Workbook
Select Case TypeName(WBFullFileNmOrObj)
Case "Workbook": Set wb = WBFullFileNmOrObj
Case "String": Set wb = Workbooks.Open(Filename:=WBFullFileNmOrObj, ReadOnly:=WBReadOnly)
Case Else: Set wb = ThisWorkbook
End Select
'--- Worksheet ---
Set m_Worksheet = Nothing
Select Case TypeName(TableOrSheetNmOrObj)
Case "Worksheet": Set m_Worksheet = TableOrSheetNmOrObj
Case "String": Set m_Worksheet = wb.Sheets(CStr(TableOrSheetNmOrObj))
Case "ListObject": Set m_Worksheet = TableOrSheetNmOrObj.Parent
m_HdrRowNb = TableOrSheetNmOrObj.HeaderRow.Row
End Select
'--- Worksheet not found, Critical Error Msg, Stop Processing ---
If m_Worksheet Is Nothing Then
MsgBox "Invalid Sheet - Ensure the sheet exists within the Workbook", vbCritical: End
End If
'--- Header Row ---
m_HdrRowNb = HdrRowNb
m_HdrArr = HeaderRow.Value
End Function
'====================================================================================================
' === Properties ====================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' HeaderRow - Return the range for the entire header row
' Example: Dim rng As Range: Set rng = AWESh.HeaderRow
'----------------------------------------------------------------------------------------------------
Public Property Get HeaderRow() As Range
With m_Worksheet
Set HeaderRow = .Range(.Cells(m_HdrRowNb, 1), .Cells(m_HdrRowNb, _
.Cells(m_HdrRowNb, .Columns.Count).End(xlToLeft).Column))
End With
End Property
'----------------------------------------------------------------------------------------------------
' LastDataRow - Return the last data row's number
' Example: Dim rng as Range: Set rng = AWESh.LastDataRow.row
'----------------------------------------------------------------------------------------------------
Public Property Get LastDataRow() As Range
With m_Worksheet
Dim iRow As Long: iRow = .Cells.Find("*", LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If iRow > m_HdrRowNb Then Set LastDataRow = .Range(.Cells(iRow, 1), .Cells(iRow, _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column))
End With
End Property
'----------------------------------------------------------------------------------------------------
' AppendDataRow - Return the range of the the Sheet's LastDataRow + 1
' Example: Dim rng as Range: Set rng = AWESh.AppendDataRow
'----------------------------------------------------------------------------------------------------
Public Property Get AppendDataRow() As Range
With m_Worksheet
Dim iRow As Long: iRow = .Cells.Find("*", LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If iRow < m_hdrrow Then iRow = m_HdrRowNb
iRow = iRow + 1: Set AppendDataRow = .Range(.Cells(iRow, 1), .Cells(iRow, _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column))
End With
End Property
'----------------------------------------------------------------------------------------------------
' DataBodyRange - Return the range for all rows under the sheet header or Nothing if none found
' Param: IncludeHeader as Boolean - True if the header should be included; otherwise False (default)
' Example: Dim rng As Range: Set rng = AWESh.DataBodyRange
'----------------------------------------------------------------------------------------------------
Public Property Get DataBodyRange(Optional IncludeHeader As Boolean = False) As Range
Dim iFirstRow As Long: If IncludeHeader = True Then iFirstRow = m_HdrRowNb Else iFirstRow = m_HdrRowNb + 1
Dim iLastRow As Long: iLastRow = m_Worksheet.Cells.Find("*", LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If (IncludeHeader = False And iLastRow = m_HdrRowNb) Or iLastRow = -1 Then
Set DataBodyRange = Nothing: Exit Function
End If
With m_Worksheet
Set DataBodyRange = .Range(.Cells(iFirstRow, 1), .Cells(iLastRow, _
.Cells(m_HdrRowNb, .Columns.Count).End(xlToLeft).Column))
End With
End Property
'----------------------------------------------------------------------------------------------------
' Sheet - Returns the worksheet used by AWE_Sheet
' Examples - Dim sh as Worksheet: Set sh = AWESh.Sheet
'----------------------------------------------------------------------------------------------------
Public Property Get Sheet() As Worksheet: Set Sheet = m_Worksheet: End Property
'----------------------------------------------------------------------------------------------------
' Workbook - Returns the Workbook used by AWE_Sheet
' Examples - Dim wb as Workbook: Set wb = AWESh.Workbook
'----------------------------------------------------------------------------------------------------
Public Property Get Workbook() As Workbook: Set Workbook = m_Worksheet.Parent: End Property
'====================================================================================================
' === COLUMNS =======================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' DataColumns - Return the range for one or more columns based on their column number, name or range.
' Return: one or more column ranges.
' Params: ColNbrOrNameOrRanges as Variant. A list of column numbers, names, or Ranges.
' Example: Dim rng As Range: Set rng = DataColumns("Column 1", "Column 5", "Column 6")
'----------------------------------------------------------------------------------------------------
Public Function DataColumns(ParamArray ColNbrsOrNamesOrRanges() As Variant) As Range
Dim RtnRng As Range, Var As Variant
For Each Var In ColNbrsOrNamesOrRanges
If RtnRng Is Nothing Then Set RtnRng = DataBodyRange.Columns(ColNbr(Var)) _
Else: Set RtnRng = Union(RtnRng, DataBodyRange.Columns(ColNbr(Var)))
Next Var
Set DataColumns = RtnRng
End Function
'----------------------------------------------------------------------------------------------------
' ColNbr - Search for and return a Column's number on the header row
' Params: ColNbrOrNameOrRange as Variant. A column number, name, or Range.
' Example: Dim iCol as Long: iCol = AWESh.ColNbr("Column Name")
' Note: a critical message displays if the column is not found and processing stops.
'----------------------------------------------------------------------------------------------------
Public Function ColNbr(ColNbrRangeOrName As Variant) As Long
Dim iColNbr As Long
'--- Column Number ---
On Error Resume Next
Select Case TypeName(ColNbrRangeOrName)
Case "Long", "Integer": iColNbr = ColNbrRangeOrName
Case "Range": iColNbr = ColNbrRangeOrName.Column
Case "String": iColNbr = -1: iColNbr = Application.Match(ColNbrRangeOrName, m_HdrArr, 0)
'--- Try again if column was not found ---
If iColNbr = -1 Then
m_HdrArr = HeaderRow.Value
iColNbr = Application.Match(ColNbrRangeOrName, m_HdrArr, 0)
End If
End Select
On Error GoTo 0
If iColNbr = -1 Then
MsgBox "Critical Error " & m_Worksheet.Name & "::AWE_Sheet::ColNbr" & vbLf & vbLf & _
"Cannot find column, """ & ColNbrRangeOrName & """.", vbCritical: End
End If
ColNbr = iColNbr
End Function
'----------------------------------------------------------------------------------------------------
' ColHdr - Find and return a column header's cell range.
' Params: ColNbrOrNameOrRange as Variant. A column number, name, or Range.
' Example: Dim rng As Range: set rng = AWESh.ColHdr "Column Name"
'----------------------------------------------------------------------------------------------------
Public Function ColHdr(ColNbrOrNameOrRange As Variant) As Range
Set ColHdr = m_Worksheet.Cells(m_HdrRowNb, ColNbr(ColNbrOrNameOrRange))
End Function
'====================================================================================================
' === FIND CELLS ====================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' FindCells: Search for one or more cell ranges using any combination of a column's and row's number,
' range, or a search value.
' Return: One or more cell ranges that were found in the column.
' Params: RowNbrRangeOrSrchStr - The row number, range, or a string that is being searched for.
' For String Wildcards use: "*" match zero or more unknown characters;
' "?" match any single unknown character in a string;
' "#" match any single digit (0 thru 9) in a string;
' "[charlist]" match any single character in the charlist;
' "[!charlist]" match any single character not in the charlist;
' Note, a hyphen in a charlist separates upper and lower bounds, i.e.
' [a-zA-Z0-9] matches lower and upper case A thru Z and 0 thru 9.
' ColNbrRangeOrName - The column number, range, or name string that contains the row that is
' being searched for.
' SearchAllRows - True if all rows are being searched or false (default) if only the first
' occurrence is to be found. Note, sort order for multiple rows is
' non-contiguous cells first, followed by continuous cells.
' Example:
' Dim rng as Range
' '--- Find cells in column, "Name" that contain a hyphen followed by 4 digits and a character ---
' For Each rng In AWESh.FindCells("*-####?", "Name", True)
' '--- Print each found value and the corresponding row from the "Date" column ---
' Debug.Print rng.Value & " - " & AWESh.FindCells(rng, "Date").Value & " - " & rng.Row
' Next rng
'----------------------------------------------------------------------------------------------------
Public Function FindCells(RowNbrRangeOrSrchStr As Variant, ColNbrRangeOrName As Variant, _
Optional SearchAllRows As Boolean = False) As Variant
Dim rCell As Range, rSrchRng As Range, sFirst As String, rRtnRngs As New Collection
Dim iLastRow As Long, iColNbr As Long: iColNbr = ColNbr(ColNbrRangeOrName)
'--- SearchFor row(s) in iColNbr ---
With m_Worksheet
Select Case TypeName(RowNbrRangeOrSrchStr)
Case "Long", "Integer": Set rCell = .Cells(RowNbrRangeOrSrchStr, iColNbr)
Case "Range": Set rCell = .Cells(RowNbrRangeOrSrchStr.Row, iColNbr)
Case "String":
'--- Get the range of the search column ---
iLastRow = .Cells.Find("*", LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rSrchRng = .Range(.Cells(m_HdrRowNb + 1, iColNbr), .Cells(iLastRow, iColNbr))
'--- Find the first occurance ---
Application.FindFormat.Clear
Set rCell = rSrchRng.Find(What:=CStr(RowNbrRangeOrSrchStr), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlWhole)
End Select
If SearchAllRows = False Then
Set FindCells = rCell: Exit Function
End If
End With
'--- If SearchAllRows is True and the first row was found then find all occurances ---
If SearchAllRows = True And TypeName(RowNbrRangeOrSrchStr) = "String" Then
If Not rCell Is Nothing Then
sFirst = rCell.Address
Do
rRtnRngs.Add rCell
Set rCell = rSrchRng.FindNext(rCell)
Loop While rCell.Address <> sFirst
End If
End If
Set FindCells = rRtnRngs
End Function
'====================================================================================================
' === SORT and FILTER ===============================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' SortSheet - Sort the sheet by ascending or descending row values in one or more columns.
' Params: ColIdxOrNames As Variant - Column numbers or names to sort by. Prepend "<" to reverse sort.
' Example: Sort Col1 ascending and Col2 decending - AWESh.Sort "Col1 Name", "<Col2 Name"
'----------------------------------------------------------------------------------------------------
Public Function SortSheet(ParamArray ColIdxOrNames() As Variant)
Dim ColIdxOrName As Variant, ColIdx As Long
With m_Worksheet.Sort
For Each ColIdxOrName In ColIdxOrNames
If Left(ColIdxOrName, 1) = "<" Then
ColIdxOrName = Trim(Right(ColIdxOrName, Len(ColIdxOrName) - 1))
.SortFields.Add key:=DataColumns(ColNbr(ColIdxOrName)), Order:=xlDescending
Else
If Left(ColIdxOrName, 1) = ">" Then _
ColIdxOrName = Trim(Right(ColIdxOrName, Len(ColIdxOrName) - 1))
.SortFields.Add key:=DataColumns(ColNbr(ColIdxOrName)), Order:=xlAscending
End If
Next
.SetRange DataBodyRange: .Header = xlNo: .Apply: .SortFields.Clear
End With
End Function
'----------------------------------------------------------------------------------------------------
' ClearSheetFilters - Clears all Filters on the Sheet
' Example: AWESh.ClearSheetFilters
' Note: Displays a message and ends processing if the sheet is protected and processing stops.
'----------------------------------------------------------------------------------------------------
Public Function ClearSheetFilters()
On Error Resume Next
Dim cnt As Long: cnt = m_Worksheet.AutoFilter.Range.Areas.Count
If Err.Number > 0 Then HeaderRow.AutoFilter field:=1, VisibleDropDown:=True
On Error Resume Next
'--- Verify that the sheet isn't protected ---
If m_Worksheet.ProtectContents = True Then
MsgBox "Critial Error: " & m_Worksheet.Name & "::ClearSheetFilters" & vbLf & vbLf & _
"Unprotect sheet before clearing filters.", vbCritical: End
End If
If m_Worksheet.AutoFilter.Range.Areas.Count > 0 Then m_Worksheet.ShowAllData
End Function