Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Delete Matching Cells


I have four columns of info. Two are check #s and amounts from the bank and two are check #s and amounts from a database. How can I delete check #s(along with their amounts) that match? Is there any way to detect check numbers that match but amounts that don't?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Delete Matching Rows
Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.

IE: I have two worksheets, each have identical row headers, with 5 columns each.

Company Load Date Load Time Load Description Amount ?Report?
Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1
Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1
Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1

If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.

Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.

Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.

I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.

View Replies!   View Related
Delete Rows Not Matching Criteria
I have been using the code found here

Sub DeleteRowsFastest()
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.............................

to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.

View Replies!   View Related
Macro To Delete Matching Values
Column A Column B
1 b
1 1
1 2
3 4

I need a macro that if value in column b matches with value in column a, delete it both the value in column b and a and put the deleted value into column c. now my value in my columns is a combination of numbers and letters and it can have this characteristic too: `2076 or `FI7890

View Replies!   View Related
Delete Rows Matching Cell, Across All Worksheets
in VBA, I would like to do the following:

Delete the entire row where any cell in that row is equal to cell "b15" in worksheet "Metal Type".
I would like it to run across all the worksheets hidden or unhidden, in the workbook, (except for worksheet "Metal Type").

If possible I would also like it to prompt the user to accept the contents of cell "b15" in worksheet "Metal Type" or enter a new string.

Also can it alert the user when "no matches were found"


View Replies!   View Related
Search For Matching Text, Then Delete Rows Above It
Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

ExampleCell1
ExampleCell2
ExampleCell3

ExampleCell1
ExampleCell2
ExampleCell3
UID = example


That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".

Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.

Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.

View Replies!   View Related
Delete Rows Not Matching 3 Static Criteria
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.

Sub TestDeleteRows()

Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With

View Replies!   View Related
Delete Rows Not Matching Date Variable
I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook.

I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this:

Sub Macro1()

Dim RptDate As String
Dim RptMonth As String
Dim iLastRow As Long
Dim i As Long
RptDate = Range("B1").Text
RptMonth = Range("B2").Text
Workbooks.Open Filename:=(RptMonth)
Sheets( Array(RptDate, "Total Database")).Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 8 Step -1
If Left(Cells(i, "A").Value, 4) <> RptDate Then
Rows(i).Delete
End If
Next i
End Sub

It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.

View Replies!   View Related
Adding Search Function To Delete Matching Records..
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.

View Replies!   View Related
Compare 2 Columns Delete Rows With Matching Values
I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.

View Replies!   View Related
Delete Matching Debits & Credits (reversal Entries)
Q:How to delete reversal entries?

I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.

Example:
A B
Name Amount
1)Mr. A 2000
2)Mr. B 6000
3)Mr. A -2000
4)Mr. D 4000
5)Mr. A 2000

Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem

View Replies!   View Related
Fill Cells Based On Corresponding Cells Matching 3 Criteria
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View Replies!   View Related
Variable # Of Cells That Need Formula Matching # Of Cells
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.

I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.

View Replies!   View Related
Matching 8 Cells To Their Counterparts
I have a table which is 8 columns wide by approximately 1000 (+) rows. I am struggling with the following;

if I type in seven numbers somewhere I need to get returns on a full 8 cell match? (and possible partial, but exact matches to 4+out of 8 numbers) All the numbers in individual cells are no larger than 99 and (in theory) are arranged from left to right in ascending order.

Can anyone point me in the right direction?

I don't want to use auto filter as I am just looking for a reference that I am searching the correct number. Does that make sense?

View Replies!   View Related
Matching Cells (2003)
I have a list of unique numbers on sheet 'List'. On another sheet, 'Data', I have a columns of data and column E contains a number which will match a number in the list on sheet 'List'.

What I want to do but don't know how to is loop (i think) through the numbers in 'List' and every row in 'Data' that contains a number is moved so that the data is grouped according to the number. i.e all rows with 78878 will appear beside each other and so on.

Neec code that i could use that will automate this so the user can just press a button and the data is reorganized?


View Replies!   View Related
Macro For Matching Up Cells
I am struggling with a macro that I’m trying to create. Basically what it would do is it would start at cell K1 on a sheet called “Database” and look for the value contained in cell K1 in the entire sheet.

It would paste all matches until no more are found into a sheet called “Matches”. Cell K1 would also be moved to this sheet if matches were found against it. It would then move on to the next non-empty cell in column K.

If no matches were found it would move on to the next non-empty cell.

View Replies!   View Related
Highlighting Matching Cells
im looking for a formula that will find matching dates in column a and then i want to see if column b has a matching time in it

View Replies!   View Related
Matching Cells Query
I have 2 worksheets - main and match. In the main I have something like the following:.....

There are 200 entries in the main work sheet. In the match work sheet I have something like:......

I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")

View Replies!   View Related
Countif For Non Matching Cells
How do I write a nested COUNTIF formula ( assigning a value of 1 for each cell) for a range of cells that does NOT contain "", "OFF", or "REQ OFF" ?

For example:

