Filling Rows According To Cell Values And Deleting Rows With No Values
Jan 25, 2013
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Test Tracking Sheet.xlsx
View 4 Replies
ADVERTISEMENT
Nov 28, 2013
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
View 1 Replies
View Related
Apr 8, 2009
I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.
I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?
View 9 Replies
View Related
Jun 9, 2009
What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.
What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....
View 6 Replies
View Related
Nov 13, 2012
I've tried to write a VBA code that copies a value down and deletes empty rows. The macro needs to copy down the value in column A from the above row if the current column is empty but delete a row if column B is empty. The following code will copy down the value but will trigger a 400 error and won't delete any rows. The desired effect is to have column A completely filled and the empty row (Row 5) and the row containing "Page 1" (Row 6) deleted.
Code:
Public Sub fillType()
Dim LastRow As Integer
Dim Count As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For Count = 0 To LastRow - 1
'If A column is empty, copy value from column above
[code].....
View 2 Replies
View Related
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
Aug 6, 2012
I have a sheet whereby in column A i have company names and column B how much is invested in this company so:
A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10
What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.
View 9 Replies
View Related
Mar 31, 2008
I have a spreadsheeet with values in columns F:K from row 10 onwards
I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted
View 9 Replies
View Related
Jun 26, 2009
In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.
the row of numbers looks like this ....
View 9 Replies
View Related
Oct 24, 2006
How can I delete rows in a worksheet using values in a certain column as criteria?
Example: I want to delete all rows that show a value of zero in column k. How is this done?
View 6 Replies
View Related
Nov 22, 2012
I have a userform containing 3 textboxes, to calculate derivatives.
User enters two integers in textbox1 and textbox2; and to textbox 3, I need to transfer these values but with a little issue.
For example, user entered 3 to textbox1 and 5 to textbox2. In textbox 3, it needs to show 3x^5.
For example, user entered 7 to textbox1 and 2 to textbox2. In textbox 3, it needs to show 7x^2.
So "x" and "^" are our defaults in textbox3.I tried to transfer the numbers that the user enters to cells A1 and A2, -I don't know if this works- but I don't know how to call them to change the text in the textbox.
View 2 Replies
View Related
Jun 3, 2009
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
View 13 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Oct 13, 2012
Within one column, there are cells with values A, B, and C for example. I want a loop that
- delete all rows from the first row to the row where A occurs,
- delete all rows BETWEEN where B and A occurs.
- and then finally, delete the single rows where C occurs.
This might seem I'm just deleting everything but obviously there are more "A" and "B"s in the column.
1
2
3 A
4
5 B
6
7 A
8 B
9 C
10
So in this example, rows 1 to 3 are deleted since I want to delete row 1 to the row where A is; rows 5 to 7 are gone (B to A); then finally row 8 is gone since C is there. Whether it's A/B inclusive doesn't matter..I just would like to see the (simple) logic.
View 9 Replies
View Related
Jan 28, 2010
I have rows between the Words "vendor" and "sales order" both words are in column A. The number of rows is never constant but needs to loop through the whole document. Is there a way to make a macro to do this?
View 9 Replies
View Related
May 15, 2014
Here i am having functional code for checking 4 cells data based on conditions
1) if more than 2 cells is empty means the entire row has to delete-how to make it?
2) i want to fill the empty value in relevant cell at the time of execution of function i.e.
for example cell1 cell2 cell3 cell4 cell5
value value blank cell blank cell result(coming based on my condition but the empty cells or not filling)
how to fill the empty cell
I am attaching my file for reference : if_custom.xls
View 3 Replies
View Related
Jun 20, 2008
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
View 2 Replies
View Related
Nov 14, 2009
I need to separate multiple values separated by comma in a single cell into individual rows. However, I also need that the data in the adjacent columns of the original cell also to be repeated in the new rows. The problem is illustrated in the attached file.
that the table is just a sample and most of the tables I work with contain at least 15,000 rows,
View 7 Replies
View Related
Mar 12, 2013
I'm looking for a formula to sum three adjacent cell values in a specific row and then sum up all of the rows.
A B C
1 0 2
3 1 2
4 4 0
For example, the value for the above would return 17 = [(1+0+2)+(3+1+2)+(4+4+0)]
View 5 Replies
View Related
Jul 2, 2014
I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.
Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE
SUPPLIERSCODE
WAREHOUSE STOCK
And then cut out the rows in which G
View 9 Replies
View Related
Feb 1, 2009
Is there any way to hide a row if columns 6 and 7 both show either a "-" or a number lower than 0.5? Cells in both rows contain a VLOOKUP formula.
View 9 Replies
View Related
Sep 8, 2007
I need to hide rows on a worksheet based on entries from DATA VALIDATION dropdown lists on another. Due to the fact that all the entries are on a different page, and there is no active cells or entries made on the "second" worksheet, the WORKSHEET_CHANGE events will NOT work. Trust me, I have spent two days searching the net for a possible answer and trying every trick I could find. Not to seem redundant or a pain, but right from the git-go, FORGET the WORKSHEET_CHANGE event as an option. I need a way to fire the macros from a formula change in a linked cell, and it won't be an active cell, nor will there be any kind of entry made on that page at all. ALl the entries (choices) will be made on another sheet, but the result needs to be hiding the rows not required based on the choices I make. There are over 300 lines and when I am done there will be only approximately 35-40 left. Right now the people using this are actually making copies of the workbook, and then deleting the worksheets they don't need (I am hiding those with an array) and then deleting the lines they don't need on the worksheet remaining. (Stupid and slow I know, but I didn't develop this workbook, I'm just trying to bring it out of the stone age) Here is some sample code with what I want to do, except it is of a WORKSHEET_CHANGE nature and will not work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("INPUT!M6"), Target) Is Nothing Then
Rows("11:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("31:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("11:30").Hidden = False
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("11:30").Hidden = True
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("31:50").Hidden = False
If LCase(Range("INPUT!M6").Value) = "0" Then Rows("11:50").Hidden = False
End If
End Sub
View 9 Replies
View Related
Mar 1, 2008
I have a number of rows that I want to have duplicated X number of times (and altered) where X is found by looking at certain cells within each row.
There are four numbers in each row, and I want to split them up into multiple rows each with three zeros and one one.
I would like to convert data from this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 3 0 1 0
Mike 0 1 1 2
etc.
...to this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 0 0 1 0
Mike 0 1 0 0
Mike 0 0 1 0
Mike 0 0 0 1
Mike 0 0 0 1
etc.
You can see that the W, X, Y, and Z columns from the four new Mike rows sum to equal the values in the original Mike row (0, 1, 1, 2), but everything has been split so that each row just has a single one in it and three zeros.
Does anyone have an idea of how to do this? Thanks Auto Merged Post Until 24 Hrs Passes;I thought of another way of putting it that may be easier to understand.
Given an input row of "George, 4, 7, 3, 2", I would like the output to contain 4 rows of "George, 1, 0, 0, 0", 7 rows of "George, 0, 1, 0, 0", 3 rows of "George, 0, 0, 1, 0", and 2 rows of "George, 0, 0, 0, 1".
View 3 Replies
View Related
Aug 13, 2014
I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.
[Code] ....
View 6 Replies
View Related
Feb 20, 2009
Column B contains geographical Areas. Column C contains a list of business departments.
North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View 4 Replies
View Related
Apr 8, 2014
I am trying to hide rows based on values in a cell in that row. I have formulas in column xea that yields a 0 or 1 and I want to hide all rows with 0.
The code below works but I have to run the macro. I want it to happen automatically when the cell values change and I have not been able to figure it out.
Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("xea1:xea350")
[Code] ........
View 9 Replies
View Related
Jul 22, 2014
I'm finding it hard to explain what I am trying to do (which may be why I can't find a solution through Google), so I have an example. I am using a much larger spreadsheet than this sample(18,000 rows)
ID
First_Name
Last_Name
Company
[Code]....
I want to highlight rows if there are multiple instances of ID and # values - I included a column at the end showing which rows in this data set would be highlighted.
View 2 Replies
View Related
Jul 7, 2009
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
View 6 Replies
View Related
May 1, 2008
I need to CUT & PASTE records according a cell value. For example:
Columns A2 to AC1000 there are some values. In each rows of AD2:AD1000 the values repeats with the result something like "YES", "NO", "N.A.", "LESS CHANCE". So, I need to shift (to another sheet) only the entire rows with values of "NO" and "N.A."
View 6 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
