VBA: Identify Last Column with data in a specific Range and copy to next column

[goobers]

Hi everybody. I'm having a frustrating time with my latest project, but I know the answer is probably a lot simpler than I am trying to make it.

In the file I have uploaded, I want to be able to:

1) Identify the Last Column that contains Data, but only for the range starting with Row 9.

2) I then want to Copy the info from the last column with data (in my spreadsheet, that would be $D9:$D65536) into the next column to the Right (data would be copied into E9)

I have found plenty of VBA expressions that will identify the last row or last column, but for the life of me I can't find the code to search only for a specific range.

As always, thanks for all your help.

[Bob Phillips]

Dim lastrow

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D9").Resize(lastrow - 8).Copy Range("E9")

[goobers]

XLD, thanks for the quick response.

I forgot to mention that this macro will need to be run every quarter (possibly monthly). So, I can't statically select Range("D9") and Copy Range("E9"). Each time I run it, the Last Column with data is always going to be different.

[Bob Phillips]

Dim LastRow As Long
Dim LastCol As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells(9, Columns.Count).End(xlToLeft).Column

Cells(9, LastCol).Resize(LastRow - 8).Copy Cells(9, LastCol + 1)