Variable not set up right?

[butch3]

Hi all. I've created this simple code to help me move some data around. I'm trying to move data from Column C to Column D, E, F, etc when there is no text in a cell within Column B. See my code and example below.

Sub Test()​

​

Dim rownum As Long​

Dim rowtext As Long​

Dim colmnltr As Long​

​

For rownum = 2 To 950​

​

If IsEmpty(Range("B" & rownum)) Then​

​

Range("C" & rownum).Select​

Selection.Cut​

​

colmnltr = colmnltr + 1​

​

Range(Columns(colmnltr) & rowtext).Select​

ActiveSheet.Paste​

​

Else​

​

rowtext = rownum​

colmnltr = 3​

​

End If​

​

Next rownum​

​

End Sub

​

Initial Data:

Name	Relationship	Immediate Family	NOK 1	NOK 2	NOK 3
FName LName	Father	Son - First Last
		Mother - XX
		Father - XY
First Last	Brother	Mother - XX
		Father - XY

<tbody>
</tbody>

After VBA formatting:

Name	Relationship	Immediate Family	NOK 1	NOK 2	NOK 3
FName LName	Father	Son - First Last	Mother - XX	Father - XY
First Last	Brother	Mother - XX	Father - XY

<tbody>
</tbody>

 

[Fluff]

How about

Sub butch3()
   Dim rng As Range
   
   For Each rng In Range("B:B").SpecialCells(xlBlanks).Areas
      rng.Offset(-1, 2).Resize(1, rng.Count).Value = Application.Transpose(rng.Offset(, 1).Value)
      rng.Offset(, 1).Value = ""
     [COLOR=#ff0000] rng.EntireRow.Delete[/COLOR]
   Next rng
End Sub

If you don't want to delete the empty rows remove the line in red.

 

[butch3]

That was incredibly efficient.

Is there any reason that my code didn't or wouldn't work? I guess I need to read more about the specialcells, areas, offset, resize, and transpose functions.

 

[Fluff]

To be honest I didn't actually look at your code.
But this wont work

Range(Columns(colmnltr) & rowtext).Select

as Range needs a letter for the column not a number.