Fill numbers

claudehollett

Board Regular
Joined
Dec 11, 2003
Messages
89
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Does this work for you?
Code:
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

Board Regular
Joined
Dec 11, 2003
Messages
89
This runs through without error but seems to have no effect. No numbers are entered into col B.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
It is hard without knowing your sheet layout

How about

Code:
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

Board Regular
Joined
Dec 11, 2003
Messages
89

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Dec 11, 2003
Messages
89

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Dec 11, 2003
Messages
89
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,706
Members
410,630
Latest member
Maggie28
Top