I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

Click on AD65536 on the sheet and then pull down:

Insert Name Define kenny

So now kenny is the Name associated with cell AD65536

If you add or remove columns, the Name will adjust. Then in VBA:

Sub sistence()
lastRow = Range("kenny").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)
End Sub

Always consider using a Defined Name if you have to refer to a point of the
worksheet that has to move.
--
Gary''s Student - gsnu200800


"Kenny" wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

Not sure I'd ever name cells near the bottom of the sheet, only need to once
insert a row and the name is damaged (ref error)

OP, the idea is a good one but name the entire column, eg

Range("AD:AD").Name = "colAD"

dim rMyCol as range
Set rMyCol = Range("colAD")
lastrow = rMyCol(rMyCol.Parent.Rows.Count).End(xlUp).Row

If no need to cater for XL2007 simply 65536 in the brackets

Regards,
Peter T


"Gary''s Student" wrote in message
...
Click on AD65536 on the sheet and then pull down:

Insert Name Define kenny

So now kenny is the Name associated with cell AD65536

If you add or remove columns, the Name will adjust. Then in VBA:

Sub sistence()
lastRow = Range("kenny").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)
End Sub

Always consider using a Defined Name if you have to refer to a point of
the
worksheet that has to move.
--
Gary''s Student - gsnu200800


"Kenny" wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the
code
follow the column changes. I can only think that I would have to search
the
header row (2) for the specific text in that header to identify the
column
for the code. Not sure at all about how to go about this. Please help.
Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

