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