Hi All,
Im a bit of a n00b with these Macros and have been trying to modify some VBA code I used for a different but similar task without success. What I want to do is ensure that there are no blank cells in the the first 2 columns of my spreadsheet.
The data is stored like this
AA : 12 : 34 : 45
: : : 46
: : : 47
BB : 23 : 34 : 48
: : : 49
CC : 45 : 34 : 50
DD: 67 : 34 : 51
: : : 52
: : :53
EE: 91 : 34 :54
And id like the Macro to run through the sheet a copy columns 1 and 2 and paste them into proceeding rows until they hit the next set of data, then copy those cells and paste until they hit the next and so on, so the sheet looks like this
AA : 12 : 34 : 45
AA : 12 : : 46
AA : 12 : : 47
BB : 23 : 34 : 48
BB : 23 : : 49
CC : 45 : 34: 50
DD: 67 : 34 : 51
DD: 67 : : 52
DD: 67: :53
EE: 91 : :54
Sorry for the shitty drawing
but let me know if you have any ideas. This is what I tried to no avail
Sub AddBlankLines()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Col = "A"
StartRow = 1
BlankRows = 2
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R + 1, Col) = "" Then
.Cells(R, Col).Copy
.Cells(R + 1, Col).Paste Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
Bookmarks