A row of 20 cells (A1:A20), 5 are blank (""), 3 contain the text "OFF", 4 contain the text "REQ OFF", and the rest contain anything other than the three values just mentioned.

I need a sum number for all the cells in that row that contain any other value. In this example, the output value should be 8. Out of 20 cells in the row, a total of 12 cells had either "", OFF, REQ OFF, and the remaining 8 had some other value that did not equal those.

Some sort of COUNTIF(AND(... Can't seem to figure this one out.

View Replies!   View Related
Delete Rows Matching Criteria & Move Rows
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:

1- all rows with the value "TOM" in column C will have to be deleted.

2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).

View Replies!   View Related
Consolidate Matching Rows And Delete Duplicate Rows
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5

Should be processed and come out like this:.......................

View Replies!   View Related
Finding Matching Cells From Two Different Columns
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.

View Replies!   View Related
Finding Matching Values In Cells
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?

For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9

something to return the value 9

View Replies!   View Related
Matching Cells And Replacing Data
We have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.

The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.

I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.

We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......

View Replies!   View Related
Sorting Based On Matching Cells
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue

View Replies!   View Related
Matching A Cell To One Of Four Named Groups Of Cells
I am trying to write a formula that will return a result based on which list a cell is found in.

Column A comprises of aisle/position information. The values range from B01 to B65, C01 to C65 ... H01 to H65 (Like aisles/shelf positions in a supermarket). This is not a static list, as the information in column A will change from week to week. It will be sorted prior to trying to work with it. The first three characters of the values in A will be what I'm comparing to the list. There is a fourth character that determines further details about the item that are not pertinent to this issue.

I need to match the value in column A to one of the four groups of values below and have the correct color display in column F. The correct color would be the name of the list that the value is found in.

Blue (K2:K61) contains the values B01 through B25 and C01 through C25.

Red (L2:L80) contains the values F01 to F21, G01 to G23 and H01 to H35.

Yellow (M2:M95) contains the values B36 to B65, C45 to C65 and D23 to D65.

Green (N2:N124) contains the values E36 to E65, F34 to F65, G34 to G65 and H37 to H65.


It probably boils down to a nested IF statement that checks to see if LEFT(A2,3) is found in any of the four lists, but I just can't wrap my head around how to write it.

View Replies!   View Related
How To Use SUMIF On Consecutive Cells Matching Criteria
How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:

Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.

View Replies!   View Related
Array With Values From Cells Matching A Criteria
How do you load an array with values from cells matching a criteria?

I want to look for the number 1 in row 5 and put the Adjacent values in Row 2 in an array.
So here my array should have 1,3,6

Dim CountOne As Integer
CountOne = Application.WorksheetFunction.CountIf(Worksheets("Tool Administration Form").Range("B8:AE8"), "1")

Dim All As Variant
Dim rCell As Range
Dim ArrCnt As Integer
Dim lArrVar As Long
ArrVar = 0

Do
For Each rCell In Worksheets("Tool Administration Form").Range("B8:AE8")
If rCell = 1 Then
ArrCnt = ArrCnt + 1
All = Cells(-3, 0).Value
End If
Next rCell

Loop Until ArrCnt = CountOne
I keep getting errors... I don' tknow how to fill the array properly wiht the values in the cells.

Example:
A B C D E F G
1
2 1 2 3 4 5 6 7
3
4
5 1 2 1 3 3 2 1 4



View Replies!   View Related
Row Matching Macro. (match Duplicating Cells)
I m writing a macro that will match duplicating cells in an excel spreadsheet. Here is an example.

BEFORE MACRO

id1 | example 1 | example 2 |
id2 | example 3 | example 4 |
id1 | example 5 | example 6 |
id2 | example 7 | |

AFTER MACRO

id1 | example 1 | example 2 | example 5 | example 6 |
id2 | example 3 | example 4 | example 7 | |

View Replies!   View Related
Copy Matching Cells In Series From Column
in writing a macro that does the following.

Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.

View Replies!   View Related
Copy Cells Matching Multiple Criteria
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.

Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.

As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.

View Replies!   View Related
Count Cells Matching Any Color In Specified Range
Im trying count cells based on the cells color. My sheet has multiple blocks filled with different colors. I have searched all of the regular sites out there for vb code related functions, but I can find one that will let me select a range of colors and a range of cells to be counted.

View Replies!   View Related
Find Rows With Cells Matching Criteria
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.

The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)

Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".

Step 2:......................

View Replies!   View Related
Highlight Cells Matching Selected Cell
I would like to select a cell inside my workbook and when it is selected it automatically highlights similar value cells in the same workbook. Is that possible? A Look at the attachment should give you a much better idea as to what I am talking about.

View Replies!   View Related
Count Cells Matching Multiple Criteria
I am using the below code to count records in a range that meet multiple criteria.

myCount=0
For Each dCell In Range("MyRange")
If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1
Next dCell

Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).

View Replies!   View Related
Copy Comment Of Matching Cells To One Cell
I have two ranges AA:DD and F:G, I have to find matching pair in AA:DD and in F:G and copy H and add as a comment to D, its hard to explain. see my attached workbook.

