moving cells from 1 line to another in vba (1 Viewer)

[swarv]

Hi all,

I have a sheet with quite a few records.
In Column A is the title of the person but there are some gaps. i.e. Row 5228 might not have a title in it.
when I get to this row can I copy the cells B5228, C5228, D5228, E5228, F5228 and place them in L5227, M5227, N5227, O5227, P5227 ?

Then Delete the line 5228?

Im sure this is possible but not sure how.

Thanks

Martin

 

[DCrake]

First question; Why?
Second question; Are you attempting to import this sheet into Access?

More Info please

 

[swarv]

some background:
This is a sheet exported from another system in excel to then import into an sql database.

In cell K5227 there is a caraige return inserted in ( / ) and therefore it puts the rest of the line onto a new line below and I want to place that info on the new line onto the end of the above line and then delete the new line.

I have vba code to delete the slashes.

Any help?

Thanks

 

[chergh]

Try this:

Sub blah()

For i = Worksheets("Sheet1").UsedRange.Rows.Count To 2 Step -1

    If IsEmpty(Worksheets("Sheet1").Range("A" & i)) Then
    
        Worksheets("Sheet1").Range("B" & i, "F" & i).Copy
        Worksheets("Sheet1").Range("L" & i - 1).PasteSpecial xlPasteValues
        Worksheets("Sheet1").Rows(i).Delete
        
    End If
Next i

End Sub

 

[swarv]

I have edited it below:
Is this correct?
what should I dim i as?
my sheet is called react.
Could you explain what A is please?

Sorry about the questions.

Sub blah()
Dim i As String
For i = Worksheets("react").UsedRange.Rows.Count To 2 Step -1
    If IsEmpty(Worksheets("react").Range("A" & i)) Then
    
        Worksheets("react").Range("B" & i, "F" & i).Copy
        Worksheets("react").Range("L" & i - 1).PasteSpecial xlPasteValues
        Worksheets("react").Rows(i).Delete
        
    End If
Next i
End Sub

 

[swarv]

I think I have tidyed it up a bit and it seems to work.

Sub blah()
Dim i As Variant
For i = Worksheets("react").UsedRange.Rows.Count To 2 Step -1
If IsEmpty(Worksheets("react").Range("I" & i)) Then

Worksheets("react").Range("B" & i, "F" & i).Copy
Worksheets("react").Range("L" & i - 1).PasteSpecial xlPasteValues
Worksheets("react").Rows(i).Delete

End If
Next i
End Sub

 

[chergh]

i should be dim'd as "long". You seem to have figured everything else out.

 

[Brianwarnock]

I don't understand why the isempty is for Range("I"& i) when the title is in column A

Brian

 

[chergh]

Good point brian I didn't even notice he changed that.

 

[swarv]

The A column is for title like you said but sometimes the title is missing and the rest of the row is fine.
If I search for I column then it picks up the row that I want.
make sense - im losing myself here.

 

[Brianwarnock]

I was just going by what you said in the first post, but if you are happy that all is well then fine.

Brian