Learn Excel Blog

               

Paul has a column of names. Is there a function which will count the number of text entries in the column?

Yes, Paul, this is frustrating. I will bet you are a recent Lotus 1-2-3 user. In Lotus, of course, @COUNT(A1:A100) would work, but alas in Excel, the COUNT() function only counts numeric entries. These small changes are one of the frustrating challenges in switching from Lotus to Excel. It would be so much easier if Microsoft were to include a help topic entitled, “Really Minor Changes we Made to the Most Popular @Functions.”

The new function in Excel to count a column that contains text entries is

=COUNTA(A1:A100)

It’s not a personal vendetta against former Lotus users – merely an effort to improve the product. You can now count everything with =COUNTA() or just count numeric data with =COUNT()….

Hey – by the way – if you are switching from Lotus, the @VLOOKUP has been improved – you will have to add a 4th argument of FALSE to get the Excel VLOOKUP to work like the Lotus @VLOOKUP…..

               

Ketan writes I want to write an formula which calculates the max of length of the column values. For e.g. if we have a column

a

aa

aaaa

aa

aaaaaa

Then I want to insert a row at the top and formula should return the max of the length of the column values. In this case 5.

I know that this can be done by inserting a column and typing the formula =len(a1) then writing max(b1:b5), but when I have a worksheet containing large no. of columns, a single formula to calculate like this will be very helpful.

Yes, Ketan this formula would be very helpful. There are a whole class of super-formulas in Excel that can solve problems like this one in a single cell. Veteran MrExcel readers know this is my very favorite tip. It will move you to the front of the pack, ahead of 95% of other Excel users. You can read the complete details about CSE Formulas. Assuming your column of data is in A2:A2000, you would use this formula:

=MAX(LEN(A2:A2000))

You must hold down Ctrl, Shift and then hit Enter. I call these CSE formulas as a reminder that the magic key sequence is Ctrl + Shift while hitting Enter.

These formulas are very powerful and allow you to replace an entire column of formulas with a single formula.

               

Karthik asks: I am working on a event code for highlighting the active cell, basically show change the background color of the active cell. I used the change event and this changes the value of the active cell only when the value has changed.

What a cool question. The trick is to use the Worksheet_Selection change event instead of the Worksheet_Change event. This special event handler macro has to be entered on the code pane associated with your particular worksheet. A discussion of how to find this in the project pane is at this archived tip.

You would then enter the following code:

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

&nbsp&nbsp&nbsp&nbspIf Not OldRng Is Nothing Then

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspOldRng.Interior.ColorIndex = xlNone

&nbsp&nbsp&nbsp&nbspEnd If

&nbsp&nbsp&nbsp&nbspTarget.Interior.ColorIndex = 6

&nbsp&nbsp&nbsp&nbspSet OldRng = Target

End Sub

This program uses a public variable called OldRng to remember the last range that was selected so that the color can be reset back to white when you select a new range.

               

P.K. asks: I have a worksheet where I input some values. Upon clicking a macro button, I want the values or its calculated results to be appended to a different sheet or file.

Good question. Let’s say the values are in cells C10 and E12 of Sheet1 and you want them appended to the next row in Sheet2.

The real trick here is the code to find the next row on Sheet2. Starting at the last row in Sheet2 (A65536) and then using .End(xlup) will find the last row with data in column A. Adding 1 to that row will point you to the next row for data on Sheet2. Use this code:


Sub CopyThem()
NextRow = Worksheets("Sheet2").Range("A65536").end(xlup).Row +1
Worksheets("Sheet2").Cells(NextRow, 1).Resize(1, 2).value = Array( _
Worksheets("Sheet1").Range("C10").value, _
Worksheets("Sheet1").Range("E12").value)
End Sub

               

Charlie asks Is there a way in VBA to remove a worksheet which will bypass the “The selected sheets will be permanently deleted. Click OK” prompt”

This particular message is one that can be suppressed by using the Application.DisplayAlerts property. Use the following code:

Application.DisplayAlerts = False

Worksheets(“MySheet”).Delete

Application.DisplayAlerts = True

Learn Excel from MrExcel