Finding last row/cell

[Thom]

Hi

I use a query with a sort to populate a number of sheets. this is works fine.


The data held is addresses and whatnot so each sheet is a city.

I need a sheet with every address on it which I have but I need to sort it.

So I need to sort column A, starting at cell A4. I need to find the last cell used in column A and do a sort.

I have this:

Dim LastRow As Long
With XLSheet
LastRow = .Range("A4").SpecialCells(xlCellTypeLastCell).Column
End With


Selection.Sort Key1:=Range("A4:LastRow"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
But it doesn't like it.

[Thom]

Never mind, this is what I have, and it works:

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

Range("A4:A" & LastRow).Select

Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[Thom]

Ok I'm at another stumbling point.

The above worked fine for that issue.

Now though I want to draw a border along the bottom of each sheet. Each sheet has a different number of rows. I tried

With XLSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Range("A1:A" & LastRow).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

But it puts the border 5 cells under the last used row/cell.

[JimmyTheHand]

You might have a SPACE in the last cell this method finds. Or a formula returning nullstring.
Otherwise, your code looks OK, provided that XLSheet is the same sheet as the one the macro runs on.

BTW, this thread should have been started in the Excel Help forum.

Jimmy