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
ADVERTISEMENT
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
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"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
I appreciate all your help!
Sincerely,
James
View 9 Replies
View Related
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
Oct 22, 2008
I have an array that includes blanks in it. What is the formula to use to output the array of values without the blanks? Example: I have an array (Cells A1:A10) with thirty blanks within this 10 cell array. I wish to have the list of non-blanks outputted, presumably to the next column (Column B).
Array A:
Joe
Stan
Bill
Bob
Jill
Jack
Judy
View 3 Replies
View Related
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
Apr 21, 2014
I have a group of data and I want to delete every blank row and the row directly below it and there could be multiple blank rows in the data group.
Data
Data
Data
Data
Data
Data
Data
Data
Data
Data
[code]....
View 3 Replies
View Related
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
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Jun 26, 2014
i have this code which inserts blank rows in alternate rows,
Code:
Sub insertrow()
' insertrow Macro
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
For count = 1 To 20
If activecell.Value "" Then
activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
View 3 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Jun 9, 2009
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
View 9 Replies
View Related
May 25, 2013
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
View 4 Replies
View Related
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
Aug 23, 2012
I am trying to delete all the rows that have blanks in both columns "D" and "F".
VB:
With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then
[Code]....
View 4 Replies
View Related
Jul 13, 2009
i have table in the left side and i want to get the results in the table in the right side skipping blanks row price with taking in consideration blanks are formula contain ""
[IMG][/IMG]
View 9 Replies
View Related
Jan 9, 2008
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
View 9 Replies
View Related
Dec 11, 2008
I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.
A
B
C
D
E
F
G
1
ausbil........................
Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:
H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt
Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.
View 9 Replies
View Related
Sep 14, 2009
I have the attached a workbook (& the code) which merges the worksheets from 7 different workbooks together into a 'Raw data' worksheet in my master workbook. It is driven off a parameters table which allows you to select which workbooks/worksheets you want to include in the merge. - The code works fine however when it is copying the data from each worksheets it is just copying all the data as a 'block' copy where I need it do do it row by row becuase I dont want to copy the row if colum C in each row contains no data, ie. it is blank.
So in my code I need another loop which will: Copy data from column A - E for each row if column C is not blank. Paste into Column B - F in Raw Data worksheet (as column A contains the source filename) Loop until it reaches no more data. I adeeally just want to copy the specific range as above, not the whole row but either will do. This is my code, refer to attachment too which includes master workbook and an data entry workbook where the data comes from.
Sub OpenWorkbooks()
Dim Book_Name As range ' List of available books
Dim Sheet_Name As range ' List of available sheets
Dim dLastRow As Long
Dim oLastRow As Long
Dim sLastRow As Long
Dim DestinationSheet As Worksheet
Dim SourceSheet As Worksheet
Dim WorkBookList As range
Dim WorkSheetList As range
Dim WorkbookPath As String
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
'set source and destination sheet
Set DestinationSheet = Sheets("Raw Data")
Set SourceSheet = Sheets("Parameters")........................
View 5 Replies
View Related
Jun 7, 2008
I know how to do this as an if function, but do not know how to do that as part of a VB module. If No Value in Column R Then Insert "Phrase" Otherwise Do Nothing
View 3 Replies
View Related
Jun 4, 2014
combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
Desired output:
Item
Quantity
[Code]....
View 9 Replies
View Related
May 8, 2008
I have an excel file which contains 2 set of data. 1 with 14 rows and other with 20 rows. These are randomly distributed in the sheet and are abut 1000 of data set in a sheet with a combination of 14 and 20 row data set. Data is separated by blank rows, i.e. if it is 14 row data set it would have a blank after 14th row and similarly a blank after 20th row for a 20 row data set. Following the blank another data set starts which might be of 14 or 20 again. I have attached a sample sheet for reference.
View 5 Replies
View Related
Feb 22, 2007
I'm using excel to parse txt a file with account numbers in it. If it finds a dodgy number I want it to delete the entire line in the text file. I have manage to write the code that opens the file and locates the dodgy numbers, however I'm not sure whether it is posible to select the line and delete it.
View 6 Replies
View Related
Jan 27, 2009
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.
The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).
View 5 Replies
View Related
Dec 11, 2009
Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).
View 2 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Sep 21, 2009
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.
Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.
View 2 Replies
View Related
Oct 26, 2011
I want to delete all rows in the column of the ActiveCell when the ActiveCell.value < 0.01. Could you tell me why the code below doesn't work? It deletes the right rows but keeps looping without stopping
Sub DeleteZeros()
Dim Col As Long
Dim StopRow As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Range("A1").Select
[Code] .........
View 6 Replies
View Related
Apr 25, 2007
I have a macro that deletes rows. For some reason, the macro bombs out when the selection seems to be too big. Why is that?
deleterow_min = Sheets("Sheet1").Cells(1, 6)
deleterow_max = Sheets("Sheet1").Cells(1, 7)
Rows(deleterow_min & ":" & deleterow_max).Select
Selection.Delete Shift:=xlUp
View 9 Replies
View Related
Aug 5, 2009
In a part of my code I have something that will delete all hidden rows, like:
For Z = 1 To TotalRows - 1
If Cells(TotalRows - Z, 1).EntireRow.Hidden Then
Cells(TotalRows - Z, 1).EntireRow.Delete
End If
Next Z
I've done it in reverse to be quicker, but it's still quite slow. Granted, I am deleting several thousand rows, but surely there's a quicker way? Thinking about it logically I would presume that (in general) selecting stuff first in Excel and then deleting them appears to be quicker, but I'm not sure how to pull that off.
View 9 Replies
View Related
