Macro for Autofill

[Sebastian]

I need a macro that will take the formula from cells J2 and K2 and autofill them below. The formula for the part of spreadsheet pictured below would be

Sub autofill()
Range("J2:K2").Select
Selection.autofill Destination:=Range("J2:K7"), Type:=xlFillDefault
End Sub

The problem is it's not always seven rows.
Please help.

test.xls
	G	H	I	J	K
1	PartName	QtyOrdered	QtyDespatched	Analysis1	Analysis2
2	ENDCAPMOULDIN	41000	4150	Late	Undersupply
3	ACCELERATORPAD	1400	1400
4	CLAMPHTSNORKE	2600	2000
5	GRILLELARGEBE	12	12
6	GRILLELARGEGR	36	36
7	C/POSTLARGEGR	81	100
Wednesday 17-09-02

 

[Ivan F Moala]

On 2002-09-18 22:09, Sebastian wrote:
I need a macro that will take the formula from cells J2 and K2 and autofill them below. The formula for the part of spreadsheet pictured below would be

Sub autofill()
Range("J2:K2").Select
Selection.autofill Destination:=Range("J2:K7"), Type:=xlFillDefault
End Sub

The problem is it's not always seven rows.
Please help.

test.xls
	G	H	I	J	K
1	PartName	QtyOrdered	QtyDespatched	Analysis1	Analysis2
2	ENDCAPMOULDIN	41000	4150	Late	Undersupply
3	ACCELERATORPAD	1400	1400
4	CLAMPHTSNORKE	2600	2000
5	GRILLELARGEBE	12	12
6	GRILLELARGEGR	36	36
7	C/POSTLARGEGR	81	100
Wednesday 17-09-02

Not fully tested ??<pre/>
Sub AutoFillDown()

Dim Rg As Range
Dim LRg As String

Set Rg = Range("G1").End(xlDown)

LRg = Rg.Item(Rg.Count).Offset(0, 4).Address

Range("J2:K2").AutoFill Destination:=Range("J2" & ":" & LRg)

Set Rg = Nothing

End Sub<pre>

 

[Sebastian]

On 2002-09-18 22:38, Ivan F Moala wrote:

On 2002-09-18 22:09, Sebastian wrote:
I need a macro that will take the formula from cells J2 and K2 and autofill them below. The formula for the part of spreadsheet pictured below would be

Sub autofill()
Range("J2:K2").Select
Selection.autofill Destination:=Range("J2:K7"), Type:=xlFillDefault
End Sub

The problem is it's not always seven rows.
Please help.

test.xls
	G	H	I	J	K
1	PartName	QtyOrdered	QtyDespatched	Analysis1	Analysis2
2	ENDCAPMOULDIN	41000	4150	Late	Undersupply
3	ACCELERATORPAD	1400	1400
4	CLAMPHTSNORKE	2600	2000
5	GRILLELARGEBE	12	12
6	GRILLELARGEGR	36	36
7	C/POSTLARGEGR	81	100
Wednesday 17-09-02

Not fully tested ??<pre/>
Sub AutoFillDown()

Dim Rg As Range
Dim LRg As String

Set Rg = Range("G1").End(xlDown)

LRg = Rg.Item(Rg.Count).Offset(0, 4).Address

Range("J2:K2").AutoFill Destination:=Range("J2" & ":" & LRg)

Set Rg = Nothing

End Sub<pre>

Thanks Ivan,
You're a legend. I've tried it on 12 different workbooks and it worked every time.
Could you help me with one more thing? How to get rid of rows that have cells with "NULL" in them. It can be NULL with ** on each side. Sample below.

Thanks,

SeptemberTD.xls
	D	E	F	G	H	I	J	K	L
1113	17/09/2002	17/09/2002	D	fg	6050202GY	BASEBONAIRE	100	104
1114	17/09/2002	17/09/2002	D	fg	6050106	DISTRIBUTIONTR	76	20
1115	17/09/2002	17/09/2002	D	fg	6050301BE	GRILLEMEDIUMB	176	177
1116	17/09/2002	17/09/2002	D	fg	6050202RD	BASEBONAIRE	100	31
1117	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1118	**NULL**	17/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1119	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1120	**NULL**	10/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1121	**NULL**	12/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
SeptemberTD

 

[Ivan F Moala]

Thanks Ivan,
You're a legend. I've tried it on 12 different workbooks and it worked every time.
Could you help me with one more thing? How to get rid of rows that have cells with "NULL" in them. It can be NULL with ** on each side. Sample below.

Thanks,

SeptemberTD.xls
	D	E	F	G	H	I	J	K	L
1113	17/09/2002	17/09/2002	D	fg	6050202GY	BASEBONAIRE	100	104
1114	17/09/2002	17/09/2002	D	fg	6050106	DISTRIBUTIONTR	76	20
1115	17/09/2002	17/09/2002	D	fg	6050301BE	GRILLEMEDIUMB	176	177
1116	17/09/2002	17/09/2002	D	fg	6050202RD	BASEBONAIRE	100	31
1117	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1118	**NULL**	17/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1119	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1120	**NULL**	10/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1121	**NULL**	12/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
SeptemberTD

From your data it looks like cells with this are also repeated across the row.
Do you want to remove the ENTIRE ROW ?

 

[Sebastian]

On 2002-09-19 01:02, Ivan F Moala wrote:

Thanks Ivan,
You're a legend. I've tried it on 12 different workbooks and it worked every time.
Could you help me with one more thing? How to get rid of rows that have cells with "NULL" in them. It can be NULL with ** on each side. Sample below.

Thanks,

SeptemberTD.xls
	D	E	F	G	H	I	J	K	L
1113	17/09/2002	17/09/2002	D	fg	6050202GY	BASEBONAIRE	100	104
1114	17/09/2002	17/09/2002	D	fg	6050106	DISTRIBUTIONTR	76	20
1115	17/09/2002	17/09/2002	D	fg	6050301BE	GRILLEMEDIUMB	176	177
1116	17/09/2002	17/09/2002	D	fg	6050202RD	BASEBONAIRE	100	31
1117	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1118	**NULL**	17/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1119	**NULL**	16/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1120	**NULL**	10/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
1121	**NULL**	12/09/2002	*	*NUL	L****OVFL****NULL	****NULL**	**NULL**	**NULL**
SeptemberTD

From your data it looks like cells with this are also repeated across the row.
Do you want to remove the ENTIRE ROW ?

That's correct. I want to remove entire row.

 

[Ivan F Moala]

Try something like this;

NB: It may pay to TEST on backup data!

<pre/>
Sub TesterII()
Dim TheRg As Range

Set TheRg = Columns("D:D")

On Error GoTo Ex
With TheRg
.AutoFilter
.AutoFilter Field:=1, Criteria1:="~*~* NULL ~*~*"
.SpecialCells(xlCellTypeConstants, 2).Select
.EntireRow.Delete (xlUp)
End With


Ex: TheRg.AutoFilter

With ActiveSheet
If .AutoFilterMode Then
TheRg.AutoFilter
End If
End With


End Sub
</pre>