Excel VBA question - Running a autofil

  • Thread starter Thread starter Vliegveld
  • Start date Start date
V

Vliegveld

Hello,
My question is if it is possible to program a VB script that ca
autofill all the cells.
I want to create a Pivot table, but the cells are not filled in all.
So for example in colom A there is Name: Erik in A2
Then A1 to A10 are empty but there should be Erik there to.
In B2 there is Sport And in C2 there is the actual sport name.
So The B2 name Sport should be extended further.
I used this code, but then I will have to select all the Ranges wit
hand and type it in.
Is there a way I can let the script run untill there is a filled i
cell and do so for all the coloms?

Range("C17").Select
Selection.AutoFill Destination:=Range("C17:C19"), Type:=xlFillCopy
Range("C17:C19").Select
Range("C21").Select


Range("D17").Select
Selection.AutoFill Destination:=Range("D17:D19"), Type:=xlFillCopy
Range("D17:D19").Select
Range("D21").Selec
 
Dim crow = Cells(Rows.Count,"C").End(xlUp).Row-1

Range("C17").AutoFill Destination:=Range("C17:C" & cRow),
Type:=xlFillCopy

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Tx for the quick reply

When I put

Dim crow = Cells(Rows.Count,"C").End(xlUp).Row-1 Range("C17").AutoFil
Destination:=Range("C17:C" & cRow), Type:=xlFillCopy

I get the error:
Expected: End of Statement

Is it also possible for the script to run until a new value is foun
and then continue the autofill with that new value
 
The newsreader may have wrapped it around. It should all be on one line.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Can't see the wood for the trees<g>. Try this

Dim crow As Long

crow = Cells(Rows.Count, "C").End(xlUp).Row - 1

Range("C17").AutoFill Destination:=Range("C17:C" & crow),
Type:=xlFillCopy


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you very much Bob, this script is working, and copy's all the cel
values.
But the problem is it writes over cells that already have a value.
I would be great if the script stops when he finds a cell with a ne
value, and then continue filling the cells under it with that ne
value. And so on.
Dont know if this is possible, but it would make my day.
(17000 cells to do times 3 coloms
 
v3?

Dim cStartRow As Long
Dim cEndRow As Long
Dim cRow As Long
Dim pzValue

cStartRow = 17
cEndRow = Cells(Rows.Count, "C").End(xlUp).Row
pzValue = Cells(cStartRow, "C").Formula

cStartRow = cStartRow + 1
Do
Cells(cStartRow, "C").Formula = pzValue
cRow = Cells(cStartRow, "C").End(xlDown).Row
If cRow <= cEndRow Then
Cells(cStartRow, "C").AutoFill _
Destination:=Range(Cells(cStartRow, "C"), Cells(cRow - 1,
"C")), _
Type:=xlFillCopy
End If
cStartRow = cRow
Do
cStartRow = cStartRow + 1
Loop Until Cells(cStartRow, "C") = "" Or cStartRow = cEndRow
Loop Until cStartRow >= cEndRow

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the new script,
Unfortunately this one also doesnt stop when a cell in the same colom
has a new value, it just continues autofill with the first value.
 
Not for me it doesn't, give me an example of data where it goes wrong, what
values in what rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Think I may have slightly misunderstood the brief.

See if this version does what you want

Dim cStartRow As Long
Dim cEndRow As Long
Dim cRow As Long
Dim pzValue

cStartRow = 17
cEndRow = Cells(Rows.Count, "C").End(xlUp).Row
pzValue = Cells(cStartRow, "C").Formula

'cStartRow = cStartRow + 1
Do
Cells(cStartRow, "C").Formula = pzValue
cRow = Cells(cStartRow, "C").End(xlDown).Row
If cRow <= cEndRow Then
Cells(cStartRow, "C").AutoFill _
Destination:=Range(Cells(cStartRow, "C"), Cells(cRow - 1,
"C")), _
Type:=xlFillCopy
End If
cStartRow = cRow
Do Until Cells(cStartRow + 1, "C") = "" Or cStartRow + 1 = cEndRow
cStartRow = cStartRow + 1
Loop
pzValue = Cells(cStartRow, "C").Formula
Loop Until cStartRow >= cEndRow



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
Not for me it doesn't, give me an example of data where it goes wrong, what
values in what rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
For example I have:

In cell c17: NC0101
Then in cell c19: NC0102
Then in cell c200: NC0103

It only uses the value of c17 and autofills it to 1600
 
Have you looked at my follow-up post yet?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Or course it can, send me your workbook direct. Watch the spam filter in the
email address.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry Bob, oversaw that post.

I tried it, but strange enough, it works only untill the first change
in cells.
So c17 until c22 It autofils the value from c17,
then it takes the new value from c22 and autofills it to the end
(overwriting all the other values, eg c2003 etc.)
 
As I suggested, send me the workbook.

Clarify one thing, C17 fills dowjn to C21, should C23 be filled down from
C22 or C17?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The workbook is 24 mb. Having problems sending it;-)

it is now as follows (sorry for my unclear descriptions)
c17 is filles with: NC0001, then c18 is empty, c19 is empty
c20 is filled with a new value. c21 is empy until c20001 is emty
c2002 has a new value and so on
So c18 and c19 should be filled with the value from c17,
c21 until 20001 should be filled with the value from c20
and so on.

tx for the help again
 
IF you give me your email address, I will send you a simple workbook which
shows it working.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top