View Replies!   View Related
Fill Cells Matching ComboBox With TextBox
I have a userform with text boxes and a combobox referencing a named range in the spreadsheet. What I'm trying to accomplish is when I click on a name in the combobox, I would like the text I'm adding to this name to be on the same row. Currently a new row is created with all this information instead of being added to the existing row and I end up with duplicate names in the combobox.

View Replies!   View Related
Generating Urls In Spreadsheet Based On Matching Cells.
I am trying to create a spreadsheet for an online gift registry. They require that the spreadsheet have the photo's url's in a column. I already have the spreadsheet filled with my data. In the spreadsheet, Column D is filled with unique numbers, some with parenthesis, (ex. 52011, 52011(2), 34132, etc.)

I also have a folder full of images that are similarly formatted as such
"...imagesrand_name_52011.jpg". (They will be moved eventually to a webserver.) Each number in the column may or may not have a corresponding image. And the images may or may not have a corresponding number in the spreadsheet. Is there a way to generate a url automatically in a column that corresponds to the image with the matching number? And if it doesn't, just leave it blank?

View Replies!   View Related
Count Number Of Cells With Partial Matching Strings
1. Counts the number of cells in a column with the same 18-character text string(Full String is 24) using a RIGHT( function.

2. Returns a 1 if there is 1 and a zero if the count is greater than 1.

View Replies!   View Related
Find Cells Matching Value & Return Adjacent Details
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.

View Replies!   View Related
Bank Reconciliation - Many To One Matching And Partial Cell Matching
programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

View Replies!   View Related
Search To Find Matching Cells And Copy/transpose Adjacent Data To Original Sheet?
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.

As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.

If it's any help, there are a maximum of 9 matches for a single search term in the real document.

Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!

View Replies!   View Related
Matching Cell Formats Based On Matching Cell Values
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.

I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.

View Replies!   View Related
Delete A Value From Cells
I have a workbook with parantheses in each cell. The contents are text and an example is;

Basketball-WNBA (71)

I want to delete the (71) from every cell. The numbers are NOT the same but will always be at the end with () around them. How can I do this without going to every cell? There are about 40,000 cells in the workbook..


View Replies!   View Related
Delete Cells With #N/A!
I have used Vlookup to search and to return specific values to column 77. For those values that are not defined, Vlookup will return #N/A ( null value). Can I know how to write a macro that can go through each cell in column 77 and delete those cells that have null value? I tried the following code but it doesn't work

Sub clear_contents()
Dim rwindex, colindex
colindex = 77
For rwindex = 658 To 663
With Worksheets("Invoice").Cells(rwindex, colindex)
If .Value = Null Then
Worksheets("invoice").Cells(rwindex, colindex).ClearContents
End If
End With
Next rwindex
End Sub

View Replies!   View Related
Delete Cells, Shift Everything Else Below It Up
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...

View Replies!   View Related
Delete First X Characters In Cells
I am trying to write a macro that will go through each cell in a column with the following format "| 12- 4" or "| 60-11" and will remove the first to character "| ".

There numbers are lengths, the first being feet the second inches. I would like to achieve in a separate column the numerical length (with decimals) multiplied by 1%. My code is as follows I just need help on the conversion.

Sub newLength()
Dim LR, lrow As Integer
Dim feet, inches, Line As Double

LR = Range("B65536").End(xlUp).Offset(1, 0).Row
For lrow = LR To 2 Step -1
If Cells(lrow, "D") <> Cells(lrow - 1, "D") Then
Rows(lrow).Insert Shift:=xlDown
End If
feet = Left(Cells(lrow, "E").Value, 4)
inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do.
Cells(lrow, "F") = feet + inches
Next lrow
End Sub

View Replies!   View Related
Macro To Delete Cells
I am creating a macro that pulls data from a pivot table and organizes it on the same sheet as the pivot table. Once I get my data organized, there are several rows that contain data that I dont need and I want my macro to delete this info.

The catch is, since I have the macro placing data on the same sheet as my pivot table I cannot just delete the whole row so I am trying to think of a way to identify the rows that have unusable data and then have the macro just delete the cells in that row without affecting the pivot table.

I used a formula in the last column of my wks to mark unnecessary rows with a "D". Is there a way I can program the macro to identify the "D" and then delete a selected range of cells in that row - and then have it loop through all the rows of the spreadsheet?

View Replies!   View Related
Delete Empty Cells Only
There are 5 worksheets in a workbook one (Names) has 10 columns,
200 rows, I need to eliminate all blanc cells on this worksheet,
to make a list.
I don't want delete entire row,
only the empty cells in each column.
All rows have data but in different columns.

View Replies!   View Related
Compare Three Cells And Delete Row
I am trying to write some code that will compare three cells on a row and if they match then it will delete the row.

Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)

I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.

The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.

View Replies!   View Related
Delete Cells On Specified Condition
I have some data in numaric form from range a2:f20000 which I have got as a sum from a sheet and I want if any cell (a to f) from row 2 to 20000 has sum up value blank or 0 then it deletes shift xlleft.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved