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:

License Number	License Status	UWI	Deemed Asset Value	Deemed Liability Amount	PVS Value
25393	Suspension	12-28-081-09W5		83956	Inactive
		Type	Liability	Included in Cost	Deemed Liability Amount
		Abandonment	WB Abandonment	Y	60081
		Reclamation	Site Reclamation	Y	23875
28928	Issued	04-16-085-09W5		92614	Active
		Type	Liability	Included in Cost	Deemed Liability Amount
		Abandonment	Additional Event	Y	13748
		Abandonment	WB Abandonment	Y	54991
		Reclamation	Site Reclamation	Y	23875

<tbody>
</tbody>

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