Macro for Autofill

Sebastian

New Member
Joined
Sep 17, 2002
Messages
32
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
GHIJK
1PartNameQtyOrderedQtyDespatchedAnalysis1Analysis2
2ENDCAPMOULDIN410004150LateUndersupply
3ACCELERATORPAD14001400
4CLAMPHTSNORKE26002000
5GRILLELARGEBE1212
6GRILLELARGEGR3636
7C/POSTLARGEGR81100
Wednesday 17-09-02
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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
GHIJK
1PartNameQtyOrderedQtyDespatchedAnalysis1Analysis2
2ENDCAPMOULDIN410004150LateUndersupply
3ACCELERATORPAD14001400
4CLAMPHTSNORKE26002000
5GRILLELARGEBE1212
6GRILLELARGEGR3636
7C/POSTLARGEGR81100
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

New Member
Joined
Sep 17, 2002
Messages
32
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
GHIJK
1PartNameQtyOrderedQtyDespatchedAnalysis1Analysis2
2ENDCAPMOULDIN410004150LateUndersupply
3ACCELERATORPAD14001400
4CLAMPHTSNORKE26002000
5GRILLELARGEBE1212
6GRILLELARGEGR3636
7C/POSTLARGEGR81100
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
DEFGHIJKL
111317/09/200217/09/2002Dfg6050202GYBASEBONAIRE100104
111417/09/200217/09/2002Dfg6050106DISTRIBUTIONTR7620
111517/09/200217/09/2002Dfg6050301BEGRILLEMEDIUMB176177
111617/09/200217/09/2002Dfg6050202RDBASEBONAIRE10031
1117**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1118**NULL**17/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1119**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1120**NULL**10/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1121**NULL**12/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
SeptemberTD
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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
DEFGHIJKL
111317/09/200217/09/2002Dfg6050202GYBASEBONAIRE100104
111417/09/200217/09/2002Dfg6050106DISTRIBUTIONTR7620
111517/09/200217/09/2002Dfg6050301BEGRILLEMEDIUMB176177
111617/09/200217/09/2002Dfg6050202RDBASEBONAIRE10031
1117**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1118**NULL**17/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1119**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1120**NULL**10/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1121**NULL**12/09/2002**NULL****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

New Member
Joined
Sep 17, 2002
Messages
32
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
DEFGHIJKL
111317/09/200217/09/2002Dfg6050202GYBASEBONAIRE100104
111417/09/200217/09/2002Dfg6050106DISTRIBUTIONTR7620
111517/09/200217/09/2002Dfg6050301BEGRILLEMEDIUMB176177
111617/09/200217/09/2002Dfg6050202RDBASEBONAIRE10031
1117**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1118**NULL**17/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1119**NULL**16/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1120**NULL**10/09/2002**NULL****OVFL****NULL****NULL****NULL****NULL**
1121**NULL**12/09/2002**NULL****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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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>
 

Forum statistics

Threads
1,144,330
Messages
5,723,746
Members
422,513
Latest member
Meathead2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top