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?
I am trying to create a macro to search through a workbook and delete "empty" cells, leaving just the range of cells with valid data. So far i've come up empty handed. I have attached a sample workbook with a spreadsheet on it that has the empty cells I speak of. Press CTRL+END to see where it takes you in the sheet and it will be well beyond the range of data I have inserted. The reason I need to do this is because I gets spreadsheets back from clients that need to be imported into SQL and it is very time consuming to have to go through each workbook.
View 2 Replies View RelatedI 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 Relatedformula 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.
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 RelatedI 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.
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).....
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).
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 RelatedIn 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?
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 RelatedI 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"
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.
I would like to create a message box that will tell the user if the input they've loaded is valide base on two criteria.
1. The sum of the cells from O2 to O3000 is equal to zero.
2. There are no zeros in the cells from B2 to B3000.
Currently, Im running a button macro. When this button is clicked, the table from "315 Employee Data" will copy the names from column C to "315" sheet B12 onwards.
Now, what i want is when I update the employee data on "315 employee data" sheet, I want "315" sheet to automatically match the names from "315 employee data", delete and add names when I add or remove employees from the column the next time i click on that button again.
After the above is achieved, when i add new employees,run the macro and it displays the updated list of names, I want "315" sheet column A to do automatic numbering.
I have attached the file for your reference.samplesample.xlsm
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI 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
I have a userform that has validations in it. One of the validations is to ensure the user has entered in a correct date. Right now if the user has the correct date in the form, but decides they no longer want to add any data into the spreadsheet using the userform, they can simply click the Close Form button at the bottom of the form and the form will close out.
I just discovered however that if the user inadvertently put in an incorrect date and then attempted to close the form, the validations will not allow the user to close the form until they have corrected the date. I would like to set up my close form button to close the form no matter what data has been entered into the form. Basically have it so the close form button overrides any validations within the userform coding. Currently I only have unload me within the cmdClose_Click() sub routine, It looks like this:
[Code]....
Could I use something to the effect of clearing all fields when the close form button is clicked...
[Code] ....
Would this work???
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
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.
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
I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.
I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?
I have Excel 2007. I saved in both xlsx and xlsm formats
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction
01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
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 RelatedI 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.
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] .........
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
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.
Here's my problem. I have the following table:
View 5 Replies View RelatedI 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