Deleting Rows Where Cells In Multiple Columns Are Blank

Apr 17, 2013

I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.

Here's an example of what I'm working with:

5
10
15
20
50
2
4
6
8
20

This is what I need the final product to look like:

5
10
15
20
50
2
4
6
8
20

So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?

View 5 Replies


ADVERTISEMENT

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:

[URL]

Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

Deleting Duplicate Row (8 Cells) And Shifting Unique Rows Up - 3 Columns As Filter

Feb 4, 2014

I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).

As filter I would like to be used A,B,C columns.

EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1

Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

View 1 Replies View Related

Conditional Formatting: Reference Changes Adding/Deleting Rows/Columns Or Cells

Sep 26, 2006

I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.

View 3 Replies View Related

Transpose Duplicate Rows Into Columns Including Blank Cells

Mar 6, 2014

I found this great macro to use in a spreadsheet I'm trying to transpose from rows to columns, however, I need a place holder for blank cells: [URL] .....

I was able to use the macro in the last post by Ochenden but the blank cells need to have a placeholder.

How to change the macro or come up with a different script for me to use?

Attached is how I need the spreadsheet to look.

Script I used:

Sub aaa()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet2")

Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=OutSH.Range("A1"), Unique:=xlYes

[Code] ..........

Transpose_Duplicates_Examples.xlsx

View 2 Replies View Related

Delete Blank Rows (formula Not Deleting All Rows)

Sep 30, 2008

I have the following codes to delete all blank rows in column A

Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow

With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With

End Sub

Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.

View 9 Replies View Related

Deleting Rows Below 1st Blank Row

Aug 3, 2007

I've spent some time creating a module that speeds my every day tasks. I've set up Excel so that it is attached whenever I open a new spreadsheet ( Book.xlt). owever, I am frequently emailed spreadsheets from others and would like the functionality of my module without having ot import the module everytime. How do I force Excel to attach/import my module from my hard-drive to any Excel file I open? I understand naming conventions could come into play in the event someone emails me a spreadsheet that already has "Module1" attached. I'm sure I can come up with a unique name for my module should the automated import process actually be possible. How 'bout it folks?

View 6 Replies View Related

Deleting Rows That Contain Blank Info

Oct 23, 2012

How do you delete rows automatically even if there are not an equal amount of rows in between the rows I want to auto delete?

View 3 Replies View Related

VBA - Deleting Rows With Blank Values

Nov 13, 2006

I am trying to write a macro which will delete all rows where cells in column B are blank. I am new to macros and since I have only been able to record them, I am having a tough time hand-writing this one.

View 9 Replies View Related

Macro For Deleting Blank Rows ...

Mar 3, 2008

I am able create a macro using the find function to find the next blank row, but I would like to have it select a range of rows down that I can then delete. Each time I run the macro the next blank row may be different thant the last, so it can't be a set number of row numbers each time. I could also possibly use the print area function if it would be easier.

Here is what I have so far, what is in red is where I need it to vary from next active blank row down to R2001C14, and then delete all the active blank rows ...

View 9 Replies View Related

Difficulty Deleting Blank Rows

May 6, 2008

I am copying a range of rows from one sheet to another.

The rang consists of 11 rows; the first and last will always have data in them but the rest may or may not. Consequently, there are always a number of blank rows which I want to delete...

View 9 Replies View Related

Deleting Blank / Empty Rows

Feb 21, 2009

how to delete ALL the EMPTY/BLANK rows in an Excel sheet?

The sheet consisit of 18,000 rows.

View 11 Replies View Related

Insert Multiple Blank Rows At Designated Cells

Jun 13, 2014

I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .

Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.

This is a reformulation of this post: [URL] ...........

View 1 Replies View Related

Getting #VALUE Error When Deleting Blank Rows With VBA Code

Jul 13, 2009

I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).

So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...

View 14 Replies View Related

Deleting Blank Rows (without Any Text Or Data)

Jun 17, 2009

In a rage of rows I have some rows that are blank - without any text or data. Is there a macro I might enter that will look at the range of rows, determine which are blank and then delete the blank rows?

View 9 Replies View Related

Deleting Comments In Blank Cells

May 6, 2009

Is there a way to delete comments in a selected range of blank cells?

View 7 Replies View Related

Deleting Rows Based On Value In Columns

Apr 28, 2009

I'm sure this is simple code, but...

I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.

View 14 Replies View Related

Deleting Rows Based On 2 Columns

Oct 9, 2011

I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.

The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.

I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.

Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True

[code].....

View 1 Replies View Related

Deleting Duplicate Rows When ALL Columns Are Identical

Aug 8, 2007

I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]

Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?

For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.

View 9 Replies View Related

Deleting Rows Based On Columns Data

Jul 2, 2007

I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...

View 9 Replies View Related

Merging Columns / Deleting Rows And Summing Totals

Mar 22, 2012

My data in excel looks like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5

What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7

View 2 Replies View Related

Deleting Multiple Rows?

Mar 15, 2013

I have a large list of coordinates that I pasted from a website, and in Excel it has an emty row between every coordinate. I have about 2,000 cordinates, so ~4,000 rows. Is there a way to delete every other row besides manually?

View 1 Replies View Related

Deleting Multiple Rows?

Jan 18, 2013

In my spreadsheet, column A can contain the same word over several rows. If column B is blank at any time - I want to delete all rows that have the same word in column A.

Apple 1
Orange 2
Apple
Peach 1
Peach 2

So in this example - I want to delete rows 1 and 3 that show the word Apple.

View 2 Replies View Related

Deleting Multiple Rows With Multiple Criteria

Feb 6, 2007

I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.

Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................

View 2 Replies View Related

Lock Cell From Moving When Adding/Deleting Rows Or Columns

Jun 16, 2008

Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?

View 6 Replies View Related

Adding And Deleting Multiple Rows

Jul 9, 2013

I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.

So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.

So if I had the below sheet:

I could search by column a and add up column h to read like this:

View 1 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View 14 Replies View Related

Deleting Rows NOT EQUAL To Multiple Criterion

Apr 28, 2008

I have a spreadsheet with several thousand lines on it and I only want certain rows from it. Unfortunately I cannot use a sort as I have to keep groups of data in the same order (the line count in between the data that I need left behind isn't always the same so I can't go that route either). I was hoping there was some code to delete all rows other than those where the cell in column A contains either "NAME"; "Data1" or "Data 2".

I tried the code below but that didn't work.

View 10 Replies View Related

Getting Rows To Columns Macro Skipping Blank Rows

Dec 28, 2011

I have this in Column A, with about 120 entries:

Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1

Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2

Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3

I want this:

Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3

all the way down.

I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved