Macro to number each row

[sam99car]

Hello,

I am not sure if "ranking" is the proper term, but I am trying to write a macro that will number each row (A7=1,A8=2,A9=3,etc).

For example, the data always starts at A7 but could end at A100 or A600-it varies. This would be applied to each worksheet for each workbook. (The number of worksheets in each workbook also varies.)

Thanks in advance.

-sam.

[Asala42]

Hey,

I'm just getting used to VBA but how about something similar to:

Sub numberrows()
Dim x As Integer
Dim MaxRow As Integer

x = 7
MaxRow = 600

For Each sht In Worksheets
    Do While x < MaxRow
        sht.Activate
        Range("A" & x).Value = x - 6
        x = x + 1
    Loop
x = 7
Next sht

End Sub

In this setup, I have you manually assign which row to start (row 7) as well as the final row (600).

Hope that helps,
Adam

[ This Message was edited by: Asala42 on 2002-05-22 11:43 ]

[Barrie Davidson]

On 2002-05-22 11:28, sam99car wrote:
Hello,

I am not sure if "ranking" is the proper term, but I am trying to write a macro that will number each row (A7=1,A8=2,A9=3,etc).

For example, the data always starts at A7 but could end at A100 or A600-it varies. This would be applied to each worksheet for each workbook. (The number of worksheets in each workbook also varies.)

Thanks in advance.

-sam.

Sam, you can also do this via a formula. Try this and copy down:

=IF(ISBLANK(A7),"",ROW(A7)-6)

This will put an empty string in the cell if A7 is blank.

Hope this helps you out.

Regards,

[Nimrod]

I thought Asala codes was great . However it sounded like you wanted this to occur in ALL workbooks that were open ... if thats the case then this modification of the code will take care of that situation.

Sub numberrows()
For Each WkBk In Workbooks
    For Each sht In Worksheets
        For x = 7 To 600
            sht.Range("A" & x).Value = x - 6
        Next
    Next sht
Next WkBk
End Sub

[ This Message was edited by: Nimrod on 2002-05-22 12:07 ]

[James]

Hi Sam99Car,

Heres what i use:

This numbers all rows that are populated
Starts in "A6" (adjust to suit)

Private Sub CommandButton1_Click()
Dim ColA As Range, Rw As Range, n%
Set ColA = Range("A6")
Set rw = ColA.EntireRow
Do Until Application.Worksheet Function.CountA(rw)= 0
n = n + 1
ColA.Value = n
Set ColA = ColA.Offset(1, 0)
Set rw = rw.Offset(1, 0)
Loop
End Sub

Regards,
James

[sam99car]

Thank you ALL.

Asala and Nimrod's code is what I am looking for.

-sam.