Posts Tagged ‘vba macro’
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.
Let say we want to distinguish between ordinary cells and cells with formula in it, so we give a special background color to the cells with formula.
For doing it manually is time consuming, especially on Excel Worksheet with large of cells with formula in it (usually a very complex worksheet).
The following vba macro code will do the trick for us.
Sub SelectAllCellsWithFormula()
'select all cell with formula
Cells.SpecialCells(xlCellTypeFormulas).Select
'change cell background color
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
End Sub
FIN
Ever want to paste some data into new empty cell after the last cell with data in certain column? Then take a look at this simple macro.
'first, select cell in the first row of that column, like A1, K1, etc
Range("A1").Select
'move to the last cell with data
Selection.End(xlDown).Select
'move to one row below it
ActiveCell.Offset(1, 0).Select
'paste the copied data in there
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Simple isn’t it? Simple code, but will help us a lot each time we wanna move the active cell into one cell below the last cell contained any kind of data, in the same column.
One requirement needed to use this simple excel vba macro, first we need to select the first cell in the same column where we wanna do the selection.
Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells.
The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on the worksheet, and then using Excel FIND function we will find which cell contain that minimum/ maximum value.
Excel VBA macro implementation of the algorithm above will look like below, change code Application.Min(oRg) into Application.Max(oRg) to find the maximum value instead of minimum value.
Sub FindMinValue()
Dim oRg As Range, iMin As Variant
Set oRg = Cells
'Finding the minimum value
iMin = Application.Min(oRg)
'Select cell containing the min value
oRg.Find(What:=iMin, _
After:=oRg.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select
'Change selected cell format
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Displaying min value info
With Selection
MsgBox "Min value : " & iMin & vbCrLf & _
"Cell position " & vbCrLf & _
"Row : " & .Row & vbCrLf & _
"Column : " & .Column
End With
End Sub
As an additional info, the code above will display the cell info, row and column position of the cell containing the minimum/maximum value.
Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide.
Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting Hide, they simply need to choose UnHide, and all the Worksheets in hiding will be revealed.
The above method only working in Microsoft Excel before 2007, in Excel 2007, the menu to hide and unhide columns/cells/worksheets are hidden by default, but we can add this menu into Excel Quick Access Toolbar (tiny menu at top left corner of the window), by accessing menu Customize Quick Access Toolbar (tiny down arrow on the right), select More Commands… » Choose commands from Home Tab » and select menu Hide & Unhide.
The other method on how to hide our Excel Worksheet, more secured, and not really well known by people is using Worksheet xlSheetVeryHidden properties.
To perform this method, first we need to go to Microsoft Visual Basic Editor (ALT+F11), in the project explorer (if the explorer is not showing, click CTRL+R), select Worksheet that we want to hide, then go to Properties Windows (F4), and in the Visible properties, select 2 – xlSheetVeryHidden like in this picture:
If we follow all the guide above, the Worksheet will be disappear/hidden from view, even when we use menu Format > Sheet > UnHide, the Worksheet will not be displayed in the list of Worksheets in hiding.
We can also get the same result using Excel VBA macro, like this:
Private Sub Workbook_Open()
Worksheets("Sheet1").Visible = xlSheetVeryHidden
End Sub
With a simple one line of code, Sheet1 will automatically set to VeryHidden each time the Excel Workbook was opened. Change “Sheet1″ with your Worksheet name.
FIN
Maybe for some reason we want to change our Microsoft Excel Status Bar, then this simple vba function will do the work for us.
The end result will look like this:
Ok, to do it, put this following code in ThisWorkbook code
Private Sub Workbook_Open()
'when workbook opened, change the status bar
Application.StatusBar = "Changing Excel Status Bar | excelvbamacro.com"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'remove status bar info before workbook closed
Application.StatusBar = False
End Sub
Now we can brag to our friends about our Excel status bar when we opened our Excel Workbook 
)
