VBA - last cell in column

[Wookie]

What I would like is a bit of code that would look down Column A in my worksheets (Debit by Co&Vend from the code below) and give the answer of the last cell in that column with values in it. The answer to the first part would then be used to replace Selection.AutoFill Destination:=Range("B2:D5000")
with Selection.AutoFill Destination:=Range("B2:whatever last cell is").

At the moment the code i have is not looking for the last cell as i have chosen a range that is big enough to make sure i dont miss entries.

Sheets("Debit by Co&Vend").Select
Range("B2:D2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:D5000")
Range("B2:D5000").Select
Columns("A:D").Select
Selection.Copy

 

[Richard Schollar]

Hi

Something like:

Dim lngLastRow As Long
 
lngLastRow = Cells(Rows.Count,"A").End(xlUp).Row

Then you have your last row, and can use it to define your range.

 

[Norie]

Something like this?

Dim LastRow As Long
With Sheets("Debit by Co&Vend")
    LastRow = .Range("A" & Rows.Count).End(xlUp.Row)
    .Range("B2:D2").Copy .Range("B2:D2").Resize(LastRow - 1)
End With

 

[Wookie]

Sorry for my ignorance but how do i go about putting this into the code, what should i take out of what i posted etc.

 

[Norie]

Which code are you referring to?

If it's what I posted just replace all the code you posted with what I posted.

 

[Wookie]

Cheers Norie, been a long day.

 

[jakeb3482]

use this below:

Function find_in_col(search_term, col) As Integer
counter = 0
Do
counter = counter + 1
Loop While Cells(counter, col) <> search_term
find_in_col = counter

End Function

search term will be ""
col will be the column you are working in.

so rowx = find_in_col("",1)
this will give you the row number of the last column