Re-number column from 1

**chrisellis250** · 11-23-2021, 01:26 PM

Hi all,

I am trying to renumber a column in my worksheet from 1 downwards (i.e. 1 to the last cell in column A) I have tried with the code below which clears the contents in Column A, puts a 1 in A10 and then does a flashfill:

Sub Procedure1()

Sheets("Co-location").Select
Range("A10:A1048576").ClearContents
Range("A10").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A10").Select
    Range("$A$10").FlashFill
    
End Sub

But it does not number then sequentially, i.e. 1 2 3 4 5 6 7 and so on

I have attached a workbook to show how my data looks etc

Any ideas why this is not working?

Many thanks,

Chris

**Bo_Ry** · 11-23-2021, 01:48 PM

Please try

PHP Code:


Sub Fills() 
 
With Sheets("Co-location").[a10] 
    .Value = 1 
    .AutoFill .Resize(.CurrentRegion.Rows.Count - 1), 2 
End With 
 
End Sub

**WideBoyDixon** · 11-23-2021, 04:52 PM

Alternative:

Public Sub RenumberColumn()

With Sheets("Co-location")
    With .Range(.Range("A10"), .Cells(.Rows.Count, "A").End(xlUp))
        .Formula = "=ROW()-9"
        .Value = .Value
    End With
End With

End Sub

WBD

**sintek** · 11-24-2021, 05:21 AM

With Range("A10:A" & Cells(Rows.Count, 2).End(xlUp).Row)
    .Value = Evaluate("=Row(" & .Address & ")-9")
End With

Re-number column from 1

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re-number column from 1

Re: Re-number column from 1

Re: Re-number column from 1

Re: Re-number column from 1

Thread Information

Users Browsing this Thread

Similar Threads

One column reads number from another column and base on number of days highlights

[SOLVED] Search for number in Column A, then upload image in next column if number does not exist.

[SOLVED] If number in Column A matches number in column B - Copy B cell and adjacent cells ?

Fill sequential number down empty column, save last number, use as first number next time

Find highest revision number in one column of item number in another column and add total

Find highest revision number in one column of a item number in another column

Lookup largest number in a column treating negative number as postive number

Bookmarks

Bookmarks

Posting Permissions