Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Delete rows with no data

I have an excel spreadsheet that I would like it to automatically look at
columns of data and if there is no data or formulas for that column of data
to go ahead and delete the column or if I have it in a row to delete the row.
Is there an easy way to do this?


Post your answer or comment

comments powered by Disqus
Hi all, and thanks for your help in advance.

I'm having trouble with a calculated item in a pivot table that when created adds records to the pivot table that have no data. Any help would be greatly appreciated.

My pivot table has the following data:

PivotField1 | PivotField2 | Type | Data
---------------------------------------
A           | D           | One  |    1 
A           | D           | Two  |    2
A           | E           | One  |    3
A           | E           | Two  |    4
A           | F           | One  |    5
A           | F           | Two  |    6
B           | G           | One  |    7
B           | G           | Two  |    8
B           | H           | One  |    9
B           | H           | Two  |   10
B           | I           | One  |   11
B           | I           | Two  |   12
C           | J           | One  |   13
C           | J           | Two  |   14
C           | K           | One  |   15
C           | K           | Two  |   16
C           | L           | One  |   17
C           | L           | Two  |   18
I made the column pivot the Type field, with the row pivots the PivotField1 and PivotField2 fields. I also made my data the sum of the Data field. The resulting pivot table looks like this:

Sum of Data                 | Type      
---------------------------------------
PivotField 1 | PivotField 2 | One | Two
---------------------------------------
             | D            |   1 |   2
A            | E            |   3 |   4
             | F            |   5 |   6
---------------------------------------
             | G            |   7 |   8
B            | H            |   9 |  10
             | I            |  11 |  12
---------------------------------------
             | J            |  13 |  14
C            | K            |  15 |  16
             | L            |  17 |  18
---------------------------------------
So far, so good. I then try to add a calculated item to the Type field, named Three, which is defined as "= One * Two". I would then expect the following:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
A            | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
-----------------------------------------------
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
-----------------------------------------------
             | J            |  13 |  14 |   182
C            | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------
Unfortunately, I get the following:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
             | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
             | G            |     |     |     0
A            | H            |     |     |     0
             | I            |     |     |     0
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |     |     |     0
C            | H            |     |     |     0
             | I            |     |     |     0
             | J            |  13 |  14 |   182
             | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------
Is there any way to remove the rows with no data? I've tried using an if function to check if the value is > 0, but that doesn't seem to help.

Thanks again for your assistance.

Hi there,

I am trying, with no luck, to create a VBA macro in Excel that will
delete rows with data duplicated in 2 columns. Column A has ID
numbers and Column B has Dates. I need to delete rows that have
duplicate ID No. AND Date and leave the other rows on the worksheet.

I have tried Chip Pearson's code which works well however it doesn't
allow for the dates in column B so it considered the latest date to be
the record to leave and deletes the rest.

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "H").Value = Cells(RowNdx - 1, "H").Value Then
If Cells(RowNdx, "I").Value

I have several spreadsheets in which 4 of every 5 Rows needs to be deleted.
ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Dear Experts ,

I have a spreadsheet with a million rows ,

Now the thing is if there are rows with identical data in column A ,B ,C,D,F then i want to get such rows deleted ,If there are 2 such rows ,then i want 1 row deleted ,
'If there 3 such rows ,then i want 2 deleted ,

If 4 rows ,i want 3 deleted ,

Basically i just want to have all rows with unique data in A,B,C,D,F,(Please dont check E ,if at all ,it cn just help that while selecting to delete rows ,the one with lesser data in E can be deleted first ,but other than that "E"is not useful to my study ,
I attach a excel file with input sheet ,you will see that rows 1 and 2 are identical and 2 has more characters in cell E ,so i delete 2 and keep 1 ,row 3 is a unique enry ,so i keep that as welll ,

This process for million rows or as long row data is available is what i need ,

Regards ,

Amlan Dutta

I wrote a simple macro to delete cells that are not shaded in yellow. The code does not work like I want it to. I have to run the macro several times in order to delete all of the rows that are white and not shaded. I have a spreadsheet with certain rows shaded in yellow. I want to delete all rows in the data set that are not shaded. Here is the code I am using.

For i = 1 to lastrow
cells(i,1).select
If selection.Interior.ColorIndex 6 then
selection. entireRow.delete
end if
next i
end sub

Again, it works but only deletes some rows at a time instead of all non shaded rows.
Thanks,

Hi there!
I face a problem with my Pivot Table. I would like to hide automatically the rows where there is no data in the TOTAL column (the last one). Is it possible?
Thanks in advance for your help!
Bebert

Hi,
I have a pivot table for which I always need to displays rows items.
eg) I have a column named Age bucket. (< 1 day, < 1 week, 1-2 weeks etc)
& region (London, NY etc.)
I alwayd need to display the Age buckets & regions irrespective of whether
they appear in the pivot data source or not. The age buckets which doesnt
have data can show "0" against them.
I read something about doing it via SQL, but is there nay other wya?
I have turned on the "show items with no data" under field settings for Age
bucket & region.
Thnaks,
--
Tausif Mohammed

Hi my name is Nile.

I have the following code that checks every row from the bottom for any
data
and having found none deletes it, then goes on, it is limited to the
certain
range.

It works fine with contiguous range such as ("A10:C20"), but does not
work
with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone
help
me please?

here is the code:
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-
Dim i As Long

'turn off calculation and screenupdating.
With Application
..Calculation = xlCalculationManual
..ScreenUpdating = False

Range("A8:A58,D8:K58").Select

'working backwords because deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
End With
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-

Hello all- my first post here and I'm a little lost as to how to change the way one of my codes pulls values. I have the following code that I use the attached example file:

Option Explicit
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "Timberline" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Timberline").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Timberline"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Timberline"

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "FP SUMMARY"), 0)) Then

            'Find the last row with data on the DestSh
            Last = LastRow(DestSh)

            'Fill in the range that you want to copy
            Set CopyRng = sh.Range("X1:Z600")

            'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"
                GoTo ExitTheSub
            End If

            'This example copies values/formats, if you only want to copy the
            'values or want to copy everything look at the example below this macro
             With CopyRng
        DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
            .Columns.Count).Value = .Value
    End With

            'Optional: This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

When I run the macro it populates a new sheet titled "Timberline" with all of the values in the X:Z columns. I would like to only pull those rows that have values of greater than 0, as it is set up to pull the first 500 or so rows from each tab. Some of these files get to be 30 sheets, so of the 15,000 rows that are populated, roughly 1/10th of them are really needed. It's not a big deal for myself to clean the page up, but I'm not the end user. Any help would be greatly appreciated. Thanks.

Hi,

I am using excel 2010 and in that a pivot table for getting data from a cube.Now when i retrieve data from the cube it is not showing data for which the values are null or zero.I have tried pivot table options >display>ticking both show items on rows and columns with no data...but that doesn't seem to work.Even in pivot tools>options>field settings>print & layout the check box is greyed out leaving me no option to check it.Can anyone tell me what could be the possible workaround for this?

Thanks

Hi all, a long shot maybe but I am after the following.

I have a sheet of data approx 3000 rows (20 columns) which is given to me every week. This data is in the same format with all information being in the same column each wee, however the data in each column changes values / text etc. From this data a pivot chart is created to calculate our largest downtime pieces of equipment.

Some of the rows we want to delete every week as the breakdown is nothing to do with our dept. These rows can be anywhere in the sheet.

The rows we want to delete will always have the data to be identified as unwanted in the same column i.e the fault description always appears in column 'M'.

What I was thinking of was a way of having a list of faults (that we do not want included in our data) on one sheet and then a macro that runs through the original data and deletes all the rows with the corresponding fault in the unwanted list sheet.

The list of faults to be deleted will grow over time and so must be variable.

Is this possible?

Thanks for any help or guidance given.

Can Someone help me out with a macro.

I need a macro to delete all rows with the data "06 - DEAD" in column H

Would really appreciate if someone could help me out with this.

I just took on a client who sends me a multipage spreadsheet weekly with movie times for his 10 theatres.
My guess is he just deletes the information from the previous weeks row and adds a new row. His sheet really only needs about 16 columns and maybe 20 rows. The sheets he sends have upwards of 1500 rows and columns.
Many of the rows are in between the actual text rows as well.

I convert this to a webpage and post to his site. Problem is the sheets are so large they take forever to clean up (in GoLive) and much too long to do by hand.
Is there an easy way to delete all rows with no text in them?

thank you in advance for any help.

Hey everyone,

So I'm trying to format a large series of data involving names and addresses. Some of the rows however consist of names and no addresses while most of it consists of names with addresses.

What I want to do is design a macro to delete all rows that include names and no addresses.

