Filling an Autonumber 1-whatever down on a Sheet Programmatically

[DoctorV]

I have a Microsoft Query that retieves data from an Access Query an
puts it into an Excel Worksheet named AllData starting at cell B2.
have a sub named FormatDates that formats various column
appropriately. What I need to do is add to this sub a procedur
starting at cell a2 going down to the last row of data and filling
number starting with 1 to howver many numbers of rows there are to thi
last row

Just like edit Fiil down when you have the number 1 and 2 highlighte
and you fill down the range to the last row of data.

Thanks Here is my sub below

Sub FormatDates()
Sheets(Array("AllData")).Select
Sheets("AllData").Activate
Columns("o:q").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ShrinkToFit = True
.NumberFormat = "m/d/yyyy"
End With
Columns("r:r").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ShrinkToFit = True
.NumberFormat = "m/yyyy"
End With
Sheets("AllData").Select
Range("A2").Select
'Sheets("MainForm").Select
'Selection.Sort Key1:=Range("N2"), Order1:=xlAscending
Header:=xlGuess, _
'OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Sort Key1:=Range("d2"), Order1:=xlAscending
Key2:=Range("c2") _
, Order2:=xlAscending, Key3:=Range("b2"), Order1:=xlAscending
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveWindow.SmallScroll ToRight:=4
Columns("m:m").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=4
Columns("j:j").Select
Selection.EntireColumn.Hidden = True
Rows("1:1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.Select
Selection.Rows.AutoFit
End Su

 

[DoctorV]

Tried using loop at end of the sub but nothing is happening

Dim i As Integer
i = 2

Do While (Not IsNull(Cells(i, "B")) And Cells(i, "B") <> "")
Cells(i, "A") = i - 1
i = i + 1
Loo

 

[Dave Peterson]

First, you do a lot of selecting in your existing code. And how can you tell
the lastrow? Can you pick out a column that always has entries? If yes, then
this example may work for you. (I used column B--like your example).

I think (not tested against real data) that this does the same thing:

Option Explicit
Sub FormatDates()

Dim LastRow As Long

With Sheets("AllData")
With .Columns("o:r")
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ShrinkToFit = True
.NumberFormat = "m/d/yyyy"
End With

.UsedRange.Sort _
Key1:=.Range("d2"), Order1:=xlAscending, _
Key2:=.Range("c2"), Order2:=xlAscending, _
Key3:=.Range("b2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

.Range("j1,m1").EntireColumn.Hidden = True

With .Rows(1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("a2:a" & LastRow)
.Formula = "=row()-1"
.Value = .Value
End With

.Cells.Rows.AutoFit
End With
End Sub

the one portion of your code that scared me is relying on xl to guess if you had
headers. I'd wouldn't do that. If I have headers, I say xlyes. If I don't, I
use xlno.

(and you had a small bug--order1 was used twice--order3 wasn't used at all.)

This is my scary portion:

.UsedRange.Sort _
Key1:=.Range("d2"), Order1:=xlAscending, _
Key2:=.Range("c2"), Order2:=xlAscending, _
Key3:=.Range("b2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

I assumed that you only had one header row (row 1). You may have to adjust that
to sort starting with the correct row.