Thursday, August 17, 2006
Change Row and Column Headers
You cannot change the default Row (1,2,3,...) and Column (A,B,C,D....) headers, but a workaround is:
1. Enter what you'd like to be your Row Headers in the first column after cell A1
2. Enter what you'd like to be your Column Headers in the first row after cell A1
3. Format first row and column to match the default headers
4. Go to Tools>Options>View Tab>Clear the Row & column Headers check box.
Now, your sheet might look like this:
1. Enter what you'd like to be your Row Headers in the first column after cell A1
2. Enter what you'd like to be your Column Headers in the first row after cell A1
3. Format first row and column to match the default headers
4. Go to Tools>Options>View Tab>Clear the Row & column Headers check box.
Now, your sheet might look like this:
Friday, August 11, 2006
Assign macro to a button
In this short video you can learn how to assign a macro to a button on your toolbar. If this macro is in the personal workbook then you can use it on any workbook.
Monday, August 07, 2006
Count cells without formulae
If you want to count cells in a given range without formulae use this function
If you want to count cells with formule then just remove then "Not" in front of c.HasFormula
Function CountNoFormula(rng As Range) As Long
Dim c As Range
On Error GoTo CountNoFormula_Error
For Each c In rng
If Not c.HasFormula Then
CountNoFormula = CountNoFormula + 1
End If
Next c
CountNoFormula_CleanUpExit:
Exit Function
CountNoFormula_Error:
CountNoFormula = CVErr(Err.Number)
Resume CountNoFormula_CleanUpExit
End Function
Dim c As Range
On Error GoTo CountNoFormula_Error
For Each c In rng
If Not c.HasFormula Then
CountNoFormula = CountNoFormula + 1
End If
Next c
CountNoFormula_CleanUpExit:
Exit Function
CountNoFormula_Error:
CountNoFormula = CVErr(Err.Number)
Resume CountNoFormula_CleanUpExit
End Function
If you want to count cells with formule then just remove then "Not" in front of c.HasFormula
