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


Deleting Duplicate Rows Based On Cell Values

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

Deleting Rows With Certain Values In VBA

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

Deleting Rows With Non-percentage Values

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

Copy Values And Deleting Rows VBA?

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.

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


View 2 Replies View Related

VBA - Deleting Rows With Blank Values

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

Deleting Duplicate Rows And Summing Values?

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:

IBM 20

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

Deleting Rows Where Values Are Opposite & Equal

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

Deleting Rows With Duplicate Values In A Column

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

Deleting Rows Based On Values In Certain Column

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

Filling Textbox With Values From Another Textboxes With Default Values

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

Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

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

Hide Blank Rows Based On Values In Other Rows

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

VBA Delete Rows In Between Cell Values

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.

3 A
5 B
7 A
8 B
9 C

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

Delete Rows Between Two Cell Values

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

Filling Empty Cell Value Based On Function Values

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

Delete Rows Based On Values In Rows Below

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

Separating Values In A Cell Into Multiple Rows?

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

Sum Three Adjacent Cell Values In Specific Row And Then Sum Up All Rows

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.

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

Comparing Cell Values And Moving Whole Rows?

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:


And then cut out the rows in which G

View 9 Replies View Related

Hide Rows Based On Two Cell Values

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

Hide Rows Based On Other Cell Values

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

Duplicate Rows Based On SUM Of Cell Values

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. 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

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

Hide Range Of Rows Based On Cell Values

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

Insert Number Of Rows Based On Cell Values?

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


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

Hide Rows Based On Values In A Cell Automatically

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

Highlight Rows Where Only Specific Cell Values Match?

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)



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

Insert Rows & Repeat Values Based On Corresponding Cell Value

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

Move Rows In Table Based On Cell Values

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

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Copyrights 2005-15, All rights reserved