Archive for the ‘Cells and Range’ Category
Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here).
Sub CreateCommentsSummary()
Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer
' get all cells with comment
Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
' get cell reference where user want to place the summary
Set rgOutput = _
Application.InputBox(Prompt:="Select cell where you want to put the comments summary", _
Title:="Comments Summary", Type:=8)
iRow = rgOutput.Row
iCol = rgOutput.Column
' read each cell with comment and build the summary
For Each rgCell In rgComments
Cells(iRow, iCol) = rgCell.Address ' print cell address
Cells(iRow, iCol + 1) = rgCell.Value ' print cell value
Cells(iRow, iCol + 2) = rgCell.Comment.Text 'print cell comment text
iRow = iRow + 1
Next rgCell
End Sub
Happy coding 
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only this time, we change the row into column.
Function LastColumn() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = ix
End Function
The function above will return the appropriate position of the column even when the last column containing data in a hidden state.
The results of this function is the column number, not the column name (1 for A, 2 for B, etc). If what we need is the column name, then we can use the excel column number to column name converter that I wrote before.
With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells.
In this example, background color of the cell that contains the error value will automatically change color to red, each time the Excel Worksheet activated.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim errCells As Range
Set errCells = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
errCells.Interior.Color = 255
Set errCells = Nothing
End Sub
Note that using the Cells.SpeciallCells function, we do not need to perform any looping to get Cells which contains the Error value.
This is just like automating Excel cell conditional formating process. Instead of selecting manually all the cells contains Error value or select all cells and then performing cell conditional format, with above code, we do it automatically using simple Excel VBA macro.
Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date.
The algorithm is like this: I’ll create a loop from the first row until the last row with data and check if the date is before Jan 1st, 2009 or not, if it is, simply delete that row.
Sub DeleteFilteredRows()
Dim i As Long
For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(i, "A").Value
If CDate(Cells(i, "A")) < CDate("1/jan/2009") Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
Of course I’ll make an adjustment to the code above to meet my current condition.
FIN.
A few weeks ago I received this question from someone called LS:
Hi
I want to write a macro that counts the following
- Number of cells with formula
- Number of Rows with formula
- Number of Columns with formula
Please let me know if there is a way of doing it in Excel VBA
To find out the number of cells, rows, or columns with formula in active Excel worksheet is quite simple.
In my previous post, we already have the Excel VBA code on how to select all cells with formula, so we only need to tweak this macro a little bit and get the results that we looking for.
Sub NumberOfCellsWFormula()
'select all cells with formula
Cells.SpecialCells(xlCellTypeFormulas).Select
'number of cells with formula
Debug.Print Selection.Count
'number of rows with formula
Debug.Print Selection.Rows.Count
'number of columns with formula
Debug.Print Selection.Columns.Count
End Sub
The Excel VBA procedure above will simply print the number of cells, rows, and columns of current active Excel Worksheet that containing any formula in it. Please be sure to run it from Excel VBA windows with Immediate Window turn on (View ยป Immediate Window or CTRL+G).
In short, we only need to use Range properties Count, Rows.Count, and Columns.Count to get the number of selected cells, rows or columns.
FIN.
In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not.
The following excel vba function assigned to check whether a workbook we need is open or not.
Using Workbook name as an input parameter, the function will do a looping in Workbooks collection to check all opened Workbook name, if there is a Workbook with the same name with the Workbook that we looking for, then the function will return true, and false if otherwise.
Public Function CheckSourceAvailability(sWorkBook As String) _
As Boolean
Dim wb As Workbook, bResult As Boolean
bResult = False
For Each wb In Application.Workbooks
If InStr(LCase(wb.Name), LCase(sWorkBook)) > 0 Then
bResult = True
Exit For
End If
Next wb
CheckSourceAvailability = bResult
End Function
All information about opened Workbook was saved by Excel in a collection object called Workbooks, just like Worksheets collection used by Excel to save information about all the Worksheets available.
FIN.
Sometimes in Excel, we need to find the row position of a particular text, maybe to be able to paste certain of data right below the corresponding text or else.
Very useful if we have an Excel Worksheet Template that we use a lot, with fix header on it.
' Input param: Text we want to look for
' Optional input params:
' Search direction (forward, backward),
' Search order (in row or column)
' Output: row position of the text being searched
Private Function pFindRowPos(sText As Variant, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional SearchOrder As XlSearchOrder = xlByRows) As Long
Dim lResult As Long, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=SearchOrder, _
SearchDirection:=SearchDirection, _
MatchCase:=False, SearchFormat:=False)
If Not oRg Is Nothing Then lResult = oRg.Row
pFindRowPos = lResult
Set oRg = Nothing
End Function
FIN.
