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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Relationship[/TD]
[TD]Immediate Family[/TD]
[TD]NOK 1[/TD]
[TD]NOK 2[/TD]
[TD]NOK 3[/TD]
[/TR]
[TR]
[TD]FName LName[/TD]
[TD]Father[/TD]
[TD]Son - First Last[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mother - XX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Father - XY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First Last[/TD]
[TD]Brother[/TD]
[TD]Mother - XX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Father - XY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After VBA formatting:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Relationship[/TD]
[TD]Immediate Family[/TD]
[TD]NOK 1[/TD]
[TD]NOK 2[/TD]
[TD]NOK 3[/TD]
[/TR]
[TR]
[TD]FName LName[/TD]
[TD]Father[/TD]
[TD]Son - First Last[/TD]
[TD]Mother - XX[/TD]
[TD]Father - XY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First Last[/TD]
[TD]Brother[/TD]
[TD]Mother - XX[/TD]
[TD]Father - XY[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.