Excel automatic row numbering

Automatic row numbering is quite straight forward.

Just type two consecutive numbers, even alphanumeric combination.

Excel is smart enough to auto number or makes a pattern sequence to insert values on the rows.

If there are hidden rows in excel and desired output is not to include hidden rows as part of auto numbering, then this method will not work.

Then one option of course is to manually type the number or the values.

But it would be much better to do it on one click.

If you need a time waster then do it manually, but if urgency is required VBA will be a good option to automate the process.

If there are hidden rows in excel, it will auto number but the hidden rows will also be included on the count.

Link below Microsoft provided a VBA code on how to detect hidden rows.

http://support.microsoft.com/kb/150363/en-us

To tweak the code just a little bit then auto numbering with hidden rows will not be an issue.

How the code works, it will check for highlighted cells and check the hidden rows in the highlighted cells.

Use the VBA code by highlighting the desired cells and run the macro.

Excel will not put values on the hidden rows, thus the number will be in sequence on the rows that are not hidden.

Here's the re-used VBA code from Microsoft website to auto number not including hidden rows.

Sub xhide_autorow_number()

For Each cell In Selection

           If cell.Rows.Hidden = False Then

               If cell.Columns.Hidden = False Then                   

                   count = count + 1

                   cell.Value = count

               End If

           End If

       Next

MsgBox count & " item(s) selected" & vbCrLf & "Auto Numbering Done"

End Sub