CODEWITHSUNDEEP
excelvba
heavyarms
heavyarms

Reputation: 155

.End(xlToRight) within a range

I've just got a quick one wondering if I can somehow alter the following snippet to include .End(xlToRight) instead of defining L6 (the result is the same).

Sub Test()
Dim LastCol As String

With Worksheets("Sheet1")
    LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address
    .Range(Range("A5"), LastCol).Copy    

    .Range("B5:L5", Range("B5:L5").Offset(LastRow - FirstRow, 0)).PasteSpecial xlPasteFormulas

    .Range("B6", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
    .Range("B6").PasteSpecial xlPasteValues
End With
End Sub

Many thanks for any help :)

EDIT: updated pdw TonyDallimore (see response below)

Tony, the above code is the sum of what I've been trying to get working. Within the with statement, I'm looking to copy the contents of row 5, and paste them down to the nth row - which is defined by a list already present in columnA. The last line per your advice will then paste the values of all but the top row (r5) to preserve transparency for the user, while minimising file size. The middle bit is the remaining 'work in progress', as L5 is not certain to be the farmost column.

Upvotes: 1

Views: 60047

Answers (1)

Tony Dallimore
Tony Dallimore

Reputation: 12403

Both questions

.End(xxx) is the same as Ctrl+Arrow from the keyboard and stops at the first cell that is different from the current cell. So if you start at a cell with a value it stops at a cell without a value or vice versa.

The trouble with starting top left and using xlDown and xlToRight is that it will stop at a blank cell in the middle of your table. Fine if you absolutely cannot have a blank cell in the middle, but XlUp or xlToLeft from bottom right are safer.

Question 1

Is your problem that .End(xxx).Column will return 12 and you do not know how to turn it into the letter "L"?

Is so, there are lots of choices. I think the easiest is to remember that

.Cells(6,Columns.Count).End(xlToLeft).Address

would return "$L$6".

Question 2

Does .Cells(1000, ColRange) represent the bottom right cell?

.Cells.SpecialCells(xlCellTypeLastCell) might be an easier option.

Upvotes: 5

Related Questions