Variable not set up right?

butch3

New Member
Joined
Feb 4, 2019
Messages
46
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:
NameRelationshipImmediate FamilyNOK 1NOK 2NOK 3
FName LNameFatherSon - First Last
Mother - XX
Father - XY
First LastBrotherMother - XX
Father - XY

<tbody>
</tbody>

After VBA formatting:
NameRelationshipImmediate FamilyNOK 1NOK 2NOK 3
FName LNameFatherSon - First LastMother - XXFather - XY
First LastBrotherMother - XXFather - XY

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

New Member
Joined
Feb 4, 2019
Messages
46
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows
To be honest I didn't actually look at your code.
But this wont work
Code:
Range(Columns(colmnltr) & rowtext).Select
as Range needs a letter for the column not a number.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,895
Messages
5,544,901
Members
410,643
Latest member
sng
Top