Remove Blocks Of Rows
Apr 27, 2006
I have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
View 9 Replies
ADVERTISEMENT
Jul 16, 2014
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
View 1 Replies
View Related
Jul 1, 2009
What I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.
Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.
View 4 Replies
View Related
Apr 21, 2006
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
View 9 Replies
View Related
May 11, 2006
I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?
Sub InsertRows()
Dim lRow As Long
Dim r As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Application. ScreenUpdating = False
Columns("M").Insert
[m3] = "temp1"
Range("M4:M" & lRow) = "=LEFT(A4,5)"
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown
Next r
Columns("M:N").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:= _
"H:HOME imcEXCELCOUPON_COUNT5_MAYREFORECAST.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.SelectedSheets.PrintOut copies:=1
End Sub
View 3 Replies
View Related
Nov 15, 2006
Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.
For Example:
Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................
View 9 Replies
View Related
Apr 4, 2014
I am using the following code to remove an entire row if a specific cell in that row contains "remove":
iLastRow = Cells(Rows.Count, "D").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(Cells(i, "D").Value, 4) = "remo" Then
Rows(i).Delete
End If
Next i
However, from what I can see, it starts from the bottom and look upward, removing each line as it finds it. The problem is, there could be upward of 500 lines that need to be deleted. This coding looks and removes lines one at a time and can take a while. Is there any way I can get this done faster?
During the previous steps of my macro, I sort the column (Column D) that has the "remove" in it, so all of the "remove"s are adjacent to each other in Column D of my file.
View 5 Replies
View Related
Oct 31, 2013
I'm puzzled on coming up with a way to delete a set of rows in Excel. I have two columns like below and would like to remove the rows with the later year. Is there a formula or vba I could use to run on 5000 records?
Current table:
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
Desired table: [this would exclude rows where record source = MCL-INV-SUR09
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
View 4 Replies
View Related
Mar 29, 2007
How to remove unique rows from excel-table and leave only dublicates? any macros?
View 9 Replies
View Related
Jun 10, 2008
I have a customer database that is sorted by phone number. How do I remove duplicate rows.
View 9 Replies
View Related
Jun 20, 2008
I have data in a 3 column by massive amount of rows (10000+) periodically there is about 6-11 rows of 'header' data which needs to be removed. this is a report run monthly so doing this over and over drain time. I need to create a query that will find all of these rows and delete the entire rows.
View 9 Replies
View Related
May 15, 2006
Sub Remove_E_H_Ts()
Application. ScreenUpdating = False
Dim x&
For x = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(x, 2)
If Left(.Value, 1) = "E" Then ' i need to add If Left(.Value, 1) = "E" or "H" or "T" Then
.EntireRow.ClearContents
End If: End With: Next x ' after clear contents how can i remove blank rows ?
Application.ScreenUpdating = True
End Sub
i am trying to make this line of code work below....
If Left(.Value, 1) = "E" Or "H" Or "T" Then
then also after clear contents how can i remove blank rows ?
View 6 Replies
View Related
May 25, 2006
I have a macro that performs Index&Match formulae on another workbook. This leaves me with some #N/A results. I then require the macro to delete all rows that contain #N/A. I have tried to identify these via
If Range("E" & myLoop).value = "#N/A"
and
If Application.WorksheetFunction.Isnumber(Range("E" & myLoop).Value) = False
but each one gives me a Run Time Error 13 (Type Mismatch) because, even though the results are now values and not formulae, it sees '#N/A' as an error (Error 2042).
View 3 Replies
View Related
Nov 11, 2006
I have a large file and I am doing a subtotal here. I want to do further processing of the "subtotal" data (basically, I want to sort the list on the subtotalled sum values) and I would like to know if there is any method to delete all rows except the "subtotal" rows from the sheet other than the method described here, in another post in 2003.
Copy subtotals only - visible cells too complicated [SOLVED]
This works fine but I have the word Total in some of the columns I am sub-totalling.
Also, some of the entries in that column are very long strings to start with and when the subtotal is made by Excel, the details are truncated (probably to 255 characdters) and the word Total is not there.
View 7 Replies
View Related
Aug 12, 2008
ive got a spreadsheet with records such as this
james - smith - leeds- 01535 - 26/02/1983 - bd21 6ls
steven - smith - bradford - 213789 - 28/01/1982 - bd33 5th
james - smith - leeds - 01535 - 26/02/1983 - bd21 6ls
steven - king - manchester - 213789 - 28/01/1982
andrew - wright - bingley - 36473 - 12/01/1981 - bd23 689
what im trying to do is to check all of the rows for duplicates in such a way that (if any row has the matching colums 1,2,5 and 6 then all but 1 of the rows will be deleted.
i have a few thousand entries and am just trying to get rid of all the duplicate rows. The problem is that the duplicate rows are not IDENTICAL for instance soime of them may have spelling mistakes hence i cannot just do a straight forward if row = row then delete.
View 4 Replies
View Related
Jan 12, 2010
I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:
Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A
I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.
For example:
If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.
View 10 Replies
View Related
Jan 30, 2014
I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
View 7 Replies
View Related
Feb 7, 2014
I have a spread sheet which has a range as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P254
P378
P2568
P454
P389
P385
I need to get the output as shown below which is for Batch number P2 I need to include Item 1 (568) in one row and delete the other P2 row. similarly I need to do for P3. The end result will be as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P256854
P3898578
P454
View 8 Replies
View Related
Feb 24, 2014
I can't get rid of the alert "Delete entire sheet row" using DisplayAlerts = False. Here is my code:
[Code] .....
View 4 Replies
View Related
Mar 9, 2014
I need to remove the you tube link which is on some rows
Row 9:
[Code] .......
Attached File : Description.csv‎
View 10 Replies
View Related
Dec 13, 2007
Im not strong in excel, that why Im here. So:
I work every day with big amount of item numbers and lists in excel and I need some macro or code to automatically remove rows, containing unwanted text.
Example:
MEMORY DIMM 512MB PC3200 DDR
MEMORY DIMM 512MB PC6400 DDRII
MEMORY DRIVE FLASH USB2 2GB
MEMORY MINI SD 2GB W/ADAPTER
MEMORY SECURE DIGITAL 2GB
MEMORY DIMM 1GB PC6400 DDRII
MEMORY DRIVE FLASH USB2 1GB
MEMORY DRIVE FLASH USB2 1GB
So I paste the text from my database, and want to automatically delete rows containing DIMM string. How can I do it?
p.s. is there also a way to leave only the rows I want?
View 9 Replies
View Related
Nov 20, 2008
I'm trying to loop through each row in a listbox on a userform and remove the value if it isn't equal to the year shown in a CBYear. i can't sem to figure out what to use in my for statement to point the VBA to the Row as it were
View 2 Replies
View Related
Dec 15, 2012
share a code to remove BLANK ROWS.
I have data on ROW#1, #5, #10...etc,etc.. I want to remove rows in btwn (which is blank) Instead of doing manually
View 2 Replies
View Related
Aug 8, 2007
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
View 9 Replies
View Related
Aug 24, 2008
I have 6 columns on my data sheet. F column is Amount column.
If there are rows where all the columns A,B,C,D and E duplicate then I need that row to be deleted, BUT before that the value from the column F should be summed to the duplicating row's F column.
I attached a simple example file, where you can see my point better. (I need the code to work with thousands of rows, and not only with 5 rows which i put on the axample)
View 9 Replies
View Related
Oct 25, 2006
I have a spreadsheet with various hidden rows and columns that I do not wish the user to have access too as it contains too much detail for their purposes.
Copy/paste and copy/paste special also copies across the hidden columns and rows so what I need to do is to copy it to a new workbook but then remove the hidden rows and columns and just delete the existing formulas in the cells which total the data and insert a new autosum.
View 3 Replies
View Related
Nov 29, 2006
I currently have dozens of sheets within a workbook and require empty lines to be removed from each page. I already have a working code to delete the lines but I now need to be able to apply this macro across all sheets in one go. I have included a sample excel sheet with the ‘delete blank rows’ macro and also the code I have been trying to use (without success) to apply the macro across all sheets. The apply macro to all sheets I’ve been trying to use is:
Sub AllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
Next ws
End Sub
View 3 Replies
View Related
Nov 30, 2006
I am trying to delete rows - based on 1 field in each row not being equal to ANY of the values stored in an Array. e.g. my array has 3 values, CAT,DOG,MOUSE
cells are:
A1 = CAT
A2 = HORSE
A3 = DOG
A4 = DOG
A5 = BIRD
i want to delete rows not containing CAT,DOG,MOUSE. I think the autofilter could be the trick but cant quite get my head around it.
View 5 Replies
View Related
Jul 24, 2007
i'm trying to form a macro that allows the user to enter a date and have excel auto filter for all dates prior to (or ON and prior to if it makes it easier) that date, and delete all rows with that auto filter criteria in place. i'm working with a old auto filter macro i put together, but cant seem to get the criteria to work with, say, <7/14/2007 for example.
Attribute VB_Name = "modDeleteVal"
Sub DeleteRowByValue()
Attribute DeleteRowByValue.VB_Description = "Macro recorded 7/9/2007 by KHenzel"
Attribute DeleteRowByValue.VB_ProcData.VB_Invoke_Func = "V
14"
'
' DeleteRowByValue Macro
' Macro recorded 7/9/2007 by KHenzel
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else...
View 6 Replies
View Related
Aug 17, 2007
I have a large file with many item numbers that run down column B. Here is how it is set up. It has a header that says store one. Under store one it lists all item numbers in that store. Then under that it says store 2 and list all item numbers in that store and so on. There are a total of 90 stores with an average of 40 items per store. I have certain item numbers that I want to see but I want to delete all other numbers. Is there a way I can tell it to Delete all items except for the following, and then list the numbers I want it to keep?
View 3 Replies
View Related
