Absolute cell referencing

Hi

Please kindly assist with my question below.

I am writing a code to merge cells together; I want the heading of my dataset to be fixed and to appear in all mergers. The table below (and document attached) might give an idea of what I am trying to achieve.

A B C
1 Like Dislike Advice
2 The environment Weather Improve service
3 Nothing Everything Not applicable





Merger 1: - Merging rows A:A with B:B

A1 & A2 & B1 & B2 & C1 & C2

(Like The environment Dislike Weather Advice Improve service)



Merger 1: Merging rows A:A with C:C

A1 & A3 & B1 & B3 & C1 & C3

(Like Nothing Dislike Everything Advice Not applicable)


I have already written a code to merge cells but I can not figure how to make row A:A fixed/absolute.

Regards


Phresh10

Hi

Thanks for getting back and also for your advise. I have attached an excel spreadsheet with the VBA code I wrote.


Regards

Hi

Thanks for looking at my question.

I was given rough spreadsheet that has to be reformatted in many ways before it can be fed it into the system.

I have been able to write 2 codes that will reformat the first row of the spreadsheet, and also I have added a bit of loop. The number of row in the spreadsheet is not fixed.


Code 1

Sub FormatCells()

Set ws = Sheets("Sheet1")

Dim string1, string2, string3, string4, string5, string6 As String

Columns("D:D").Select
Selection.Insert Shift:=xlToRight

DataRow = 2

Do While ws.Range("A" & DataRow) <> ""

'make D2 active cell
Application.Goto Range("D" & DataRow)

string1 = ActiveCell.Offset(-1, 1)
string2 = ActiveCell.Offset(0, 1)
string3 = ActiveCell.Offset(-1, 2)
string4 = ActiveCell.Offset(0, 2)
string5 = ActiveCell.Offset(-1, 3)
string6 = ActiveCell.Offset(0, 3)
ActiveCell.Value = string1 & "-" & string2 & "-" & string3 & "-" & string4 & "-" & string5 & "-" & string6

DataRow = DataRow + 1

Loop

End Sub

Sub Insert()

Rows("3:3").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Range("m2:q2").Cut Range("H3:l3")
Range("r2:v2").Cut Range("h4:l4")
Range("w2:aa2").Cut Range("h5:l5")
Range("Ab2:Af2").Cut Range("h6:l6")
Application.CutCopyMode = False

Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D6"), Type:=xlFillCopy
Range("A2:D6").Select
Columns("D:D").EntireColumn.AutoFit
Rows("6:6").EntireRow.AutoFit


End Sub


…………………………………………………………………………………………

Code 2

Sub FormatCells()

Set ws = Sheets("Sheet1")

Dim string1, string2, string3, string4, string5, string6 As String

Columns("D:D").Select
Selection.Insert Shift:=xlToRight

DataRow = 2

Do While ws.Range("A" & DataRow) <> ""

'make D2 active cell
Application.Goto Range("D" & DataRow)

string1 = ActiveCell.Offset(-1, 1)
string2 = ActiveCell.Offset(0, 1)
string3 = ActiveCell.Offset(-1, 2)
string4 = ActiveCell.Offset(0, 2)
string5 = ActiveCell.Offset(-1, 3)
string6 = ActiveCell.Offset(0, 3)


ActiveCell.FormulaR1C1 = "=R1C5&RC[1]&R1C6&RC[2]&R1C7&RC[3]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault
Range("D2:D4").Select




DataRow = DataRow + 1

Loop

End Sub

Sub Insert()

Rows("3:3").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Range("m2:q2").Cut Range("H3:l3")
Range("r2:v2").Cut Range("h4:l4")
Range("w2:aa2").Cut Range("h5:l5")
Range("Ab2:Af2").Cut Range("h6:l6")
Application.CutCopyMode = False

Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D6"), Type:=xlFillCopy
Range("A2:D6").Select
Columns("D:D").EntireColumn.AutoFit
Rows("6:6").EntireRow.AutoFit


End Sub

Regards