Please Help. Macro - changing data layout

[talkto_menow]

am trying to import data to Access. Exported excel file has following
layout

Account_1

Date, Amount
Date, Amount


Account_2

Date, Amount
Date, Amount


I would like to move the Account cell value to different position, see
below


Account_1, Date, Amount
Account_1, Date, Amount


Account_2, Date, Amount
Account_2, Date, Amount


Is there any way to create VBA code that could do this for me?


Thanks

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTemp As String

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If IsDate(Cells(i, "A").Value) Then
Cells(i, "A").Resize(, 2).Copy Cells(i, "B")
Cells(i, "A").Value = sTemp
Else
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
If Cells(i, "A").Value <> "" Then
sTemp = Cells(i, "A").Value
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[talkto_menow]

Thanks