CODEWITHSUNDEEP
excelvba
HL8
HL8

Reputation: 1419

Find last column

I have the following code:

Last_Column = 0
On Error Resume Next
Last_Column = Sheets("Combined").Cells.Find("", [a1],, , _
                           xlByColumns, xlPrevious).Column

When the sheet has no data it returns Run time error '91': Object variable or With block variable not set,

How can I get it to continue, or what do I have to do?

Upvotes: 0

Views: 201

Answers (2)

brettdj
brettdj

Reputation: 55672

[update: and you should be looking for "*" not '""'

You are better off using a range object and then testing whether it exists, ie

Dim rng1 As Range
Dim Last_Column As Long
Set rng1 = Sheets("Combined").Cells.Find("*", [a1], , , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then
    Last_Column = rng1.Column
Else
    MsgBox "No data", vbCritical
End If

Upvotes: 2

Jamie
Jamie

Reputation: 3372

You should probably check to make sure that the data sheet is not empty or null before running your code. Then inform the user that the sheet is empty.

Upvotes: 0

Related Questions