2

I need an Excel macro to join seven columns of data on each row until the end of the data is reached. For example if I have a formula like this:

=A1&B1&C1&D1&E1&F1&G1

How can I write the macro so that it increments for every row to the end of the file in a sequence like this?

=A1&B1&C1&D1&E1&F1&G1
=A2&B2&C2&D2&E2&F2&G2
=A3&B3&C3&D3&E3&F3&G3
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
user1356553
  • 27
  • 1
  • 5
  • 2
    Would dragging the formula down work for you? (select the range in which you want to copy your formula, in your example H1:H3 and press CTRL+D). Unless you need to do this repeatedly or within a larger macro there is no need for vba. – assylias Apr 25 '12 at 15:16
  • I agree with @assylias. Why do you need VBA for this? Also if you have lot of rows then you might be averse to dragging the formula down. In such a case you can hover the mouse to the bottom right corner of the cell say H1 which has the formula. You will notice that the cursor changes to a "+". Simply double click. I am assuming that you are putting that formula in the 8th Column and your 7th column doesn't have any blank cells in between. – Siddharth Rout Apr 25 '12 at 15:26
  • This works but I had no idea it could be done without VB. This used to be a Macro only thing and I haven't had to use it for several years. THANKS! Charles – user1356553 Apr 26 '12 at 15:54

3 Answers3

4

With so many answers, the main focus on what assylias and I were highlighting has gone to waste :)

However, if you still want a VBA answer. Use this method. This is much faster than Looping or an Autofill.

Option Explicit

Sub Sample()
    Dim LastRow As Long
    Dim Ws As Worksheet

    Set Ws = Sheets("Sheet1")

    LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row

    '~~> If your range doesn't have a header
    Ws.Range("H1:H" & LastRow).Formula = "=A1&B1&C1&D1&E1&F1&G1"

    '~~> If it does then
    Ws.Range("H2:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"
End Sub

If you have 1000's of rows then you might want to switch off Screenupdating and change Calculation to Manual before you run the code and then reset them at the end of the code.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • This also works perfectly and I prefer it because no matter how big the file is I can just click the macro and resue it. THANKS! Charles – user1356553 Apr 26 '12 at 15:55
0

I think the easiest way to do this would be to just fill down as assylias says but if you want to use VBA:

Selection.AutoFill Destination:=Range("Your Fill Range"), Type:=xlFillDefault

Should copy across the other rows.

Alistair Weir
  • 1,809
  • 6
  • 26
  • 47
0

I agree 100% with the comments and the other answers, why do you need VBA to do this, but just to answer your original question, this is how I would accomplish it:

Sub FillAllWithFormula()
Dim i As Variant
Dim wsht As Worksheet

    'If you are using this for a specific Worksheet use the following
    Set wsht = ThisWorkbook.Worksheets(yourWorksheetName)

    'or if you are always using this for the active sheet use the following
    Set wsht = ActiveSheet

    For i = 1 To wsht.Rows.Count
        'Replace "X" with the column letter you want your formula to appear in
        wsht.Range("X" & i).Formula = "=A" & i & "&B" & i & "&C" & i & "&D" & i & "&E" & i & "&F" & i & "&G" & i
    Next

    Set wsht = Nothing

End Sub
psubsee2003
  • 8,563
  • 8
  • 61
  • 79