Range HELP

[GormanRupp]

I have a previously written macro for an old sheet that i would like to use. Unfortunatley i have to take a new worksheet given to me and Unmerge Columns a and b starting with cell 3 of the active cells on the sheet and then copy Columns C and D and paste them in column B. Then i can run the macro as normal. I have this portion from the Macro Recorder, but i cannot get the ranges for the unmerging and copying and pasting portion down correctly. Can anyone help with this? Thank you for your time! It is greatly appreciated!

 

[Andrew Poulsom]

In what way can't you "get the ranges for the unmerging and copying and pasting portion down correctly"? What code do you curently have?

 

[GormanRupp]

Andrew thank you for your reply I really appreciate the interest. The old macro before was taking part numbers and creating hyperlinks.

What's happening now is at my starting point, columns a and b are merged together starting from a3 to a? It can be to a4 or a400 (while still all being merged with the respected column b). I need to unmerge these two columns and then take column c and column d and paste them into the Newley unmerged column b. Then I would auto fit column c.


Would it be easier to do the entire columns instead of creating ranges?

 

[Andrew Poulsom]

Can't you just unmerge columns A:B and delete column B? What did the macro recorder give you?

 

[GormanRupp]

Range("A3:C77).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.Addindent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder - xlContext
.MergeCells = True
End With



'And then this goes on 49 more times for each of the cells in column a that contains text on the final one it says the following:

Selection.Unmerge
Range("C3:D77").Select
Selection.Cut
Range("B3").Select
ActiveSheet.Paste
Columns("C:C").Entirecolumn.Autofit
End Sub



'I tried doing the entire columns but the first three rows are held for titles of the table so i need to create 2 ranges i believe. the first is from A3 to B? and unmerge those cells.

'The next range would be from C3 to D? then cut and paste that range to column B. OR Delete Column B as you said, which would then move them all over to the left. Any Suggestions?

'Thank you very much for your help on this i really appreciate it.

 

[Andrew Poulsom]

If you want to select columns A:C excluding the first 2 rows:

Columns("A:C").Resize(Rows.Count - 2).Offset(2).Select

 

[GormanRupp]

This worked very well. Thank you for your help I greatly appreciate it.