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

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
Hello,
My question is if it is possible to program a VB script that can
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 with
hand and type it in.
Is there a way I can let the script run untill there is a filled in
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").Select


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
Tx for the quick reply

When I put

Dim crow = Cells(Rows.Count,"C").End(xlUp).Row-1 Range("C17").AutoFill
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 found
and then continue the autofill with that new value?


---
Message posted from http://www.ExcelForum.com/

still the same error comes u

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
still the same error comes up


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
Thank you very much Bob, this script is working, and copy's all the cell
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 new
value, and then continue filling the cells under it with that new
value. And so on.
Dont know if this is possible, but it would make my day.
(17000 cells to do times 3 coloms)


---
Message posted from http://www.ExcelForum.com/

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.


---
Message posted from http://www.ExcelForum.com/

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)

"Vliegveld " wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
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)

"Vliegveld " wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
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 16000


---
Message posted from http://www.ExcelForum.com/

Maybe this cannot be done with a script

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
Maybe this cannot be done with a script?


---
Message posted from http://www.ExcelForum.com/

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.)


---
Message posted from http://www.ExcelForum.com/

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)

"Vliegveld " wrote in message
...
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.)


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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)

"Vliegveld " wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

it is: ;-)


---
Message posted from http://www.ExcelForum.com/

It;s in the post.

--

HTH

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

"Vliegveld " wrote in message
...
it is: ;-)


---
Message posted from http://www.ExcelForum.com/