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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,765
Messages
5,574,105
Members
412,571
Latest member
Ventura7
Top