I have a worksheet that contains 3 colors. I sort the worksheet by 2 cell colors, moving them to the top.
I would like code to delete any rows whose cell color in column A is white, leaving only my two other colors.
Can this be done?
i dont know what that 'white' thing called. After i copied the data from other sources, i cant delete the 'white' thing. even i delete the whole sheet, the 'white' thing is still there.
View 2 Replies View RelatedI'm working on a project and am particularly new to the whole excel world (especially with macros). Currently, my boss has some data that needs to be refined and organized. Until I get a sense how to have a macro do everything all at once, I plan on have macros do each step more quickly.
Right now, the macros my boss has given me allows the data to be organized into clusters. Each cluster is separated by client IDs in column C. Each cluster is separated by a blank row. We are looking for clients who have not paid their dues and are marking their unpaid dues with colored rows. The dues that are paid are left as white. However, there are clients who do pay their dues, leaving their entire clusters white. I was wondering if there is a macro to remove those fully white clusters.
The entries are made in Columns A thru C. In Columns F and G I want any value over 100% to have a red background, any value less than 100% to have regular formatting, and I'd want percentages to have white font if an employee is deleted from Column A.
Here's an example of how I have the formatting set up:
Condition 1: Formula is =$E$2="" --->white font
Condition 2: Cell value is >1 ---->red background
Condition 3 Cell value is </= 1 ---->normal format
this is the code i'm currently using to enter a formula into a cell
View 9 Replies View RelatedUsing VBA, how can I determine if a cell contains only white spaces and NO valid characters?
View 2 Replies View RelatedI was wondering it were possible to reference an error. I'd like a cell to have white text if $O19 produces a #N/A error. I tried formula
=($O19=#N/A)
and the formula
=($O19="#N/A")
I would like to create a VBA code where it will delete the entire column if the cell value is equal to value in D2
For example:
Sub Delete_Columns()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("G2:S43"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = D2 _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireColumn.Delete
End Sub
======
But I think this line is wrong but I am not sure how to fix it - If (cell.Value) = D2 _
I have a spreadsheet with Ethnicity (White, African American, hispanic,
Asian, other) in column A and Time (hh:mm:ss) in column B.
I trying to determine by how many White, for example, have spent less than 5
hours, >5 hrs but less than 10 hr, etc.
My plan is to make a chart of the data.
I've been looking at sumproduct and COUNTIF but I can't quite figure it out.
I am attaching a sample file for reference.
On the sheet titled Price List, in row 1, I have an =IFERROR(HLOOKUP( formula to show what I need. The Data Pull sheet can be updated using a third party software, and retailers can be added or deleted. The purpose is that if a retailer is deleted from the Data Pull sheet, it will automatically be removed from the list on the Price List sheet, rather than just showing a bunch of N/A's in every cell. Then if it is re-added in the future, it will repopulate on the Price List sheet.
The formula I have in row 1 is my manual way of showing whether or not the retailer was deleted. If it contains EXCLUDE, you can go in and manually delete the column. However, I would rather have a macro do this.
Note: The "Bread Type" column will ALWAYS be in row 1, BUT will ALWAYS be in a different column letter, so I would like this macro to find the "Bread Type" column by name rather than by column number. All criteria should NOT be case sensitive.
In the example below, rows 2, 4 & 5, have the text "Wheat" or "Rye" in the "Bread Type" column, therefore, all 3 of these entire rows should be deleted. Yes, I do mean the ENTIRE row, wipe these rows off the face of the earth
I need a code that will delete an entire column (J) if J6 is blank.
View 3 Replies View Relatedway to find blank cells in a column(s) and delete the rows corresponding to the cell number. I found this code in one of the previous threads on ozgrid but it just doesnt work for me because it is slowing me down. Please help (I am working on around 60000 rows in Excel 2007)
Sub DeleteBlankARows()
With Application
.Calculation = xlCalculationManual
. ScreenUpdating = False
Dim r As Long
For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1
If Cells(r, 11) = "" Then Rows(r).Delete
Next r
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
a way to do the reverse viz. for a particular column, I'd like to delete the row corresponding to the non-blank cell in the column.
Yes thats exactly what I am looking for. deleting rows if a cell in a particular column is not empty/has data.
View 7 Replies View RelatedI'm making five charts on one sheet and there's so much white space. I removed the border, got rid of the plot area, and have tried to send the charts back but there's so much white space. QUESTION: Is there a method to elimite white space on a chart/graph?
View 2 Replies View RelatedI have a spreadsheet that I need to rearrange to improve readability. I have an example attache, which is easier to understand than describing it, but I'll try:
I want to start in a1, move to a2
copy a2 to b1
delete line 2 (the previous lrow 3 now becomes row 2
if a2 > 0.01, skip it and
move & select cell a3
... and repeat the above logic, on the next series of cells...
E.g.
copy a3 to b2
delete line 3 (the previous lrow 4 now becomes row 3
if a3 > 0.01, skip it and
move & select cell a4
...and repeat the sequence again.
I have this sheet(sheet2) with some data , I have 2 macro, my problems at this time is that I am looking for macro that delete only the blank rows that under the rows with {SELECT ....} and the under the blank rows with {CELL-ENTER......} and all the rest blank leave in place , I am also looking for option to integrate the new macro with the existing one and come up with one macro that I can refer to click button , in case that it is unfeasible , it's ok with me , I just will call the other macro at the from the first macro that I have.
View 4 Replies View RelatedI'm trying to delete rows which has columns that appears to be blank. Below code worked fine but it took about 15 min for the macro to work through the spreadsheet.
View 2 Replies View RelatedIs there a way to delete or clear a row on exit if no end date has gone into it. i.e. if the user clicks on the X button in the top right hand corner.
Every time the user clicks on start the date goes into the next empty row, if finish is clicked then the end date will go into the cell next to it. See example below. Here the user has started and ended correctly
column
AB
StartEnd
24/10/09 24/10/09
24/10/09 24/10/09
In the example below the user has forgotten to click on the finish button when they first clicked on start, they exited excel instead and no end date was inputted. When the user clicked on start again the start date went to the row below, however as there was no end date the last time, this time when they clicked on end button, then end date went to the first BLANK CELL
column
AB
StartEnd
24/10/09 24/10/09
24/10/09
I would want the delete row or clear row option, which ever would work best, to happen if the user clicked on the X in top right and corner or Exited excel via File.
Ideally I would like the row cleared and not deleted, I have read Clare Watts thread and we both seems to be having a similar problem, I have used some of the examples from her thread but non seem to work. So I now wish to go with a delete or clear row option on exit, if possible.
The row changes every time. The column for deleting or clearing would be A to AB. The end date goes in to Column B
So if the user clicked on start but did not click on the Finish Button then the last row with no end date would be deleted or cleared on exit from column A to AB
column
AB
StartEnd
24/10/09 24/10/09
24/10/09(This is deleted or Cleared of exit via X top right hand corner)
Delete entire Row if cell in column contains "Dog" in it.?
Example,
duck321
dog123
cat123
dog123
duck321
cat123
so after it would look like this
duck321
cat123
duck321
cat123
I have working data on list sheet and added data on compiled sheet.
I need to match duplicates and delete these rows from compiled sheet.
I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied.
I'm looking for a macor that can do this work for me without losing data on list sheet.
This data reaches into the thousands and I'm looking to run this off the CASE column.
My code is always failing on the findnext method. I'm thinking that the findnext method fails because it loses it's cell reference when the row gets deleted.
Sub findwbs()
Dim rngFind As Range
Dim strFirstAddress As String
I am trying to delete a rows that contains a certain value. When I run the command from VB, it works. When I run the command from the tools menu in Excel, it works. When I run the command by clicking the command button located on the spreadsheet, it returns an error message "Delete method of range class failed" and highlights the line
Cells(x, 2).EntireRow.Delete
Private Sub CommandButton1_Click()
'Deletes expired coupons
x = 9
Do
If (Cells(x, 7).Value < Cells(2, 3).Value) Then
Cells(x, 2).EntireRow.Delete
Else
x = x + 1
End If
Loop Until Cells(x, 2).Value = ""
End Sub
How do I remove the white space in charts?
Whenever I draw a pie chart, I see lot of empty space around the pie. When I try reducing the size of the chart, pie also shrinks. I want to be able to reduce the extra white space 'around' the pie in the chart.
I have a spreadsheet where the user wants gray and white lines to occur starting from row 36. Meaning row 36 is grey row 37 white 38 grey etc. to make it look like old accounting paper. The problem is there is a macro that runs to hide the zeros so I cannot preformat the rows because they will change along with the worksheet. Also, the coloring should stop when there is nothing more in the rows, usually around row 100.
View 9 Replies View RelatedI'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
[Code] ....
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
HTML Code:
Sub DeleteEmptyColumns2()
Dim Cell As Range
For Each Cell In Range("4:4")
If Cell = "0" Then
Cell.EntireColumn.Delete
End If
Next Cell
End Sub
I am attempting to remove columns in my table of data, using the above code seems to remove some them but not all, is this the best way to loop through a range?
If cells in column A3:A10000 are blank / unpopulated I would like to delete the blank rows.
View 6 Replies View RelatedI have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................
I have a excel doc with 8000 names I need to delete the people who are listed on 7 rows.
There are no blank rows and Colume A has the names sorted.