finding the last used column when it is merged?

[tonywatsonhelp]

Hi everyone
I need a macro that can message box me the last used column in the active sheet but many of the rows are merged and go past any data, I want to put data down the first column that's not being used but I'm hitting problems as we download the data and they merge so many cells so excel thinks the last column is g for example, because that is the last column with data in it but let's say row 10 is a merge of C10:K10, so I need it to find me K so I know the column I need is L
please help if you can
Tony

 

[Anthony47]

Try using this snippet:

On Error Resume Next
LastC = 1
LastC = Cells.Find(What:="*", After:=Range("A1"), _
    Lookat:=xlPart, LookIn:=xlValues, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0

LastC should tell you which is the last used column, or 1 if the sheet is empty

 

[MARK858]

@Anthony47 the OP is after the last cell in the merged range when it extends past the last column with data so with the data below the OP is looking for 11 (Column K) as C10:K10 is merged, whereas your code returns 7

so excel thinks the last column is g for example, because that is the last column with data in it but let's say row 10 is a merge of C10:K10, so I need it to find me K so I know the column I need is L
please help if you can

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order ID	Order Date	Order Date	Site	SP Name	Category Name	Ship Via
2	105-29-N21	29/04/2013	29/04/2013	On Site	Laura Callahan	Beverages	Road
3	104-14-F46	14/02/2013	14/02/2013	Off Site	Michael Suyama	Grains/Cereals	Road
4	109-31-F87	31/03/2014	31/03/2014	Off Site	Laura Callahan	Dairy Products	Air
5	105-30-F22	30/04/2013	30/04/2013	Off Site	Margaret Peacock	Beverages	Road
6	104-1-F01	01/01/2013	01/01/2013	Off Site	Nancy Davolio	Dairy Products	Road
7	110-1-F64	01/05/2014	01/05/2014	Off Site	Nancy Davolio	Confections	Rail
8	106-8-F25	08/08/2013	08/08/2013	Off Site	Janet Leverling	Produce	Air
9	104-1-F01	01/01/2013	01/01/2013	Off Site	Nancy Davolio	Grains/Cereals	Sea
10	104-1-F02	01/01/2013	01/01/2013
11	102-19-N61	19/07/2012	19/07/2012	On Site	Margaret Peacock	Beverages	Air
12	108-13-F29	13/01/2014	13/01/2014	Off Site	Anne Dodsworth	Beverages	Sea
13	104-4-N64	04/03/2013	04/03/2013	On Site	Margaret Peacock	Grains/Cereals	Air
14	105-23-N47	23/05/2013	23/05/2013	On Site	Janet Leverling	Seafood	Air
Sheet1

 

[MARK858]

@tonywatsonhelp try the code below adapted from some code Chris Neilsen posted at SO.

Sub testit()
MsgBox MyLastCol(ActiveSheet) 'replace ActiveSheet with Sheets("YourSheetName") if applying to another sheet
End Sub

Function MyLastCol(ws As Worksheet) As Long
    Dim ur As Range
    Dim lastcell As Range
    Dim col As Long
    Dim urCol As Range
    Dim urCell As Range
    
    Set ur = ws.UsedRange
  
    Set lastcell = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, , xlByColumns, xlPrevious)
    If Not lastcell Is Nothing Then
    For col = ur.Columns.Count To lastcell.Column - ur.Column + 2 Step -1
        Set urCol = ur.Columns(col)
        If Application.CountA(urCol) > 0 Then
            MyLastCol = urCol.Column
            Exit Function
        End If
        If IsNull(urCol.MergeCells) Then
            For Each urCell In urCol.Cells
                If urCell.MergeCells Then
                    If Not IsEmpty(urCell.MergeArea.Cells(1, 1)) Then
                        MyLastCol = urCol.Column
                        Exit Function
                    End If
                End If
            Next
        End If
    Next
    MyLastCol = lastcell.Column
    Else
    MyLastCol = 1
    End If
End Function

 

[Anthony47]

I realize now what the OP meant, thank you
And I realized that my snippet IGNORE merged cells
I mean that, using the data in the picture (H7:K7 are merged cells), that code returns 7; or 1 (the default value) if G7 is empty

I am sure your code does the right job!

Have a nice evening

 

[MARK858]

I am sure your code does the right job!

You're more confident then I am (hate working with merged cells so only lightly tested) , we'll see

 

[tonywatsonhelp]

Thank you for your help,
I hate meged cells as well but these are really bad, but marks vba seams to be working thank you

 

[jolivanes]

Would it not be better to replace all merged ranges with center across selection with a piece of code?

Sub Change_Merged_To_Center_Across()
Dim c As Range
For Each c In ActiveSheet.UsedRange
With c
 If .MergeCells Then
    With .MergeArea
        .UnMerge
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
 End If
End With
Next c
End Sub

I know it has the dreaded Loop but with today's machines that should not be a problem if the UsedRange is within limits.
I have not tested it for LastColumn results but I will after I get back later tonight if someone else has not done it