0

I'm currently having a hard time running a loop to merge two cells vertically. In the picture you can see what I'm talking about: I need to merge C1 and C2, then move on and merge C3 and C4 and so on...until there is no more data.

Can someone help me with that? I just have no idea how to create a loop having all the empty rows in between.. Thanks Andy

Click here to see the screenshot

aduguid
  • 3,099
  • 6
  • 18
  • 37
Andy Weinzierl
  • 11
  • 1

1 Answers1

1

Use something like,

Dim lastR as Integer

'get the last used row in col C
lastR = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 to lastR step 2
   Range(Cells(i, 3), Cells(i + 1, 3)).Merge
Next i

Where the 10 is how far down you want to go, i is the starting row, and 3 is 3rd column, C.

Tim Wilkinson
  • 3,761
  • 11
  • 34
  • 62
  • Wow, the "step" is a great move I've never heard of before. Thanks for that, works perfectly! Is there any chance to find a last row instead of the 10? It has to be somehow possible to say the lastrow is where two empties after last content occur...or am I wrong? – Andy Weinzierl Feb 16 '17 at 20:45
  • 1
    @AndyWeinzierl see here for methods to find the last cell then add 1: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Scott Craner Feb 16 '17 at 20:52
  • Just googling 'Excel find last row VBA' will give you the answer, but I have edited the question regardless. – Tim Wilkinson Feb 16 '17 at 20:53
  • I know how to find a last row, but not for that case with the empties in between.. – Andy Weinzierl Feb 16 '17 at 20:56
  • Ah ok, best best is to go from the bottom up, `Rows.Count` will choose the last row then `End(xlUp) will come up to the last used cell. – Tim Wilkinson Feb 16 '17 at 21:35