Sorting and filtering HELP!!!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yossy - 11 May 2008 03:11 GMT

I created a pivot table but the end result has many empty blanks and
unecessary totals, please does anyone know how I can sort and or filter the
columns without messing up my data. I need just clean rows and of data
without the spaces and the total. I have 20 columns and thousands of rows

All help will be greatly appreciated. Thanks

Reply to this Message

Billy Liddel - 11 May 2008 21:57 GMT

Yossy
Insert a column in A and fill a list of number from 1 to the last row. Then
sort your data and use only the non blank rows.

Alternatively you could just hide the blank rows in the PT using a Macro

Sub hideZeros()
'select the Total column
For Each c In Selection
If c = "" Then
c.EntireRow.Hidden = True
End If
Next
End Sub

And to show all the rows again use:

Sub showAllRows()
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
' Make sure that the Pivot table begins at row 5
' change A5 if necesary
Range("A5:", Cells(LastRow, 1)).Select
Selection.EntireRow.Hidden = False
Range("a5").Select
End Sub

Peter

> I created a pivot table but the end result has many empty blanks and
> unecessary totals, please does anyone know how I can sort and or filter the
> columns without messing up my data. I need just clean rows and of data
> without the spaces and the total. I have 20 columns and thousands of rows
>
> All help will be greatly appreciated. Thanks

Reply to this Message

Yossy - 12 May 2008 04:38 GMT

Thanks Billy,
The code is good but i have a column where some cells are empty and the code
basically cleansed all the other information in the other rows of those
cells. Also the macro runs really really slowly.

In the pivot table field setting option, setting each columns subtotals to
none actually removes the blanks and total but the problem is I have to do
the setting column by column and I have a lot of columns so I was wondering
if there is a quicker way of cleansing the data.

Thanks for helping out. Your help is totally appreciated.

> Yossy
> Insert a column in A and fill a list of number from 1 to the last row. Then
[quoted text clipped - 30 lines]
> >
> > All help will be greatly appreciated. Thanks

Reply to this Message