merging columns

[lezawang]

Hi the code below does not give me an error message but does not do the job either? Any help would be appreciated.

Sub col_merge()
    Dim x As Integer
    Dim y As Integer
    x = InputBox("first col#")
    y = InputBox("second col#")
    Workbooks(1).Worksheets(1).Columns("x:y").Merge
End Sub

 

[MARK858]

What are you entering in the inputboxes?


Btw, your code as is will merge columns X & Y on Workbooks(1).Worksheets(1)

 

[DataBlake]

    Workbooks(1).Worksheets(1).Columns("x:y").Merge

the code quoted is basically telling excel to merge the range x:y since you have it in quotes.
for whatever reason i found it easier to do the same thing using strings
so when it asks you which columns you would enter the letter rather than the number
this will however:
- merge anything in between the columns
- merge the entire sheet if you enter the numbers for the column

i'm sure somebody can give you a better code but this is a workaround to accomplish what you're trying to do

Sub col_merge()
    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim x As String
    Dim y As String
    
    Set ws = Worksheets(1)
    
    x = InputBox("first col#")
    y = InputBox("second col#")
    ws.Range(x & ":" & y).EntireColumn.Merge

End Sub

 

[MARK858]

@Lesawang, also just to point out that if you have a Personal Workbook then Workbooks(1) probably refers to that which you probably don't want.

 

[lezawang]

SOrry I entered 1 and 5. Thinking it will merge col#1 to col#5
I want to enter Column numbers and I want excel merge these columns. Yes I see now x and y are merged and that is not what I want. Thank you all.

 

[MARK858]

I don't think you will achieve that easily using column numbers, if they were adjacent columns then you could do the below but even then I don't think you will get the results you are expecting as when you merge cells it only keeps the top/left cells value and that will be centered halfway down the spreadsheet.

Sub col_merge()
    Dim x As Integer
    Dim y As Integer
    x = InputBox("first col#")
    y = InputBox("second col#")
    Application.DisplayAlerts = False
    With Worksheets(1)
        Union(.Columns(x), .Columns(y)).Merge
    End With
    Application.DisplayAlerts = True
End Sub