MrExcel Publishing
Your One Stop for Excel Tips & Solutions

trying to write a loop

Posted by Susan Tyler-Freer on February 01, 2002 7:48 AM

Hi, I'm very new to writing macros in Excel, in fact I usually just record something and then go in and clean it up a little when I need a macro. But I have this frustratingly simple thing, where I have values in adjacent cells (cols B and C) that need to be in the same column (B, one under the other).

So I need a little macro that will insert blank rows in between the existing rows and then one that will move the value from column C to one line number greater in column B.

I just recorded a few steps of it as a macro, and it worked fine for small sets of data, but now my boss has told me we will be getting datasets of 3,000 lines. My script will be gigantic unless I can figure out how to make Excel repeat itself down column A until it finds a blank cell.

Please help? Thank you so much.


Posted by GREGC on February 01, 2002 8:20 AM





Posted by JohnG on February 01, 2002 8:44 AM

Try this
Sub MergeCols()
Dim FBlank As Boolean
Dim FRow As Integer

FRow = 1
FBlank = IsEmpty(Cells(FRow, 1).Value)
Do While FBlank = False
Rows(FRow + 1 & ":" & FRow + 1).Insert Shift:=xlDown
Cells(FRow, 2).Cut Destination:=Range("A" & FRow + 1)
FRow = FRow + 2
FBlank = IsEmpty(Cells(FRow, 1).Value)
End Sub

Posted by Susan on February 01, 2002 5:29 PM

Thanks VERY much! You have been exquisitely helpful.