Sequence Numbers and Hidden Rows in Excel 1

[tallbarb]

Looking for suggestions on how to create a column with sequence numbers that "skip" hidden rows. For instance, in column A, rows 10 through 30 are sequentially numbered 1 through 20. Then, I hide row 20. I'd like rows 21 through 30 to resequence so they show 20 through 29. I'd like this requencing to happen automatically so, if rows are unhidden, the numbers resquence and if any rows are hidden either singularly or as a range or any combination thereof, the numbers resequence. Probably means some coding but I am pretty clueless when it comes to VBA. Any help would be greatly appreciated. This forum and its participants are fabulous!

 

[SkipVought]

Hi,

I only see a VBA solution...

i = 1
r = 10
Do While i <= 30
  If Not Cells(r, 1).EntireRow.Hidden Then
     Cells(r, 1).Value = i
     i = i + 1
  End If
  r = r + 1
Loop

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

You can make it semi-automatic by performing the renumbering on a worksheet event:
[blue]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim c As Range
Dim nRow As Long
  For Each c In Range("A10:A300")
    If c.EntireRow.Hidden = False Then
      nRow = nRow + 1
      c = nRow
    End If
  Next c
End Sub

[/color]
Change the range specification to match the number of rows you need to be affected. (Also the column letter if your numbers aren't in column "A&quot

It is only "semi-automatic" in that it will renumber your rows after clicking in some cell after hiding/unhiding your rows.

 

[mscallisto]

I agree with Skip and Zathras that it seems coding is probably the only way out.

But...just in case you didn't already know there is a simple way to keep numbering in sequence if you wish to add or delete rows...just not HIDE then as you wish to do.

formula =INDEX(ROW(),1,1)in the empty cell you wish to number then drag this up/down to where you wish to end.

Deleting a row will auto update.
Adding a row will require dragging the formula to the new row.

Someone out there may know how to add to this formula to make it work for hidden rows.