Fill numbers

[claudehollett]

I neet to number lines in col B begining in row 4 with 1 to an unknown number FinalRow. I usually specify the Range using something like: Range(Cells(4,2),Cells(FinalRow-3,2)) but I can't get this to work in this case. What can I do? Claude

 

[jindon]

Hi
Does this work for you?

Sub test()
With Activesheet
     With Intersect(.Range("b4:b" & Rows.Count),.UsedRange).Offset(,-1)
           .Formula = "Row()-3"
           .Value = Value
     End With
End With
End Sub

 

[claudehollett]

This runs through without error but seems to have no effect. No numbers are entered into col B.

 

[jindon]

It is hard without knowing your sheet layout

How about

Sub test()
Dim LastR As Long
With ActiveSheet
      LastR = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
      With .Range("b4:b" & LastR)
             .Formula = "Row()-3"
             .Value = .Value
      End With
End With
End Sub

 

[claudehollett]

This code sorts my page from A4:AI38. My line or row numbers are in col B and are also sorted out of order. What I want to do is sort Col B from B4 to FinalRow or to use autofill to just fill in the numbers or any solution which will leave column B numbered from 1 to FinalRow - 3.
This is my code:

Sub Sort()
'
' Sort Macro
ActiveSheet.Unprotect
Dim FinalRow
FinalRow = Range("A65536").End(xlUp).Row

'Sort Location Names
Range(Cells(4, 1), Cells(FinalRow - 3, 35)).Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'It works down to here============================

'Sort Row Numbers in col B
Range(Cells(4, 2), Cells(FinalRow - 3, 2)).Select

On Error Resume Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[jindon]

I neet to number lines in col B begining in row 4 with 1 to an unknown number FinalRow. I usually specify the Range using something like: Range(Cells(4,2),Cells(FinalRow-3,2)) but I can't get this to work in this case. What can I do? Claude

What do you need to know?

 

[claudehollett]

This code does exactly what I want except I need to be able to Set fillRange with a range that is based on FinalRow-3 not hardcoded as B38.

Sub fill()
'
' fill Macro
' Macro recorded 9/12/2006 by Claude
'
ActiveSheet.Unprotect
Dim FinalRow
Dim sourceRange As Range
Dim fillRange As Range
FinalRow = Range("A65536").End(xlUp).Row
Application.EnableEvents = False
Range("B4") = "1"
Range("B5") = "2"
Set sourceRange = ActiveSheet.Range("B4:B5")
Set fillRange = ActiveSheet.Range("B4:B38")
Range("B4:B5").Select
Selection.AutoFill Destination:=fillRange
Range("A4").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
End Sub

 

[jindon]

If you want to know the last cell in Col.A then

you can just change the line to

Set fillRange = ActiveSheet.Range("b4:b" & FinalRow - 3)

However you need an error trap in the case of FinalRow < 3

 

[claudehollett]

That line gives an application defined or object defined error. It is similar to what I've been trying and looks like it should work, but always comes back with an error.

I really appreciate your help. You are so kind to reach out from across the world and help someone like me. Thanks again.

NOW LETS GET IT RIGHT!

 

[jindon]

Ok

Check the value of FinalRow,

I'm afraid it may less than 4