Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting and filtering HELP!!!!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting and filtering HELP!!!!!
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 "Yossy" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting and filtering HELP!!!!!
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. "Billy Liddel" wrote: 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 "Yossy" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting/Filtering | Excel Discussion (Misc queries) | |||
Sorting and Filtering by font | Excel Discussion (Misc queries) | |||
sorting and filtering | Excel Worksheet Functions | |||
HELP! Filtering and sorting lists. | Excel Worksheet Functions | |||
Sorting and filtering | Excel Worksheet Functions |