VBA code to copy and paste multiple times

[MrsFraser07]

Hi there, I get an awful government report every month that I want to have a macro that will automatically copy and paste multiple times. The file looks like this when I get it:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]License Number
[/TD]
[TD]License Status
[/TD]
[TD]UWI
[/TD]
[TD]Deemed Asset Value
[/TD]
[TD]Deemed Liability Amount
[/TD]
[TD]PVS Value
[/TD]
[/TR]
[TR]
[TD]25393
[/TD]
[TD]Suspension
[/TD]
[TD]12-28-081-09W5
[/TD]
[TD][/TD]
[TD]83956
[/TD]
[TD]Inactive
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type
[/TD]
[TD]Liability
[/TD]
[TD]Included in Cost
[/TD]
[TD]Deemed Liability Amount
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]WB Abandonment
[/TD]
[TD]Y
[/TD]
[TD]60081
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Reclamation
[/TD]
[TD]Site Reclamation
[/TD]
[TD]Y
[/TD]
[TD]23875
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28928
[/TD]
[TD]Issued
[/TD]
[TD]04-16-085-09W5
[/TD]
[TD][/TD]
[TD]92614
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type
[/TD]
[TD]Liability
[/TD]
[TD]Included in Cost
[/TD]
[TD]Deemed Liability Amount
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]Additional Event
[/TD]
[TD]Y
[/TD]
[TD]13748
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]WB Abandonment
[/TD]
[TD]Y
[/TD]
[TD]54991
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Reclamation
[/TD]
[TD]Site Reclamation
[/TD]
[TD]Y
[/TD]
[TD]23875
[/TD]
[/TR]
</tbody>[/TABLE]

What I would like my macro to do, is delete the blank rows (there are always 2 between each data set per license number), then copy the license number, then paste it in all the blank cells underneath it until it reaches the next unique license number. Then copy the new license number, paste into all blank rows beneath it, until it reaches the next, and so on. Hopefully that make sense? Any help would be most appreciated! My report currently has over 25,000 rows and I would hate to have to copy and paste them!

Thanks!
Shari

 

[Fluff]

With the exception of the blank rows, is any of the columns fully populated(ie no blanks)?

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C1:C" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, "A").Value = "" Then Cells(i, 1).Value = Cells(i - 1, 1).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hia
Ignore post #2 , this should work regardless

Sub MrsFraser()

    Dim UsdRws As Long
    Dim Cnt As Long
    
    UsdRws = cells.Find("*", after:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Do Until Cnt = 2
        Cnt = Range("A" & UsdRws).End(xlUp).Row
        Range("A" & Cnt & ":A" & UsdRws).FillDown
        UsdRws = Cnt - 3
    Loop
    Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

End Sub

 

[MrsFraser07]

Thanks. I'm getting an error with the Cnt = Range("A" & UsdRws).End(xlUp).Row part of the code. Not sure why?

 

[My Aswer Is This]

What was wrong with my script in post #3 ? You never mentioned it.

Thanks. I'm getting an error with the Cnt = Range("A" & UsdRws).End(xlUp).Row part of the code. Not sure why?

 

[Fluff]

Thanks. I'm getting an error with the Cnt = Range("A" & UsdRws).End(xlUp).Row part of the code. Not sure why?

a) what error message did you get?
b) is the License Number in column A?

 

[Fluff]

one further point
Is the license number in row 1, with the first set of data in row 2?

 

[MrsFraser07]

Sorry, I thought you said to use the script in post #4 . I get errors in both. The first one I get Run-time error '1004' Delete method of Range class failed. The debugger goes to Range("C1:C" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[MrsFraser07]

Sorry, I thought you said to use the script in post #4 . I get errors in both. The first one I get Run-time error '1004' Delete method of Range class failed. The debugger goes to Range("C1:C" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete