AutoFill down a certain number of rows

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

What code is there to specifically filldown a certain (or varied
number of rows)?

I create a Pivot Table of data and refresh this each month, I wish to
take the number of rows found within the Pivot Table and using that
count, fill down onto the bottom of a list of numbers in another
colum.

So presume the Pivot table is in columns F and G.

I have data in columns A and B and wish to add onto the bottom of both
of these columns a bunch of 9's (for Navision). The amount of rows I
need to have as 9s is the same as the number in the Pivot Table.

How do I do it? I have the following code already:

Dim StartCell, EndCell As Range
Set StartCell = Range("G4") 'Pivot
Set EndCell = Cells(Rows.Count, "G").End(xlUp).Offset(-1, 0) 'End of
Pivot excluding Grand Total
Range(StartCell9, EndCell9).Copy

I wish to now add the count of this range onto the bottom of cols A
and B. I thought of using E.g. Range("A50000").End.(xlUp)

then somehow using Selection.FillDown (having inserted 99999999).

Thanks for your help
 
Simon

This all gets very confusing from when you get to this line
Range(StartCell9, EndCell9).Copy
Where did the 9 come from?

Have a look at my code and see where that gets us

Dim StartCell, EndCell As Range
Set StartCell = Range("G4")
Set EndCell = Range("G" & Cells(Cells.Rows.Count, "G").End(xlUp).Row)
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1) _
= Range(StartCell, EndCell).Rows.Count

Mike
 
Simon,

You are pretty much there with your code. As a side note, your StartCell
carries a Variant data type and not a Range data type. See the code below,
which adds onto what you provided.

Dim StartCell As Range
Dim EndCell As Range
Dim lngPvtCnt As Long
Dim rngNines As Range

Set StartCell = Range("G4")
Set EndCell = Cells(Rows.Count, "G").End(xlUp).Offset(-1, 0)

'count of the cells in the pivot table
lngPvtCnt = Range(StartCell, EndCell).Count
'last cell in column A
Set rngNines = Range("A" & Rows.Count).End(xlUp)
With rngNines
'fill the range with "9"
Range(.Offset(1, 0), .Offset(lngPvtCnt, 0)).Value = "9"
End With
 
Simon,

You are pretty much there with your code.  As a side note, your StartCell
carries a Variant data type and not a Range data type.  See the code below,
which adds onto what you provided.

Dim StartCell As Range
Dim EndCell As Range
Dim lngPvtCnt As Long
Dim rngNines As Range

Set StartCell = Range("G4")
Set EndCell = Cells(Rows.Count, "G").End(xlUp).Offset(-1, 0)

'count of the cells in the pivot table
lngPvtCnt = Range(StartCell, EndCell).Count
'last cell in column A
Set rngNines = Range("A" & Rows.Count).End(xlUp)
With rngNines
    'fill the range with "9"
    Range(.Offset(1, 0), .Offset(lngPvtCnt, 0)).Value = "9"
End With













- Show quoted text -
Click to expand...

Thanks Matt, this is what I am after, I would have produced what you
wrote, I thought there may be an easier way but your way does it :)
 
Back
Top