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


Deleting Groups Of Rows When Specific Text Data Is Populating Row?

Feb 14, 2013

I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.

The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"

View 8 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
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

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 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:


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


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 View Related

Count The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns

Dec 5, 2008

I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

View 4 Replies View Related

Text File Import Produces Unwanted Blank Lines Between Rows Of Data

Jan 19, 2010

After doing some searching I found a macro here which imports multiple text files into one workbook, keeping each text file as a seperate worksheet, each worksheet named the same as the original file name (minus the .txt extension).

However, it adds a blank line between each line of text. So I get this:

Pinging with 32 bytes of data:

Reply from bytes=32 time=232ms TTL=62

Reply from bytes=32 time=62ms TTL=62

Reply from bytes=32 time=65ms TTL=62

instead of this:........

Pinging with 32 bytes of data:

Reply from bytes=32 time=232ms TTL=62
Reply from bytes=32 time=62ms TTL=62
Reply from bytes=32 time=65ms TTL=62

This doesn't happen when I use the import wizard, or when I just open the file from the File -> Open menu.

My text files are always the same format (number of lines, etc.), so it's easy to delimit and insert forumlae as needed, but those extra lines are breaking a macro I've previously written to parse the data.

View 9 Replies View Related

Deleting Rows That Contain Certain Text

Aug 1, 2014

I have a sheet where individual actions are listed and then subtotaled.

ElementError Code
Action NAC-01
Action NAC-02
Action NAC-07
Action NAC-08
Action NAC-14

[Code] .......

I need to remove all the subtotal rows.

View 2 Replies View Related

Deleting Rows That Contain Certain Text

Aug 23, 2007

I have a spreadsheet nearing 10000 various products with columns A-M.
I need to do the following:

Delete the entire row and shift up
If column H contains "this text" or "File Server Manager" or "ABT". Column H being the product description.

Basically, I have customers that are authorized only to sell certain products. So instead of filtering threw and saying, ok he cant sell "File Server Manager" delete that entire row. OR he cant sell these 10 products cause it says oracle in the product description then delete that row.

I was researching the fourms and saw something on AutoFilter. Will that work?

It was something along the lines of:

.AutoFilter field:=1, Criteria1:="ENTER TEXT HERE"

I appreciate all your help!


View 9 Replies View Related

Deleting Rows With Certain Text

Feb 21, 2008

I have downloaded my pay pal transactions and need to go through and delete rows of debits that are not debits for my business, but do not know how to do this automatically. I know very little about excel so I hope this can be fairly easy.

View 9 Replies View Related

Deleting Rows With Text Or Blanks

Sep 19, 2006

Id like to thank ByTheCringe2 for getting me to this point. Now I need just a little bit more help before I can take over and work on it on my own. I have attached a sample sheet of the current problem im having. I messed up in the previous post by not giving an example of text underneath invoice.

This is what I need.

1) I looked in to this and I think the best way to go about this would be to say that if a cell in Column H does not have a numeric value, then to erase the entire row. The reason for this is that Column H is the only column that remains constant in that it is the total balance of the invoice therefore if it doesn't have a balance or there are letters in the cell, it is not a valid cell and not a valid row. I highlighted the example of my problem. This is the formula im using...

=IF(ISERROR(MATCH($E20,'Imported Data'!$E$6:$E$30,0)),"",IF(INDEX('Imported Data'!K$6:K$30,MATCH($E20,'Imported Data'!$E$6:$E$30,0))=0,"",INDEX('Imported Data'!K$6:K$30,MATCH($E20,'Imported Data'!$E$6:$E$30,0))))

View 9 Replies View Related

Resample Data: Total Number Of Points Reduced By Averaging The Data Not By Simply Deleting One Of Every Four Rows

May 16, 2006

I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.

View 5 Replies View Related

Formula To Move Data On Same Row And Deleting Empty Rows Between Data

Jul 17, 2014

formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.

What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.

See annexed file for expected results.I have preference to have a formula much more.

View 7 Replies View Related

Deleting Rows With Unwanted Data?

Dec 6, 2013

What I need to do is under Document Number (column D) is to delete the rows that do not start with "RM", "AG" or "MA"and then move the rows up.

