CODEWITHSUNDEEP
vbaexcel
Hiddenllyy
Hiddenllyy

Reputation: 179

How to know the length of the longest row in a worksheet

I'm working with a quality control table, and I want to find (or know) the longest row which contains the lagest number of cells. for example:

aa | bb | cc | dd |
aa | kk |
aa | dd | ss |

Well, I want the program can tell me the longest row is the first one, which contains 4 columns. I've used this code:

ActiveCell.End(xlDown).Select
ActiveCell.End(xlToRight).Select
my_line = Selection.Row
my_column= Selection.Column + 1

But it can only give me the length of the last line but not the longest one. So if you've some ideas please leave a comment.

Upvotes: 1

Views: 712

Answers (1)

user4039065
user4039065

Reputation:

Use the Range.Find method with a wildcard. Start at A1 and look backwards (i.e. xlPrevious). The Find will quickly find the last used cell by column.

Dim lst As Range
With Worksheets("Sheet7")
    Set lst = .Cells.Find(What:=Chr(42), After:=.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
End With
Debug.Print lst.Row
Debug.Print lst.Column

Upvotes: 3

Related Questions