Problem Are you frustrated that you have to concatenate a big range of cells in Excel, but you have to do so using CONCATENATE function by entering each cell and typing a comma after every cell? Well, here’s a solution to it. A procedure or a function whatever you like. If you use the procedure, […]
Continue readingIf you want to count cells in a given range without formulae use this function Function CountNoFormula(rng As Range) As LongDim c As Range On Error GoTo CountNoFormula_Error For Each c In rng If Not c.HasFormula Then CountNoFormula = CountNoFormula + 1 End If Next cCountNoFormula_CleanUpExit: Exit FunctionCountNoFormula_Error: CountNoFormula = CVErr(Err.Number) Resume CountNoFormula_CleanUpExitEnd Function If you want to count cells with formule […]
Continue readingThis about the problem of cells which appear to be blank and which are true blanks. If you check a cell using IsBlank() formula, you’d know if it’s a true blank or not. Even if you copy paste special (values) this =””, there wouldn’t be anything in the cell and length of that cell would […]
Continue readingTo flip the given row as shown in this figure, use the following macro Result row You can use the same macro for flipping columns too, code will find if it’s a row or a column. Note: This code was modified on 07/26/07 for error checking, and removal of Option Base Sub flip() Dim Arr […]
Continue readingCheck the following link, it provides limitations of using array formulas in Excel. Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003 Note The following are helpful facts to remember about the functions. ? If any cells in an array are empty or contain text, MINVERSE returns the […]
Continue reading