Copy Data From Above

[Guest]

Hi, wonder if someone can help please.

I have a great bit of coding below from Norman Jones which deletes contents
of cells and copies and pastes the data from above rows. I've tried to get
hold of him again to see if he could help in tweaking this code perhaps he's
had enough of my questions, don't blame him.

Anyway, rather than the code looking at the whole page I need a specific
range. Norman gave me this amendment 'Set myRange =
Activesheet.Range("A2:A10")', but unfortuantely this selects the range
specifically looking at rows. I basically need the coding to pick the range
columns A to I only, but for it to continue looking down the page copying the
data as in the original coding.

Can someone help please.

Kind regards

Chris

Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub

 

[Guest]

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).Resize(,9)

would extend it out to column I.

 

[Guest]

Tom,

Thanks for this.

I've tried the code, but unfortunately it still changes the columns out of
the range A:I. Any ideas please ?

Kind regards

Chris

 

[Guest]

Well, that is true, but you said you wanted it to look at. It was looking
only at column A and I expanded that to I. Now it appears what you want is
for it to continue looking only in column A, but to act only out to column I.
the adjustment would then be


Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).Resize(1,9).Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub

 

[Guest]

Tom,

Thanks for this.

Please excuse my 'Muppetry', very new to VB, learning as I go along.

Many thanks for your patience.

All the best

Chris