code skips blanks in data
 

Hi I am using the following code to re-format a spreadsheet . It is desigend
to move data on a stock item from a two row display to a single row for each
item. The code below does this generally, but if there are blank fields in
the data ithe cells are skipped. I think there is a problem with the
Selection(0).End(xlDown)(2).Select l.....part of the code

and the 'With Selection' step does not run and so the counter for the i
variable does not register. Hence when the end of the column is reached there
is an error message. Can anyone figure this out and help please


Sub Movem()
'
' Movem Macro
' Macro recorded 25/01/2005 by dannys
'
' Keyboard Shortcut: Ctrl+m


Dim i As Integer: i = 1
Dim rng As Range
Dim rows As Integer
Dim c As Integer


Set rng = ActiveSheet.UsedRange.rows
rows = rng.Count


For c = 1 To 11 Step 2

Cells(6, c).Select
Do Until i = (rows - 6) / 2

With Selection
Selection.Cut Destination:=ActiveCell.Offset(-1, 1)
Selection(0).End(xlDown)(2).Select
End With

i = i + 1

Loop

With Selection
Selection.Cut Destination:=ActiveCell.Offset(-1, 1)

End With

i = 1

Next c


End Sub

code skips blanks in data
 

Try replacing the problem line with:

ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate

--
Ian
--
"Alan M" wrote in message
...
Hi I am using the following code to re-format a spreadsheet . It is
desigend
to move data on a stock item from a two row display to a single row for
each
item. The code below does this generally, but if there are blank fields in
the data ithe cells are skipped. I think there is a problem with the
Selection(0).End(xlDown)(2).Select l.....part of the code

and the 'With Selection' step does not run and so the counter for the i
variable does not register. Hence when the end of the column is reached
there
is an error message. Can anyone figure this out and help please


Sub Movem()
'
' Movem Macro
' Macro recorded 25/01/2005 by dannys
'
' Keyboard Shortcut: Ctrl+m


Dim i As Integer: i = 1
Dim rng As Range
Dim rows As Integer
Dim c As Integer


Set rng = ActiveSheet.UsedRange.rows
rows = rng.Count


For c = 1 To 11 Step 2

Cells(6, c).Select
Do Until i = (rows - 6) / 2

With Selection
Selection.Cut Destination:=ActiveCell.Offset(-1, 1)
Selection(0).End(xlDown)(2).Select
End With

i = i + 1

Loop

With Selection
Selection.Cut Destination:=ActiveCell.Offset(-1, 1)

End With

i = 1

Next c


End Sub