Simple Macro Question

[Guest]

Hello, I have created a movie database of movies I own, column A is the movie
number, while column b is the movie title.

I have set up a macro to first sort the data by movie title, and then
re-number (fill) the A column with the appropriate number.

Problem is, I'm not very good with visual basic, and I think if I use an
'if' statement somewhere (i'm good with php hence the logic) it will only
fill in column A (the number) if column B (the movie) is present, or has a
title enered.

Here is the VB code for the Macro, I have it set right now to stop at 316,
because this is where page 7 would break, but I'm sure there is a way to do
it so I won't have to keep updating this macro, I just need one of you pros
to tell me how.

My current vb code:

Sub alphnumbers()
'
' alphnumbers Macro
' Macro recorded 5/24/2005 by Jason
'
' Keyboard Shortcut: Ctrl+s
'
Columns("B:B").Select
Range("A1:E65526").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2:A4").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Selection.AutoFill Destination:=Range("A2:A316"), Type:=xlFillDefault
Range("A2:A316").Select
ActiveWindow.SmallScroll Down:=-264
Range("A2").Select
End Sub

As you can probably see, I did this via the record function, as I am new to
this, but if there is a more efficient way please let me know. Right now I
only have about 300 movies, so I am getting 15 lines with 301-315 listed
without titles. 8(

 

[Bob Phillips]

Is this what you mean?

Sub alphnumbers()
'
' alphnumbers Macro
' Macro recorded 5/24/2005 by Jason
'
' Keyboard Shortcut: Ctrl+s
'
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:E" & iLastRow).Sort Key1:=Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2").Value = 1
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("A2:A" & iLastRow)
.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, _
Date:=xlDay, _
Step:=1, _
Trend:=False
End With
End Sub

 

[Guest]

Bob, that was exactly what I was looking for, thank you so much! 8)

 

[Bob Phillips]

Great, I goy confused by the Autofill in your code, so I just got rid of it


Bob

 

[Guest]

If I knew how to do it without the fill code in there I would have, but your
code is flawless for what I am doing, thanks again!