• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

vba macro for pivot table including "blank"

I am running the attached vba for creating a pivot table from the attached data. I don't know why it is including "blank" as one of the entries in the pivot table.
Sub makeRelativePivot2()
'
' makeRelativePivot2 Macro
'

'
  
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "perStockTweets!R1C1:R3926C7", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14

    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Tweet ID"), "Sum of Tweet ID", xlSum
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Tweet ID").Function _
        = xlCount
End Sub

Open in new window

Anyone know about it?
0
willie108
Asked:
willie108
  • 4
  • 2
1 Solution
 
Rory ArchibaldCommented:
Hard to be sure without the data but I'd guess one of your key cells is blank.
0
 
willie108Author Commented:
Hello. I thought I attached the data. Let me try it again.
sample.csv
0
 
willie108Author Commented:
Hello. I attached the data in the previous post. I also tried to make a pivot table manually and did not get a blank row.
Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
Your code uses A1:G3926 as the source data but you only have data to row 1602. That's why you get Blank in the macro created pivot table.
Change this:
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "perStockTweets!R1C1:R3926C7", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14

Open in new window


to this:
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "perStockTweets!" & sheets("perStockTweets").range("A1").Currentregion.address(referencestyle:=xlr1c1), Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14

Open in new window

0
 
willie108Author Commented:
Thanks. Will that be flexible? I want to use it on data for which I don't know the number of rows.
0
 
willie108Author Commented:
Great. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now