dim wks as worksheet
dim LastRow as long
dim FoundCell as range
set wks = worksheets("somesheetnamehere")
with wks
with .rows(1)
set foundcellvar1 = .Cells.Find(What:="YourHeaderString", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with

if foundcell is nothing then
msgbox "header wasn't found, quitting!"
exit sub
end if

lastrow = .cells(.rows.count,foundcell.column).end(xlup).row
...

(Untested, uncompiled.)

Kenny wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!

lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

--

Dave Peterson

try this

Sub findlastrowinvarcol()
what = InputBox("Enter text to find in row 2")

mycol = rows("2").Find(what, After:=Cells(2, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Column
'MsgBox mycol
lastRow = Cells(rows.Count, mycol).End(xlUp).Row
'MsgBox lastRow
'lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kenny" wrote in message
...
I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the
code
follow the column changes. I can only think that I would have to search
the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help.
Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

I agree with GS and Peter that naming the column is best.

You only need to name the column once. Then you can find
the column number and assign it to a variable each time you
run the macro without ever having to change anything manually.

Sub oneTime()
Range("AD:AD").Name = "test"
End Sub

Sub everyTime()
Dim lr As Long, cl As Long
cl = Range("test").Column
lr = Sheets(1).Cells(Rows.Count, cl).End(xlUp).Row
MsgBox lr
End Sub


"Kenny" wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

Thanks all of you for replying. The last column is not the problem. Lets say
the named range "Account" is cell A3; how can I create an auto fill based on
that. For example:

tracker.Range("Account").AutoFill Destination:=Range("Account:Account" &
lastRow)

Of course this does not work. But basically I want this formula to equal
tracker.Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

But to be based on the named range so no matter when I add a column it will
follow this column based on the name ranged

Thanks




"Peter T" wrote:

Not sure I'd ever name cells near the bottom of the sheet, only need to once
insert a row and the name is damaged (ref error)

OP, the idea is a good one but name the entire column, eg

Range("AD:AD").Name = "colAD"

dim rMyCol as range
Set rMyCol = Range("colAD")
lastrow = rMyCol(rMyCol.Parent.Rows.Count).End(xlUp).Row

If no need to cater for XL2007 simply 65536 in the brackets

Regards,
Peter T


"Gary''s Student" wrote in message
...
Click on AD65536 on the sheet and then pull down:

Insert Name Define kenny

So now kenny is the Name associated with cell AD65536

If you add or remove columns, the Name will adjust. Then in VBA:

Sub sistence()
lastRow = Range("kenny").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)
End Sub

Always consider using a Defined Name if you have to refer to a point of
the
worksheet that has to move.
--
Gary''s Student - gsnu200800


"Kenny" wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the
code
follow the column changes. I can only think that I would have to search
the
header row (2) for the specific text in that header to identify the
column
for the code. Not sure at all about how to go about this. Please help.
Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

Thanks all of you for replying. The last column is not the problem. Lets say
the named range "Account" is cell A3; how can I create an auto fill based on
that. For example:

tracker.Range("Account").AutoFill Destination:=Range("Account:Account" &
lastRow)

Of course this does not work. But basically I want this formula to equal
tracker.Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

But to be based on the named range so no matter when I add a column it will
follow this column based on the name ranged

Thanks



"Dave Peterson" wrote:

dim wks as worksheet
dim LastRow as long
dim FoundCell as range
set wks = worksheets("somesheetnamehere")
with wks
with .rows(1)
set foundcellvar1 = .Cells.Find(What:="YourHeaderString", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with

if foundcell is nothing then
msgbox "header wasn't found, quitting!"
exit sub
end if

lastrow = .cells(.rows.count,foundcell.column).end(xlup).row
...

(Untested, uncompiled.)

Kenny wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!

lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

--

Dave Peterson

Did you try the examples, they all appear to answer the question in your OP
(a small type in Dave's, foundcellvar1 vs FoundCell)

If I follow, and I'm not sure I do, you are now asking a new question. Try
this -

Sub NameRanges()

Range("AD:AD").Name = "theColumn"
Range("A3").Name = "Account"
End Sub

Sub myAutoFill()
Dim lastRow As Long
Dim rMyCol As Range

Set rMyCol = Range("theColumn")

lastRow = rMyCol(rMyCol.Parent.Rows.Count).End(xlUp).Row

With Range("Account")
If lastRow <= .Row Then
MsgBox "LastRow not below cell to autofill"
Else
.AutoFill .Resize(lastRow - .Row + 1)
End If
End With

End Sub


Regards,
Peter T

"Kenny" wrote in message
...
Thanks all of you for replying. The last column is not the problem. Lets
say
the named range "Account" is cell A3; how can I create an auto fill based
on
that. For example:

tracker.Range("Account").AutoFill Destination:=Range("Account:Account" &
lastRow)

Of course this does not work. But basically I want this formula to equal
tracker.Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

But to be based on the named range so no matter when I add a column it
will
follow this column based on the name ranged

Thanks




"Peter T" wrote:

Not sure I'd ever name cells near the bottom of the sheet, only need to
once
insert a row and the name is damaged (ref error)

OP, the idea is a good one but name the entire column, eg

Range("AD:AD").Name = "colAD"

dim rMyCol as range
Set rMyCol = Range("colAD")
lastrow = rMyCol(rMyCol.Parent.Rows.Count).End(xlUp).Row

If no need to cater for XL2007 simply 65536 in the brackets

Regards,
Peter T


"Gary''s Student" wrote in
message
...
Click on AD65536 on the sheet and then pull down:

Insert Name Define kenny

So now kenny is the Name associated with cell AD65536

If you add or remove columns, the Name will adjust. Then in VBA:

Sub sistence()
lastRow = Range("kenny").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)
End Sub

Always consider using a Defined Name if you have to refer to a point of
the
worksheet that has to move.
--
Gary''s Student - gsnu200800


"Kenny" wrote:

I have the following code to fill range based on the first cell in
that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can
the
code
follow the column changes. I can only think that I would have to
search
the
header row (2) for the specific text in that header to identify the
column
for the code. Not sure at all about how to go about this. Please help.
Thanks!


lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

I didn't suggest using the last column for anything.

The code searched for text in a header row.

Kenny wrote:

Thanks all of you for replying. The last column is not the problem. Lets say
the named range "Account" is cell A3; how can I create an auto fill based on
that. For example:

tracker.Range("Account").AutoFill Destination:=Range("Account:Account" &
lastRow)

Of course this does not work. But basically I want this formula to equal
tracker.Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

But to be based on the named range so no matter when I add a column it will
follow this column based on the name ranged

Thanks

"Dave Peterson" wrote:

dim wks as worksheet
dim LastRow as long
dim FoundCell as range
set wks = worksheets("somesheetnamehere")
with wks
with .rows(1)
set foundcellvar1 = .Cells.Find(What:="YourHeaderString", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with

if foundcell is nothing then
msgbox "header wasn't found, quitting!"
exit sub
end if

lastrow = .cells(.rows.count,foundcell.column).end(xlup).row
...

(Untested, uncompiled.)

Kenny wrote:

I have the following code to fill range based on the first cell in that
range. However, I add and remove colums frequentley, this causes me to
constantley change the column references in the below code. How can the code
follow the column changes. I can only think that I would have to search the
header row (2) for the specific text in that header to identify the column
for the code. Not sure at all about how to go about this. Please help. Thanks!

lastRow = Range("AD65536").End(xlUp).Row
Range("A3").AutoFill Destination:=Range("A3:A" & lastRow)

--

Dave Peterson


--

Dave Peterson