Dynamic LastCell

[James Agostinho]

Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James

 

[Tom Ogilvy]

Sub LastCell()
Dim FirstCell As String
Dim LastCell As String

FirstCell = Range("A2").Offset(0, 3).Address
LastCell = Range("A2").Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

Names("MyRange").Value = "James"

End Sub

 

[Tom Ogilvy]

Let me try that again - several errors/omissions:

Sub LastCell()
Dim FirstCell As String
Dim LastCell As String

FirstCell = Range("A2").Offset(0, 3).Address
LastCell = Range("A2").End(xlDown).Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
"='" & ActiveSheet.Name & "'!" & FirstCell & ":" & LastCell '
Problem is here

Range("MyRange").Value = "James"

End Sub

 

[L. Howard Kittle]

Hi James,

Besides Tom's fix of your code, here are three other ways to do that.

Sub CellNamer()
Dim i As Integer
i = Range("A65000").End(xlUp).Row
Range("B2").Select
For i = 1 To i - 1
ActiveCell.Value = "James"
ActiveCell.Offset(1, 0).Select
Next
End Sub

Sub RangeNamer()
Dim LastRow As Long
Dim Drange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drange = Range("B2:B" & LastRow)
Drange.Value = "James"
End Sub

Sub TheResizeNamer()
Dim i As Integer
i = Cells(Rows.Count, "A").End(xlUp).Row - 1
Range("B2").Resize(i, 1).Value = "James"
End Sub

HTH
Regards,
Howard

 

[James Agostinho]

Gentlemen,
Thank you for the help, I knew there must be way, I played with this for
several hours and I couldn't get the syntax just right.
Anyway thanks
James