Dynamic Row Reference in a Macro 3

[hjgoldstein]

Hi all.

I have generated a macro which performs a couple of functions in my data and then inserts a Pivot Table into the current worksheet.

The data I am using are generated from an external system on a daily basis and the number of records varies.

Here is my question - I know there is an answer and you are probably way ahead of me.

The macro has an absolute row reference for both the copying of functions from (say) B2 to Range B3:B378 and also for the generation of the Pivot Table using A1378.

The data today has only 342 records so there are 36 blank records in the Pivot. that's bad enough, but when there are, say, 489 records, there will be 147 records missing from the data in the Pivot.

Can you advise me how to change

Range("B378").Select

and

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R378C127"

so that the macro looks for the last populated cell in column B?

Any help would be appreciated.

Aspiring to mediocrity since 1957

 

[ClulessChris]

Last populated cell in Column B

Worksheets("Sheet1").Range("B65536").End(xlUp).Row

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[SkipVought]

Hi,

More general to any Excel version...

With Worksheets("Sheet1")
   .Cells(.Rows.Count, "b").End(xlUp).Row 
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hjgoldstein]

Sorry Skip. too vague for me. can you explain a bit more? Where does this go and what does it do?

Aspiring to mediocrity since 1957

 

[SkipVought]

Where does this go and what does it do?

Sorry! I just tool chris' code and generalized it.

Dim lRow as Long

With Worksheets("Sheet1")
   lRow = .Cells(.Rows.Count, "b").End(xlUp).Row 
End With 

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & lRow & "C127"

What version Excel? 2007 does not have PivotCaches.Create???


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Dim lngLastRow As Long
With Worksheets("Sheet1")
   lngLastRow = .Cells(.Rows.Count, "b").End(xlUp).Row 
End With
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & lngLastRow & "C127"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hjgoldstein]

Thanks guys. I think I know what you are doing here. I haven't time to do anything with it tonight but will try it first thing on Monday.



Aspiring to mediocrity since 1957

 

[hjgoldstein]

That worked perfectly. Thanks.

Aspiring to mediocrity since 1957