Find last column

Question

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?

brettdj · Accepted Answer · 2011-11-03 01:01:54Z

2

[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

answered Nov 3, 2011 at 1:01

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brettdj Over a year ago

I just saw your other response. You can continue with formatting either after the Else (different formatting depending on whether Last_Column' exists) or End If` above (ie same formatting for sheet with content or not). My query would be though what would you format in an empty sheet?

Jamie · Accepted Answer · 2011-11-03 00:55:00Z

0

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.

answered Nov 3, 2011 at 0:55

Jamie

3,3671 gold badge22 silver badges28 bronze badges

1 Comment

HL8 Over a year ago

Hi, but when it's empty I still want to be able to with formating the sheet, eg. headings etc.

Collectives™ on Stack Overflow

Find last column

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related