Move to beginning of row

[dmkitz]

I haven't written any code in a number of years so I apologize, but am called upon now to make an Excel spreadsheet more user friendly. I want a macro to 1) add a row; 2) move to the beginning of that row; and 3) merge certain cells within that row.

So far I have: ActiveCell.EntireRow.Insert
Where do I go from there? Thanks.

[RobertBC]

What was exactly you want to do?
get to the first row of your data table and inser a new row
and how about the merging?

give us an example of your spreadsheet (it can be not an actual data)

[mdmackillop]

Hi dmkitz,
Welcome to VBAX

At risk of life and limb, (see http://vbaexpress.com/forum/showpost.php?p=73992&postcount=8)

Sub InsertRowAndMergeCells()
Dim Rw As Long, Cels As String
Selection.EntireRow.Insert
Rw = ActiveCell.Row
Cells(Rw, 1).Activate
Cels = " "
Do Until Cels = ""
Cels = InputBox("Enter column letters to merge" & vbCr & "e.g. 'C-D' or 'C-G'" _
& vbCr & "Leave blank to exit")
If Cels = "" Then Exit Sub
Range(Cells(Rw, Split(Cels, "-")(0)), Cells(Rw, Split(Cels, _
"-")(1))).MergeCells = True
Loop
End Sub

[dmkitz]

I have created a form with several different sections. The sections are all part of the same spreadsheet -- just divided with borders and such for the user. Instead of resizing cells to fit the data that would be inputted, I merged two or three cells to make what looks like one large cell. When you add a row, therefore, the cells I merged in the original form are no longer merged.

I want the user to activate a macro (I put the button on the form) to insert a row above the current cell and then merge those cells which were merged in the original form. The macro that I recorded to do that is this:
Selection.EntireRow.Insert
Range("B129:D129").Select
Selection.merge
Range("E129:F129").Select
Selection.merge

but I can't use that because the cell references may change.

[dmkitz]

Point well taken regarding using merged cells! In my form, I know which columns should be merged, so can I build that right in to the macro?

[mdmackillop]

Try Sub InsertRowAndMergeCells()
Dim Rw As Long, Cels As String
Selection.EntireRow.Insert
Rw = ActiveCell.Row
Cells(Rw, 1).Activate
Range(Cells(Rw, 2), Cells(Rw, 4)).MergeCells = True
Range(Cells(Rw, 8), Cells(Rw, 12)).MergeCells = True
End Sub

[dmkitz]

It works beautifully! Thank you! :clap2:

[mdmackillop]

You're welcome.