auto numbering

Eusebious · Aug 24th 2021

I have created a table that automatically numbers the row as I enter new info, but occasionally I have to delete a row, is there a way to continue the numbering? the table does not like gaps.

**royUK** · Aug 24th 2021

How are you autonumbering?

What is the point of the number if you delete rows then the number sequence will be broken.

Eusebious · Aug 24th 2021

Hi Roy

I use the following formula to auto number rows =IF(B4="","",A3+1) this is part of a sheet to record visits to Costa Coffee shops. I then do =max(A3:A1000) to see how many I have visited. Occasionally a shop will close so I delete the row.
Eusebious

**royUK** · Aug 24th 2021

I'm sure that I worked on this previously using COUNTIFS to get the number of visits.

Eusebious · Aug 24th 2021

Thanks I'll give it a go

E

Eusebious · Aug 24th 2021

Something else you may be able to help me with is my list consists partly of a download of locations, but each place is preceded by 3 numbers and a fullstop. Is it possible to remove the numbers and capitalise the first letter of the place name. As in.

132. caerphilly to Caerphilly

133. calne to Calne

134. camberley to Camberley

135. cambridge to Cambridge

136. camden town to Camden Town

Sorry to bother you

**royUK** · Aug 25th 2021

Attach an example workbook

Eusebious · Sep 6th 2021

I have downloaded a long list of place names for my table, but they are preceded by a number, see below

132. caerphilly

133. calne

134. camberley

is there a quick way of removing the number and full stop and capitalising the first letter of the place name?
Thanks very much for the previous help.
TonyH

**royUK** · Sep 6th 2021

If your list starts in A1 of the active sheet then try this

Code

Sub CleanUp()
    Dim rCl As Range, rRng As Range
    With ActiveSheet
        Set rRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        For Each rCl In rRng
            rCl.Value = Application.WorksheetFunction.Proper(Right(rCl.Value, Len(rCl.Value) - 5))
        Next rCl
    End With
End Sub

Eusebious · Sep 6th 2021

I'll give it a try, thanks.

Eusebious · Sep 6th 2021

sorry to be a pain but this is my first attempt at code?

The range I need to fix is A233:A878

how do I change your code to suit

TonyH

**royUK** · Sep 6th 2021

Try this, you must make sure that you have selected the sheet with names on.

Code

Sub CleanUp()
    Dim rCl As Range, rRng As Range
    With ActiveSheet
        Set rRng = .Range("A233:A878")
        For Each rCl In rRng
            rCl.Value = Application.WorksheetFunction.Proper(Right(rCl.Value, Len(rCl.Value) - 5))
        Next rCl
    End With
End Sub

Eusebious · Sep 6th 2021

Cheers, cheques in the post

TonyH

auto numbering

Participate now!

Share

Tags