Getting a Run-time error 1004 message when trying to run this VBA script

Question

Hi,

I am trying to find the last column of a table so that I can merge cells to keep up with the table when i expand it. But whenever i try to run this code everything work fine until the last line which says 'Method 'Range' of object'_Worksheet failed.

This is the code:

Sub findLastColumn()

    Dim ws As Worksheet

    Set ws = ActiveWorkbook.ActiveSheet

    Dim lastColumn As Long

    lastColumn = ws.ListObjects(1).Range.Columns.Count

    MsgBox (lastColumn)

    Dim row As Integer

    row = 33

    Dim columnLetter As String

    If lastColumn > 26 Then

        columnLetter = Chr(Int((lastColumn - 1) / 26) + 64) & Chr(Int((lastColumn - 1) Mod 26) + 65)

    Else

        columnLetter = Chr(lastColumn + 64)

    End If

    MsgBox (columnLetter)

    ws.Range("D33, columnLetter33").Merge

End Sub

Sorry and I know its probably really simple but I'm quite new to VBA and couldn't find a resolution for this anywhere.

Thanks in Advance,

Will

Answer 1

Thanks! That really helps I don't need the column letter I was just displaying it to try and find where the issue was

Answer 2

At base, your problem is this line:

ws.Range("D33, columnLetter33").Merge

Which should be this (you need to build a valid range address string for the Range object):

ws.Range("D33:" & columnLetter & "33").Merge

But if your list object doesn't start in column A, counting the columns will give you the incorrect last column, so check the column of the last cell of your list object:

    With ws.ListObjects(1).Range

        lastColumn = .Cells(.Cells.Count).Column

    End With

And you never need to get the column letter, since Cells takes a number as well:

    ws.Range(ws.Cells(row, "D"), ws.Cells(row, lastColumn)).Merge

So here is my code...

Sub findLastColumn()

    Dim ws As Worksheet

    Dim row As Integer

    Dim lastColumn As Long

     'Dim columnLetter As String

    Set ws = ActiveWorkbook.ActiveSheet

    With ws.ListObjects(1).Range

        lastColumn = .Cells(.Cells.Count).Column

    End With

    MsgBox lastColumn

    row = 33

    'If you really wanted the column letter for reporting it, this is what you Could do

    'columnLetter = Split(Cells(1, lastColumn).Address, "$")(1)

     'MsgBox columnLetter

    'But this does not use the column letter - it uses the column number "lastColumn"

    ws.Range(ws.Cells(row, "D"), ws.Cells(row, lastColumn)).Merge

End Sub