Copy Macro Required

[saraapple]

I need a macro to copy the contents of a cell down until a none blank cell is met:

Stk 28.02.20 Ap.xlsx
	B
5	PAR-00251912
6
7
8
9
10
11
12
13
14	PAR-00252030
15
16
17
18
19	PAR-00251983
20
21
22
23
24
25
26
27
28
29	PAR-00251982
Sheet1

Becomes:

Stk 28.02.20 Ap.xlsx
	A	B
5	1	PAR-00251912
6		PAR-00251912
7		PAR-00251912
8		PAR-00251912
9		PAR-00251912
10		PAR-00251912
11		PAR-00251912
12		PAR-00251912
13		PAR-00251912
14	28	PAR-00252030
15		PAR-00252030
16		PAR-00252030
17		PAR-00252030
18		PAR-00252030
19	4	PAR-00251983
20		PAR-00251983
21		PAR-00251983
22		PAR-00251983
23		PAR-00251983
24		PAR-00251983
25		PAR-00251983
26		PAR-00251983
27		PAR-00251983
28		PAR-00251983
Sheet1

 

[svendiamond]

Sub fill_down()

Dim x As Long, r As Long, lastRow As Long, ws As Worksheet, downTo As Long

Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    
    r = 1
    Do While r < lastRow
        downTo = .Cells(r, 1).End(xlDown).Offset(-1).Row
        If downTo > lastRow Then
            .Range(.Cells(r, 1), .Cells(lastRow, 1)).FillDown
        Else
            .Range(.Cells(r, 1), .Cells(downTo, 1)).FillDown
        End If
        r = downTo + 1
    Loop
    
End With


End Sub

 

[saraapple]

Perfect thank you.
Any idea on the next part:

Stk 28.02.20 Ap.xlsx
	A	B	C	D
1	Date:	28/02/20	DUNELM MERIDIAN - VEHICLE DESPATCH FOR	200228
2	Time:	13:03
3	Page:	1 of 14
4	RTE	TRIP	CARRIER	DRIVER
5	1	PAR-00251912	DHLSTK	Jake Clark
6
7
8
9
10
11
12
13
14	28	PAR-00252030	DHLSTK	Andrew Bacon
15
16
17
18
19	4	PAR-00251983	DHLSTK	Anna
20				Goblewska
21
22
23
24
25
26
27
28
29	3	PAR-00251982	DHLSTK	Daniel Embrey
30
31
32
33
34
35
36
37	2	PAR-00251976	DHLSTK	Jason Norcop
38
39
40	Date:	28/02/20	DUNELM MERIDIAN - VEHICLE DESPATCH FOR	200228
41	Time:	13:03
42	Page:	2 of 14
43	RTE	TRIP	CARRIER	DRIVER
44
Sheet1

I need to delete the rows that contain the below in the table, but not the first occurrence of RTE?
So keep row 4 but the next time RPE appears eg. row 43 I want the row deleting.

This is all the result of a PDF converter!

Thanks

Date:

Time:

Page:

RTE

 

[svendiamond]

Do you still need this? I can probably work on it later today. You basically need to use Range.Find to find the first instance of "RTE" then just loop through all the cells with a For/Next to delete rows with Date/Time/Page/RTE like you said.

 

[saraapple]

Thank you so much for taking the time to come back to me. I think I have managed to do it my macro looks like this:
Sub Macro1()
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Date:*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False

With Range("A1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Time:*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False

With Range("A1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Page:*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False

With Range("A5", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*RTE*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

I know it's a bit messy and should have a proper loop but it seems to work.

 

[svendiamond]

Oh ok good. Hey, if it works, it works. I'm assuming you don't have to run this a million times so no biggie to have a shortcut solution.