Finding last row with data

[AlexanderBB]

Having a bit of trouble... trying

Sub Go()
Dim x As Range
With Worksheets("A")
Debug.Print .Cells(1, "A")  'this works
Set x = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Stop
Debug.Print .Range("A1").CurrentRegion.Rows.Count

End With
End Sub

X becomes an unprintable character and last debug.print is 1. Last row is actually 3210.
What am i doing wrong?

 

[SanjayGMusafir]

Set x = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

I'm not expert at VBA but I think it should be

Set x = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

This way it will first search down and then move to next column thus giving the right row number

 

[Akuini]

I added 2 debug.print lines:

Sub Go_1()
Dim x As Range
With Worksheets("A")
Debug.Print .Cells(1, "A")  'this works
Set x = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Debug.Print x.Address
Debug.Print Len(x)
Stop
Debug.Print .Range("A1").CurrentRegion.Rows.Count

End With
End Sub

what's the result?

 

[Alex Blakenburg]

I'm not expert at VBA but I think it should be

Set x = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

This way it will first search down and then move to next column thus giving the right row number

Sanjay, you have it back to front. To find the last row you search ByRows and to find the last column you search ByColumns

 

[AlexanderBB]

Akuini it prints
$A$3210
1
And it's the same whether ByRows or ByColumns is used

Sub Go_1()
Dim x As Range
With Worksheets("A")
Set x = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Debug.Print x.Address
Debug.Print Len(x)

Set x = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Debug.Print x.Address
Debug.Print Len(x)
End With
End Sub

$A$3210
1
$A$3210
1

I now see it's a Range where I was wanting the row number and should have used x.Row.
In an old workbook I had this:

Public Function LastPopRow(Sheet As String) As Long
LastPopRow = Sheets(Sheet).Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
End Function
Public Function LastPopCol(Sheet As String) As Integer
    LastPopCol = Sheets(Sheet).Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
End Function

Thanks for the replies.

 

[Akuini]

You're welcome.

 

[MARK858]

And it's the same whether ByRows or ByColumns is used

Try it with the data layout below

Sub Go_1()
    Dim x As Range
    With ActiveSheet
        Set x = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        Debug.Print x.Row


        Set x = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        Debug.Print x.Row

    End With
End Sub

Book1.xlsb
	A	B	C	D	E	F	G	H
1	Order ID	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total
2	104-30-N31	30/01/2013	On Site	Margaret Peacock	Meat/Poultry	Air	50	1384.1538
3	106-24-F80	24/09/2013	Off Site	Nancy Davolio	Grains/Cereals	Road	40	509.1354
4	106-25-F10	25/07/2013	Off Site	Laura Callahan	Seafood	Rail	21	371.6742
5	107-21-N11	21/10/2013	On Site	Steven Buchanan	Seafood	Sea	42	521.7894
6	108-21-N44	21/01/2014	On Site	Laura Callahan	Grains/Cereals	Sea	35	866.6508
7	106-29-N14	29/07/2013	On Site	Laura Callahan	Beverages	Road	5	104.8002
8	103-25-N94	25/12/2012	On Site	Nancy Davolio	Confections	Sea	10	483.7476
9	103-26-N15	26/09/2012	On Site	Margaret Peacock	Beverages	Rail	14	226.3584
10	109-31-F89	31/03/2014	Off Site	Andrew Fuller	Condiments	Rail	40	1179.6264
11	107-4-N28	04/11/2013	On Site	Margaret Peacock	Dairy Products	Rail	15	647.8962
12	103-2-N69	02/12/2012	On Site	Laura Callahan	Meat/Poultry	Road	20
13	105-26-F81	26/06/2013	Off Site	Janet Leverling	Beverages	Rail	50
14	106-9-F98	09/10/2013	Off Site	Margaret Peacock	Condiments	Road	65
15	104-13-F45	13/02/2013	Off Site	Janet Leverling	Meat/Poultry	Air	15
16	104-31-N90	31/03/2013	On Site	Robert King	Dairy Products	Road	60
17	103-8-F24	08/10/2012	Off Site	Anne Dodsworth	Dairy Products	Rail	40
18	104-19-N79	19/03/2013	On Site	Janet Leverling	Beverages	Air	30
19	106-9-N99	09/10/2013	On Site	Janet Leverling	Confections	Road	12
20	107-28-N58	28/11/2013	On Site	Janet Leverling	Confections	Road	20
21	103-23-N12	23/09/2012	On Site	Andrew Fuller	Produce	Air	4
Sheet3