The way its formatted is column A and B consist of first and last name while column c consists of the address so a macro would have to essentially scan column c for all cells with no data and then delete the corresponding row associated with that cell.

I've just been stumped on exactly how I would go about doing that.

Hi my name is Nile.

I have the following code that checks every row from the bottom for any data
and having found none deletes it, then goes on, it is limited to the certain
range.

It works fine with contiguous range such as ("A10:C20"), but does not work
with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help
me please?

here is the code:
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-
Dim i As Long

'turn off calculation and screenupdating.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Range("A8:A58,D8:K58").Select

'working backwords because deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-

I am looking to write a macro that will delete rows from a spreadsheet that contain blank cells, not a number and data that deviates too far from the mean for each specific column. I know how to delete all rows containing blank cells; however, I do not know how to delete all rows that contain cells with non-numerical entries. I would also like to have the macro determine if the data in each cell is valid based on the data in the remaining cells of that column and delete rows containing invalid data (i.e. if a pressure transmitter was not properly maintained and suddenly read erratically, I want the rows containing erratic readings deleted so it will not crash the data processing spreadsheet that it will be run through afterwards). The only way I can think to do this is to compare each cell's data to the mean for the associated column. I am not set on doing it this way if there is a more efficient way to get the results I am looking for.

Hello,

I have used from time to time the field option "Show items with no data".
However, most often I prefer to see no entry if there is no data.

Today, I would even like to go further: I would like to see no entry (row of column header) if the (all) data are zero for a given item.
Very often I have a lot of zeros in my PT, that I would prefer not to see.
And I would even more like not to see them on a pivot chart.

Do you think there would be a way to avoid the display of "zero" items?
Of course, I would like to do that without changing the original data base.

Thanks

Hi - hope someone can help.

I have a problem with the "show items with no data" feature.

For example, I have an excel workbook that is about 4 MB. It has one
worksheet, and on it is one pivot table displaying data from an external
source (via MS Query). The pivot has 7 row fields, no page fields, and one
column field. When I check the "show items with no data" checkbox (for any of
those fields) Excel displays a message "There is not enough memory
available..." When I uncheck the "show items with no data checkbox on that
field, the pivot functions perfectly. The pivot when fully "expanded"
requires only 10,000 rows to display.

This issue w/ the "show items with no data" isn't limited to just one
workbook. This problem occurs on many pivots in many different workbooks.

Anyone have any idea why this is happening?

Thanks so much,
gt

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...22120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

Greetings

My data is in financial year, July to June. The date field is in the format
DD/MM/YYYY which is in the column area of the pivot table. I have this
grouped by month and year, so it displays Jul-Dec 2003, followed by Jan-Jun
2004.

Some items in the "Row" area of the table do not have entries for all 12
months, but I still want to display the month for that item (with no data).
In the "field settings" of month I have selected "show items with no data".
Unfortunately this then displays Jan-Jun for 2003 and Jul-Dec for 2004, all
blank because those dates are not in the financial year I am reporting on -
and I don't want to see those months at all.

how can I just show 03/04 months (even if there is no data in them) and not
02/03 or 04/05 months?

thanks in advance

Dan

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...2120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

a column with no data, only different color fillings, how can I filter for a
specific color?

I receive the information analyzed manually and specific colors indicate
different status for production orders, and I want to write me a macro for
further analysis, but the only thing different from other orders is the
color filling of the row. Can I filter a cell's color filling (shade)???
Thanks

The attached pivot table is 'showing items with no data' even tho that option is not ticked under the Pivot table field options. I need to hide the names where there is no total. It is possible to copy and paste values only, then apply a filter to hide blank rows but this is obviously not ideal when the source data changes.

Sum of Sales
Name Total
Anna 5.5
Dave
Ian 26.65
James
Maria
Neil
Nicola 39.3
Tim 37.34
Grand Total 108.79

Thanks!

I need another macro! Boy, I wish I knew how to write these wonderful
things! In the sample below I need a macro that will delete the entire rows
where the data is repeated in a given column---that is, that it would delete
the repeat ones, leaving one of them. In this example, in the first set, all
that should be left are two rows which contain ME22N and MR8M in column E.
ME22N is repeated two more times and those rows need to be deleted. If I
could get a macro that would ask for the column to operate in, that would be
great for other spreadsheets where it would not necessarily be column E.

A B C D E
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 MR8M

MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050006 ME21N

MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO


No luck finding an answer? You could always try Google.