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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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!
 

Forum statistics

Threads
1,141,011
Messages
5,703,722
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top