View 2 Replies View Related

Deleting Rows And Moving Data..

Jul 26, 2009

I have created a spreadsheet with 4820 rows of data (4821 for computational purposes, the last row being blank) with 5 columns.

1. every third row is blank and it needs to be removed (3, 6, 9,...4818, 4821). Once this is accomplished,

2. I need to move the data that is now found in the even rows, column E moved to the above, current odd row, column F. (ie. E2 to F1, E4 to F3, E6 to F5,...E4808 to F4807...,E4820 to F4819 (however it won't go this high). then

3. I need to delete all the current even rows, as the data I need will be in the odd rows.

View 4 Replies View Related

Deleting Rows Of Invalid Data

Dec 24, 2007

I have a database in excel format containg 20,000 + rows with multiple columns, one of the columns contains web addresses in the format [url].

I have a list of around 3.500 web addresses that are no longer valid ie thay dont work, I want to filter the original list to delete all the rows that contain an invalid web address including all the other data in the row, does anyont know a way to do this without having to do it manually one at a time?

View 9 Replies View Related

Deleting Rows Of Data The With Use Of Boolean Logic

Feb 6, 2009

I've got a worksheet with five columns of data. The headings run from a1:e1

The number of rows can be anything up to 200,000 rows (I'm using Excel 2007).....

View 6 Replies View Related

Moving Data And Deleting Rows, Part 2

Jan 7, 2010

This is similar to a previous post, in July, which was masterfully solved. I have tried to adapt the previous script, without success. Therefore,

I have copied data from a screen and entered it into Excel 2007, which is attached as 'snohomish sample.xlsx The data starts out (sheet 1) in 8 columns (A-H), and only in the odd rows (1, 3, 5, ...) What I wish to do is the following:

Move 'sheet 1 column B' to 'sheet 2 column A'. This is a date which needs to be in mm/dd/yyyy form.

Move the last 14 digits in 'sheet 1 column F' to 'sheet 2 column B'. This needs to be text so that I don't loose any of the zeros.

Move 'sheet 1 column E' to 'sheet 2 column C'.

Leave 'sheet 2 column D' blank as I will be entering a currency amount after running the module.

Move 'sheet 1 column A' to 'sheet 2 column E'. This also needs to be text as I can't loose any zeros or have it in scientific format.

The rest of the data on sheet 1 is not needed on sheet 2.

I will be entering data in 'sheet 2 columns F-K' after running the module.

I am attaching 'snohomish results.xlsx' which shows what I hope the results to look like (note the sample results are on sheet 1 of a different file).

View 6 Replies View Related

Deleting Rows With Data In Black All At Once In Excel

Apr 5, 2012

I have a document that has 634 rows, some rows with data in red and some rows with data in black. I only want the rows with data in red. How do I get rid of all the rows with data in black all at once? I don't want to go through the document line by line.

View 2 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

Excel 2007 Extreme Delay When Deleting Data In Rows

Jul 9, 2008

Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.

Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.

We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.

Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.

View 9 Replies View Related

Hide Blank Rows - Non Text/color

Dec 28, 2009

I received this code which hides blank rows within a range.

View 12 Replies View Related

Find Text And Insert 6 Blank Rows Above It

May 15, 2013

I have spreadsheet with data all over. I want a macro which identifies the first cell (in Column A) which has the text "BNY" and insert 6 blank rows above the text "BNY" (First text in the Column).

Once, it is done - the macro should also assign names to the last inserted row.

Column A - Should reflect "Bank", Column B should reflect "Field1", Column C should reflect "Field 2" and Column D should reflect "Field 3".

And after the names are assigned - It should also highlight the last inserted row in Yellow.

For Example:
Raw Data (Snap):
Field 1
Field 2
Field 3


[Code] .....

Output (After Macro):
Field 1
Field 2
Field 3


[Code] .......

View 5 Replies View Related

Combine/merge Row Cells Text Between Blank Rows

Jun 14, 2009

I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.

for example





would become

aa bb
ww xx zz
gg hh ii jj

I have over 30000 lines so doing it by hand is not an option.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved