Find Column Letter - When column header is found

[Kenny]

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)

 

[Gary''s Student]

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.

 

[Peter T]

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

 

[Dave Peterson]

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.)

 

[Don Guillett]

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

 

[JLGWhiz]

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]

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

 

[Kenny]

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]

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

 

[Dave Peterson]

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

The code searched for text in a header row.