Macro Deleting Merges Rows?
Apr 18, 2014
I am looking for code to put in a macro that will go through a tab of data and delete the rows that have merged cells in them. The number of rows that there will be will never be a constant because the people using it will be pasted in information from another source, deleting the lines that have merged cells then run a macro with stuff that needs to be done to the info, but I would like to save them the step of delete the rows that have cells.I think it is Office 2013 I am using. I am not at that machine right now so I can't check.
View 4 Replies
ADVERTISEMENT
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 Replies
View Related
Apr 5, 2007
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
View 3 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
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
Mar 19, 2009
I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?
View 2 Replies
View Related
Jul 25, 2012
I'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.
View 9 Replies
View Related
Jul 28, 2007
I am looking to find and delete email addresses in this case *@aol.com. I have only one column which is full of email addresses, I just want to delete all of the @aol.com ones in this case. This is the macro I am trying to use, but it does not work.
For Each cell In Range(Range("a2"), Range("a65536").End(xlUp))
If cell = "*@aol" Then
Range(cell, Cells(1, Rows.Count)).EntireRow.Delete
Exit For
End If
Next cell
View 3 Replies
View Related
Jan 20, 2014
I want to write a macro that would delete the rows if the sum equal to zero and the description is identical. For example:
Column a: Column b:
100 Apple
200. Apple
300. Grape
-100. Apple
-200. Apple
400. Cherry
-400. Cherry
The answer should be 300 grape.
View 2 Replies
View Related
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
Apr 1, 2014
I have a excel file with more than 10 sheets and every sheet has a title on its first three rows.
I want to delete first three rows in all sheets except parent sheet .
View 4 Replies
View Related
Oct 29, 2009
Is there a way for a macro to be not active when trying to insert a row or a way to have the macro understand that it's just a row shift? I'm trying to have a time stamp that anyone changes the value in a column. The following code generates an error 1004: application or object defined error when I insert or delete a row.
View 14 Replies
View Related
Sep 18, 2013
My excel contains first 5 rows of heading information and the rest of the rows contain the data. I want to select a specific row from the data and run a macro that would delete all other data containing rows.
This is what I have now:
Code:
Sub DelRows()
Rows(6 & ":" & ActiveCell.Row - 1).Delete
Rows(7 & ":" & 65000).Delete
End Sub
Everything is fine if I select any data row except the first one (R6). In that case, the macro doesn't work as it should, deleting a row from the heading ones.
View 1 Replies
View Related
Jul 7, 2008
Create a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:
=average(a1,a2,!#REF!,a4 .........)
View 9 Replies
View Related
Apr 6, 2014
I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....
View 13 Replies
View Related
Jun 6, 2013
I'm working through a filter macro to delete unecessary rows of data from my dataset.
- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"
The macro filters the range fine, but when if comes to deleting the row, the macro stops.
Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then
With Range("CanadaData")
[Code] ......
View 2 Replies
View Related
Mar 18, 2014
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
View 1 Replies
View Related
Mar 3, 2008
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 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
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
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
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
Feb 21, 2009
Here's my problem. I have the following table:
View 5 Replies
View Related
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
Jun 26, 2008
I am having trouble with the .SpecialCells(xlCellTypeBlanks)
What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.
Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"
View 9 Replies
View Related
Nov 9, 2008
I have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.
View 10 Replies
View Related
