Macro for pasting a sequence of numbers to many rows in one column

FreshDK

New Member
Joined
Feb 21, 2017
Messages
10
Hi,

I have a sheet with 10.000 rows of data:

aaa111
aaa222
bbb111
ccc111
ccc222
ccc333
......

Sometimes I need to filter and sort the rows to view only certain rows in a specific order. But I need index numbers to be able to get back to the original order, so i do like this:

1aaa111
2aaa222
3bbb111
4ccc111
5ccc222
6ccc333
...10.000......

My problem is when I add new rows to the sheet, it is never in the bottom, but always at a specific place. Then I mess up the index numbers because they need to be "dead" and not generated by a formula not to mess up when i do filter and sorting:

1aaa111
2aaa222
3bbb111
bbb222
4ccc111
5ccc222
6ccc333
...10.000......


Right now I have this neat macro to add line below (with formulas) when i double click inside my sheet:

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

My wish is to add to this macro so that it "updates" my index numbers (pastes a new sequence of numbers from A1 to A100000) after inserting a new row. Right now it's very fragile if i don't remember to update index numbers before the occasional filter and sorting, then i cannot go back without messing up my dataset.

I have tried to google, but I cannot seem to find what I'm looking for.

Br.

FreshDK
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

FreshDK

New Member
Joined
Feb 21, 2017
Messages
10
I found a workaround macro to copy a range from a different sheet. It works but it is not elegant!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,657
Messages
5,573,445
Members
412,529
Latest member
cTatch
Top