Loop to cleanup data

T

thegetch1

Hi,

I have a spreadsheet that is a dump from a survey. Problem is,
sometimes the dump puts data that should be in one row on two. I need
to write a macro that will see there is a second row, and if there is,
move it up one row. It's easy to identify which rows need to get moved,
because the data starts in column D rather than A. Problem is, I'm not
good with syntax as you can see. I need the column name to stay the
same, but increment which row I am on. I want i and p to keep track of
that so that when I say Ci it equals C3 if in row 3, C4 if in row 4,
etc. Excel reads it though as column name CI, which is not what I want.

Sub cleanup()
'
' cleanup Macro
' Macro recorded 11/10/2006 by n0148234
'

'Set last row
Dim lastRow As Double
lastRow = ActiveSheet.UsedRange.Rows.Count
'Set lead variable
Dim i As Double
i = 3
'Set lag variable
Dim p As Double
p = 2

For row = 3 To lastRow

If IsEmpty(Range("Ci:Ci").Select) Then

Range("Di:Qi").Select
Selection.Cut
Range("Hp").Select
ActiveSheet.Paste
Rows("i:i").Select
Selection.Delete Shift:=xlUp

End If

i = i + 1
p = p + 1
Next row

End Sub

I'm sure this is fairly simple, but an help would be greatly
appreciated!!

Getch
 
A

Alan

Getch, your syntax is a bit cluncky but not really surprised as it
started life as a recording. Your major problem is that you are
including variables inside strings. To use ranges you will need to
concatenate ... e.g.

Range("Di:Qi").Select becomes Range("D" & i & ":" & "Q" & i).Select

As you can see this becomes very long winded so you may be better
usinge Cells rather than ranges. I can't figure out exactly what you
are trying to achive, the "delete row i" and "increase i by 1" go out
of sync but here is my version of what you have written if it's any use

Sub cleanup()
'
' cleanup Macro
' Macro recorded 11/10/2006 by n0148234
'


'Set last row
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
'Set lead variable
Dim i As Long
i = 3
'Set lag variable
Dim p As Long
p = 2

' for this to work lastrow must be greater than 3
For Row = 3 To lastRow


If IsEmpty(Cells(i, 3)) Then
Range(Cells(i, 4), Cells(i, 17)).Cut
ActiveSheet.Paste Cells(p, 8)
Cells(i, 1).EntireRow.Delete
End If

i = i + 1
p = p + 1
Next Row

End Sub

Alan
 
T

thegetch1

Thank you for your quick response. Despite first glance, it turns out
that there is too much variation in the data to even attempt a macro.
There wouldn't be fields consistent enough to say well it's empty so do
this. Thanks for you help anyway, glad to know people are willing to
help!!
 
Top