Find Range & Fill Down

[YellowLabPro]

I am trying something different. I have a sheet w/ data that I want to append to the bottom. I can accomplish all of this except one column.
I need to find the last row of the target column and fill down to the last row of the last used cell in Column "Y".
The last used row in col. "Y" is determined by variable lLrwT.
The last used row in col. "AB" is determined by rows.count.

I have been experimenting w/ .cells and .range but have not come up w/ the proper way to refer to this range. I recognize that certain things may not be combined, Range("AB" & Rows.Count) and Cells(rows.Count, "AB").row to create a range. But it seems logical to me, that it should be possible to find the last row in one column, another last used row in another column and create a range from that..... to fill down. Here are a couple of lines I have been working w/.

[VBA].Range(.Range("AB" & Rows.Count).End(xlUp).Row : .Range("AB" & lLrwT).Row)).FillDown[/VBA]

[VBA].Range("AB" & Rows.Count).End(xlUp).Row : .Range("AB" & lLrwT).Row).FillDown[/VBA]

I would post more, but Excel just locked up on me and I cannot get back into the code window.

[YellowLabPro]

Here is a more simplified version- still not working, Method of Range error

[vba] .Range("F" & lLrwT + 1, "F").FillDown
[/vba]

or

[VBA]
.Range("F" & lLrwT + 1: "F").FillDown
[/VBA]

[lucas]

I'm not sure exactly what you are trying to do. Are you looking at one column and putting a formula in the cell next to the top entry and filling down?

[YellowLabPro]

Give me a minute Steve.....

[Norie]

Doug

Is it a formula you are trying to insert?

[YellowLabPro]

I had just posted this to OzGrid before I could get back here and list it here, sorry for not getting it up sooner.
http://www.ozgrid.com/forum/showthre...d=1#post388864

What I am trying to do is append data to the end of my sheet, it is not contiguous columns and they have to be filled in on a certain order, leaving 3 columns w/ formulas in the above region to be copied down.

Shg on OzGrid has given me the first part,
[VBA].Range(.Cells(lLrwT, "G"), .Cells(lLrwT, "G").End(xlDown)).FillDown[/VBA]

But I am having a little difficulty getting it to deliver exactly. I will post back.

Thnks again, sorry for the delay in listing the cross-post up here.

[YellowLabPro]

I am going to end my posts here on this question for now to not cause any confusion. I am going to pursue it on OzGrid. I will post back. Sorry again for any trouble...

[Norie]

Doug

That's one of the main reasons not to cross-post in the first place, to avoid confusion.

[malik641]

I am trying something different. I have a sheet w/ data that I want to append to the bottom. I can accomplish all of this except one column.
I need to find the last row of the target column and fill down to the last row of the last used cell in Column "Y".
The last used row in col. "Y" is determined by variable lLrwT.
The last used row in col. "AB" is determined by rows.count.

I believe Malcolm came up with this code for you:
[vba]Function LR(ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function[/vba]
With this function in mind, while you are using this to work with your columns, when you get to column "Y", do what you need to do with that column, and call the function on "Y" again so you can get the last row in column "Y" AFTER you add/delete/modify data to it. You can store this last row into a variable (of type Long). Then when you get to column "AB", you can check to see if the last row of "AB" is LESS than column "Y"'s last row, then base your FillDown method on that.

Check this out:
[vba]Public Sub TestLastRowFunction()
Dim lngLastRow As Long
Dim lngCol As Long
Dim lngLastRowColumnY As Long

' Loop from column Y to column AA
For lngCol = (Columns("Y").Column()) To (Columns("AA").Column())
lngLastRow = LR(ActiveSheet, lngCol)
' Do some data appending/deleting
' ......

' Now check if Column Y was just edited
' If yes, store the last row into a new variable
If lngCol = (Columns("Y").Column()) Then
lngLastRowColumnY = LR(ActiveSheet, lngCol)
End If
Next

' Now perform data FillDown method on column AB
' First check to see if column AB's last row
' is less than Y's last row
lngLastRow = LR(ActiveSheet, "AB")
If lngLastRowColumnY > lngLastRow Then
' FillDown
Range("AB" & lngLastRow, "AB" & lngLastRowColumnY).FillDown
End If

End Sub

Function LR(ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function[/vba]

[malik641]

I guess:
[vba]' Now check if Column Y was just edited
' If yes, store the last row into a new variable
If lngCol = (Columns("Y").Column()) Then
lngLastRowColumnY = LR(ActiveSheet, lngCol)
End If[/vba] Would be better OUTSIDE the loop...but eh..it's just for demonstration purposes anyway