How to delete the headers in the Pivot Tables?

[aelwan]

After creating the Pivot Table shown below
https://www.dropbox.com/s/80fys3eb9ovbmyc/Pivot1.jpg

I want to get a continuous list of dates with the year included as shown below
https://www.dropbox.com/s/hdo7yea6usckx7u/Pivot3.jpg

In order to get the data organized as shown above, I have copied and pasted all the values from the Pivot Table into a new sheet, deleted all the years and months headers and then organized all the values as a continuous list of dates with the year included. However, following the procedures above is time taking. Could you please let me know if there is any way to delete the years and months (headers)? Thanks in advance.

 

[liiamra]

Hi,

You could easily copy and paste the values of the Pivot table and then delete the rows!

But you have the year & month headers. One way to go around this is to, after copying and pasting the values, sort the dataRange by column B, delete the last rows (I assume the number is static!) and then sort again by Column A.

Something like this:

Sub Example()

  '-------Copying the pivot table & pasting the values
  Columns("A:B").Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
  '
  '-------Sorting by column B (Cells to be deleted will be at the bottom
  Columns("A:B").Sort Key1:=Range("B1")
  '
  '-------Deleting the rows! Supposing that you have a total and blank fields, then these are 2 rows to be deleted and
  '-------suppose you have 2 years and 12 month, then there will be 24 additional rows at the bottom to be deleted
  '
  LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Determining the last used row
  Rows(LastRow - 25 & ":" & LastRow).Delete  'this will delete the last 26 rows
           
End Sub

and so on.

Regards//