Over 1 million tech questions and answers.

Excel Macro to delete row with duplicate cells

Q: Excel Macro to delete row with duplicate cells

Does anyone have a macro that will delete row that have duplicate cells in same row other columns?

RELEVANCY SCORE 200
Preferred Solution: Excel Macro to delete row with duplicate cells

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel Macro to delete row with duplicate cells

Hi

Are you looking to delete rows that have duplicate data but the duplicate data is in different columns?
Can you give a small example?

If the duplicate values are in the same column then you can use the 'remove duplicates' tool.
Select the rows/columns then go to DATA and you should see the REMOVE DUPLICATES tool.

James

Read other 5 answers
RELEVANCY SCORE 98.4

Hello, Can anyone help me. I need to add a macro that can iterate through a number of worksheets and delete all the data from cells that are unprotected.

I have tried a few things but being unable to find an diagram of the excel object model with properies and fields has slowed me down, if anyone can solve my query and point me to such a document that would be excellent.
 

A:Excel macro, change delete from unprotected cells.

You can use the cells "locked" property to determine if the cell is protected or not.

You can can use VBA to select the cells or you can manually select your range of cells and use a simple loop to take appropriate action. How many workbooks and/or sheets are we talking about running this code on? If you provide more details about your process we can give you more code showing how to loop through each sheet in the workbook or even how to loop through an entire directory of workbooks.

Code:

For each vCell in Selection.Cells
If vCell.Locked then
'Your code here
End If
If Not vCell.Locked then
'Your code here
End If
Next

Rollin
 

Read other 3 answers
RELEVANCY SCORE 90

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)

SM
 

A:Solved: Copy selected cells to chosen cells in Excel with Macro

Read other 14 answers
RELEVANCY SCORE 89.6

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks
 

A:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Code:
Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5

Else

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Replace with your code to execute if cell contains formula
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If

Next vcell

End Sub

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 86

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

A:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 82.8

I have rows of data with duplicate cells in each row.

I do not want to delete any rows.

I want to delete only duplicate cells in each row.

There are many ways to delete dups in columns. I do not want to delete rows. I do not want to delete rows if dups appear in the row. I want to delete the dups going across the sheet from left to right, row by row, going down the sheet from top to bottom.

For example, I five rows...

fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep

I want to delete the duplicate cells in each row. I want to end up with the following...

fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat

Just to clarify, if a cell is repeated in a row, one unique cell should be left in the row in which the repeated cell appears. I only want to delete repeated cells in the rows in which the repeated terms appear, leaving only one unique where the repeated cells appear. If the word "cat" appears five times in row 2 and three times in row 3, after the macro is run, the word "cat" should appear once in row 2 and once in row 3.

In some cells, spaces do appear because in some cells, the cells contain a short phrase (dog bone) rather than just... Read more

A:Want to delete duplicate cells in a row, row by row, going down the sheet.

Read other 11 answers
RELEVANCY SCORE 80.8

So I'm trying to find computers that list multiples of the same or similar software in Active Directory. I have everything in a spreadsheet as below. [No, I don't have better access to AD]. Sometimes the software instance is exactly the same, sometimes it's just a word in the instance that's the same.


Code:
WorkstationID Software
DDCHLK085887 3_FUJITSU_FI5015C_WN7 3_FUJITSU_FI5015C_WN7
DDCHLK087033 O_ACROBATPRO_11010_WIN 3_AUTOCADNOLIC_2012_AXP 3_MSPROJECTSTD_2010_WN7
DDCHLK088174 O_ACROBATPRO_11010_WIN 3_MSPROJECTSTD_2010_WN7
WDCHLK088317 3_ATTGNC_9210_AXP_GFE_23 3_ATTGNC_9210_AXP_GFE_23 3_ATTGNC_8110_A_AXP_GFE_23
DDCHLK089478 3_PROJECTPRO_2010_WN7 3_MSPROJECTSTD_2010_WN7
WDCHLK089659 O_ACROBATPRO_11010_WIN
Row 5 has three versions of ATTGNC, two of which are the same.
Row 6 has two versions of project.

What I'd like is for the duplicate software to be highlighted, or maybe a cell that says 'DUPLICATE!" or something. I can find exact duplicates, but what I can't figure out is how to discern between, for example 3_ATTGNC_8110_A_AXP_GFE_23 and 3_ATTGNC_9210_AXP_GFE_23. Any help is greatly appreciated. I've got several hundred computers to check and doing it by hand sucks!

Read other answers
RELEVANCY SCORE 80

I have an Excel (2007, running XP Pro) with 5 worksheets. When I type data in one cell, it sometimes duplicates the data in other worksheets. I think that when it does this, the file name at the top of the screen is followed by [group]. What might this be about please, and how do I stop it happening?
 

A:Solved: Excel: cells duplicate content in other tabs

If all tabs are selected, just click on any tab other than the current one and select back you tab.
If only selected tabs are selected, click on any tab outside the highlight group and then click back.
 

Read other 1 answers
RELEVANCY SCORE 78.4

Hi there,
I know this question gets asked in various forms time and time again but I'm too much of a novice to make those solutions work for me.
I have a list of data but each item is spread over two rows making it unsortable.

ie
_ A B C D E F
1 a b _ _ _
2 _ c d e f g
3 a b _ _ _
4 _ c d e f g

b and c are both in colum B
whereas i would like to have

_ A B C D E F G
1 a b c d e f g
2
3 a b c d e f g
4

or even without the empty rows but even I can fix that.

If its not too much trouble I'd appreciate an explaination with any code so that I can try and change it for similar problems in the future.
I hope this is clear. I thank you very much for your time.
 

A:Solved: Macro to move excel cells

Read other 9 answers
RELEVANCY SCORE 78.4

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 

A:Excel Macro to select specific cells

Read other 7 answers
RELEVANCY SCORE 78.4

This is just a curiosity post since I've been working a lot with excel lately. Is there anyway to create an IF formula to autofill cells based on a start date and then stop autofilling them on an end date? That probably seems really vague, so I'll attach an example.

In my spreadsheet, I have the costs per home on the right and a schedule on the left. Can I make an IF statement in the schedule that will read something like =if(B6=R7, Q7, ""), but then have the cost per month fill out the cells until Month 4?

I want to make a cash flow schedule that can be updated from as few entries as possible since I'm usually manually updating larger worksheets that become very convoluted.
 

Read other answers
RELEVANCY SCORE 78.4

Hello,
I am having some issues trying to figure out how to select multiple cells using macros. My goal is to be able to highlight a certain name in a column of names, and this name can appear multiple times and not always in a set range.

Example:
John
Larry
Shane
Larry
Larry
John
Larry

(The blue font indicates a highlighted cell)

Is their a way for a marco to look at all of the names in column A until "Do Until IsEmpty()" has been completed and have the specified name in the macro "Larry" stay highlighted to view individual rows of information. Also, these names will not have blank cells inbetween them.

Thanks for all the help!!!
 

A:Excel Macro Selecting Multiple Cells

Read other 6 answers
RELEVANCY SCORE 78.4

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

A:Excel macro: Return values from 2 cells on same row

Read other 9 answers
RELEVANCY SCORE 78.4

Scenario:
Excel spreadsheet 1 with 'X' number of lines in it.

Goal:
Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

Example:
First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x
x = "=COUNTA(CSEXPORT!C:C)-1"

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.

Thanks
 

A:Excel Macro that will copy formula 'X' cells

Read other 6 answers
RELEVANCY SCORE 78.4

I know this is probably very simple, and I am going to hit myself once the answer is found, but I cannot seem to think of the solution for the question below.

I need to highlight all the cells in a column that does not contain a zero. How would I go about doing this with code?
 

A:Solved: Excel Macro - Highlight Certain Cells

Read other 9 answers
RELEVANCY SCORE 78.4

two problems involving writing macros i would like to solve,

1st - want to write a macro to move to another worksheet, then edit a cell in that worksheet so that the value increases by one

2nd - would like write a macro - which again needs to move to another sheet, then copy a formula in one cell to another, to give two cells with same formula, and then fix one one of those cells (1st) so that it is no longer a formula - just a number
 

A:excel problem - macro to edit cells

You don't need to switch sheets in either case.

Sub Macro1()
Sheets("Sheet2").Range("A1") = Sheets("Sheet2").Range("A1") + 1
End Sub

Sub Macro2()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("A1").Value
End Sub
 

Read other 1 answers
RELEVANCY SCORE 77.6

Hi

This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
'
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
'
' Keyboard Shortcut: Ctrl+i
'
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Range("B8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Accounts September 2007.xls").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.H... Read more

A:Excel copy & paste Macro for selected cells

Read other 16 answers
RELEVANCY SCORE 77.6

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.
 

Read other answers
RELEVANCY SCORE 77.6

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
ItemA
ItemB
ItemC
ItemD
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.

Ads
 

A:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.
 

Read other 1 answers
RELEVANCY SCORE 76.8

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.
 

A:Solved: Simple Excel Macro to Fill empty cells

Read other 8 answers
RELEVANCY SCORE 76.8

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!
 

Read other answers
RELEVANCY SCORE 76.8

I have a macro that I recorded that does the following and I just need to change one thing for it to work the way I need it to do.

I select a list of cells in a different worksheet, and then run the macro.

This macro copies what I selected, goes to the worksheet named: Contact Fields Formulas. And then it pastes to Column D, starting in Row D4, the list of the cells that were copied.

Then, thru another macros I created before, when I am recording this macro, I run two other macros which one adds to the beginging of the words in the cells pasted, the " and the other macro I run while I am recording, adds to the end of the words in the cells pastd, the ".

Then the macro recorded goes to D4 and moves to E4, and makes E4 into E4=C4&D4, and it moves to E5 and then goes back up to E4, copies E4, moves to D4 and then down to the last cell with words that were pasted from D4 down, and then moves over to the cell to the right of the last cell in Column D that had the words as pasted, and then selects all cells going up to the E4, and pastes what was copied from E4.

Then, the E4 to the bottom of the last cell is still selected and it copies all of that, and then pastes it as a Paste Special with the concept Value, so that the Cells form E4 down, are the words of the two cells that were merged from the left of the E column cell.

So the question is, when I select another list of cells to copy and move over and do what the macro does, when in the area of

"moves... Read more

A:Excel Macro to Select Bottom Cell of LIst of Cells

Read other 8 answers
RELEVANCY SCORE 76.8

Hi

I have a following macro:
Code:
Sub BondPrices()

Selection.AutoFilter Field:=16, Criteria1:="BONDS"

Range("S2:S10000").Select
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
cell.Value = cell.Value / 100
End If
Next cell

Selection.NumberFormat = "0.00%"
Selection.AutoFilter
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.

Michael
 

A:Solved: Excel: Macro to divide specific cells by 100 with a filter

I've solved the problem myself by adding a line to the code that selects only visible cells:

Code:
Selection.SpecialCells(xlCellTypeVisible).Select

 

Read other 1 answers
RELEVANCY SCORE 76.8

I'm writing an Excel macro to find cells based on format. The cells in questions are subtotals so the format is bold. I'm using the following code:

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate

It works fine for most cells, eg it finds cells with values A1P-1MR Total, A1P-2RN Total, E1P-1MR Total

However it skips certain cells eg. E2C-2RN Total,
E3G-2RN, Total Grand Total
The format seems to be the same for all of these cells, so what's going on?
 

A:Solved: Excel macro selction.find skips cells

I just realized the problem. The number format was different.
 

Read other 1 answers
RELEVANCY SCORE 76

I have been given the task of updating our company price book, and am in desperate need of help with my macro.
Currently I have a macro that multiplies the numbers in the cell range by a percentage. However each page has prices in different cell ranges. I need help with an if statement to only select cells that have been formatted as currency (or at least cells that have a number in them), and to skip over blank cells, as well as cells with letters or letters and numbers in them.

Here's what I have currently

Sub Macro1()
'
' Macro1 Macro
'
[A1:H50] = Application.RoundUp([A1:H50*1.02], 2)

'
End Sub

Thanks
-SasukeSama21
 

A:Solved: Excel Macro needed to only select cells formatted as currency

Read other 16 answers
RELEVANCY SCORE 76

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

Read other answers
RELEVANCY SCORE 76

I found many macro showing how to find duplicate values and then delete the row. But I need a macro to delete all the duplicate values from columns A to E & G without deleting the rows. Like from example A to example B on the attached Excel spreadsheet. Your help will be greatly appreciated.
 

A:Macro to delete duplicate values without deleting the row

Read other 16 answers
RELEVANCY SCORE 75.2

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!
 

A:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

Read other 1 answers
RELEVANCY SCORE 75.2

I have an extremely large file and I need to match off values within rows that are equal to each other and delete the rows with matching values.

For e.g. Column D and Column H have the same numerical value
so the row needs to be deleted

Please help!
 

A:Macro to find duplicate values within same row and delete that row if they match

I forgot to mention that I do not want the Macro to include blanks
Only if there are actual numerical values in the cells of Column D and Column H
 

Read other 3 answers
RELEVANCY SCORE 74.4

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.
 

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Read other 11 answers
RELEVANCY SCORE 74.4

Hi, I have a macro in excel that automatically updates my outlook calender - I use it to keep track of actions/deadlines/etc...

The macro runs beautifully except... whenever I run it it creates duplicates of appointments it has previously made in my outlook calendar. Is there any thing that I can add to the macro below to ensure it doesn't duplicate?

Really appreciate any help on this one. Cheers.
Sub outlookexport()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i
Set olApt = Nothing
Set olApp =... Read more

Read other answers
RELEVANCY SCORE 73.6

Hi guys i am back yet again and prob not the last time as this site is the best.

I need a little help with my macro that i have attached and pasted below. In the attached excel file you will see multi records of the same thing. I need to only have 1 of each item listed and the duplicates deleted. Please Help

Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("B1:B2000").Rows.Count
Sheets("Sheet1").Range("B1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

If you need further information please let me know.
Thanks ahead of time for the great help you all... Read more

A:Solved: Excel 03, macro for deleting duplicate line items

Read other 6 answers
RELEVANCY SCORE 72.8

I have a list in column A down to row 59325. Many cells are highlighted red. How can I delete all red cells? THANKS!
 

A:Solved: Delete highlighted cells in excel

Read other 13 answers
RELEVANCY SCORE 72.8

I have a worksheet with entries in 12 columns and 200 rows. I have follg issues
1)I tried to select a range of entries, right clicked and tried to "select delete". But curiously Delete as an option (and Cut as an option) is greyed out. Not sure why it happens.But if I try to use Cells toolbar and select Delete--> shift Right it works.
2) I also noticed that whenever I open a workbook the "enable fill handle and drag and drop" feature gets unchecked in The Excel Options menu.
Office Button-->Excel Options-->Advanced Options

Are these because of any add-ins?
 

A:Excel delete a range of cells in a column

Hi, why don't you start by telling us which version of Excel you're using?
And about Add-Ins, have you installed any Add-In? Is so you could start by disablig them one at a time to see if it makes a difference.
 

Read other 3 answers
RELEVANCY SCORE 72

Hi,

I need help with a macro. So far the macro is operating as it should , creating MS Outlook 2010 calendar appointments using data from an excel data spreadsheet (data base). The issue I&#8217;m having is that every time the macro is run for a new entry, duplicate calendar appointments are created for the already existing data in the data base. I would like to have the macro do a search to ensure an appointment does not exist before creating one. I have read a few threads with similar problems but have had major issues incorporating the solutions to resolve the issue I&#8217;m having.

I have attached the macro in txt. format for reference.

Thanks for your help.

kkfonty
 

A:Solved: Excel macro updating Outlook calendar is creating duplicate appointments.

Hi
You can use this function to check if an appointment exists based on the Start Day/Time

Code:

Public Function CheckAppointment(ByVal argCheckDate As Date) As Boolean

Dim oApp As Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim oApptItem As Outlook.AppointmentItem
Dim oFolder As Outlook.MAPIFolder
Dim oMeetingoApptItem As Outlook.MeetingItem
Dim oObject As Object

On Error Resume Next
' check if Outlook is running
Set oApp = GetObject("Outlook.Application")
If Err <> 0 Then
'if not running, start it
Set oApp = CreateObject("Outlook.Application")
End If

Set oNameSpace = oApp.GetNamespace("MAPI")
Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)

CheckAppointment = False
For Each oObject In oFolder.Items
If oObject.Class = olAppointment Then
Set oApptItem = oObject
If oApptItem.Start = argCheckDate Then
CheckAppointment = True
End If
End If
Next oObject

Set oApp = Nothing
Set oNameSpace = Nothing
Set oApptItem = Nothing
Set oFolder = Nothing
Set oObject = Nothing

End Function

For this to work you need to have Tools/References/Microsoft Outlook xx Object Library enabled.
Then alter your macro like this

Code:

Private Sub cmdOutApt_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = "&q... Read more

Read other 2 answers
RELEVANCY SCORE 69.6

I have a macro that marks correct answers based on the correct value in B2 - B17 and AI2 - AI17 being the wrong answer.
Also if B2 - B17 is entered incorrectly that cell color turns red. If the answer is correct the cell color turns yellow and is added up at the bottom.

What my macro does not do is change the cell color to red if C2 - AI2 does not match B2 or AI17. Also if AI2 - AI17 is not entered correctly the cell color should change. Here is the macro. Also a file is attached with the macros. When the changes are made to the macro it should find mistakes in the attached file, some of the cells that are wrong are C2, D2, C3, D3, C15 there are others that are wrong.

Code:

Sub Update()
Dim Rng1 As Range
Dim Rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next

Set origRng = Range("B2:B17")
If origRng Is Nothing Then Exit Sub

For Each Rng1 In origRng
'Check contents of Rng1
If Rng1.Text = "" Then
'Rng1 is blank, skip this row
Range("B" & Rng1.Row & ":AI" & Rng1.Row).Interior.ColorIndex = 0
Else
'Rng1 is not blank, run match code
Set compRng = Range("C" & Rng1.Row & ":AI" & Rng1.Row)
bMatch = False
For Each Rng2 In compRng
If InStr(UCase(Rng2), UCase(Rng1)) = 1 And Rng2 > "" Then
... Read more

A:Highlight cells if they don't match one of two cells (macro)

Any ideas?
 

Read other 3 answers
RELEVANCY SCORE 69.6

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.
Thanks!
 

A:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

Read other 7 answers
RELEVANCY SCORE 69.6

Hi, I have a column in Excel that consists with time (minutes) from 0 to 80. I need a macro that will take each cell within a range (let say a column A1 to A6000) and do F2 and enter to each of the cells. I have too many cells to do it manualy..F2=>Enter. My lookup won’t recognize the numbers unless I do so. Can you help?

Thanks
 

A:Excel Macro that does "F2 and Enter" for a range of cells

Read other 6 answers
RELEVANCY SCORE 68.8

Hi.
I am using an Excel worksheet with several thousand records listed. On a daily basis, I get an updated list, but I need to keep hold of some of the 'old' records.
At present I'm copying the new list, adding it to the end of my existing list and then filtering it so that only unique records are visible.
Obviously, this means the list is ever-increasing with many duplicated records (hidden or otherwise).
Is there a way to simply delete the duplicate records (so I can just keep one record of each) rather than hide them?

Ta
Gram
 

A:Solved. Excel 2000 - delete duplicate records?

Read other 15 answers
RELEVANCY SCORE 68.4

Viewing a list of Excel macros by clicking Macros on the Developer tab in Excel 2010 displays the name of a macro that I did not create and want to delete. It appears as "chart.backup_q". The Delete button on the Macro dialog box is greyed out. Clicking the Edit button on the dialog box gives an error message "Reference is not valid". How can I get rid of this unwanted macro? I've not tried running it, because I have no need of the implied action and worry about what it might do.
 

A:Can't delete Excel macro

Go directly to the VBA editor (ALT+F11)
There should be a window in the top left corner named
Project-VBA Project. if not use CTRL+R and it will appear.
Find the workbook that contains the macro, clik in the +
sign on the Modules folder, find the module that contains
the macro rightclick and choose remove module...

That should eliminate it.
 

Read other 1 answers
RELEVANCY SCORE 68

In attempting to record a macro I accidentally created a couple macros for which I have no use. How can I delete them?
Tools / Macro brings up a list of macros, but among the action choices offered, the Delete button is grayed out.
 

A:Solved: How delete an Excel macro?

Read other 10 answers
RELEVANCY SCORE 68

Need help with a macro for a tool I'm developing for work. What I'm stuck on is something to remove inserted lines in a specific range. See attachment.

I've got buttons within column I8:I14 that will copy B2:I2 and instert below the button,shifting the content down. I need an option for the users to remove these lines somehow. I was thinking of having checkboxes within the inserted lines in column B so the users can select which rows they want deleted and then can click the "Delete Checked" button in I7 to remove. My attempts at getting checkboxes to copy over have failed and I'm not sure what else I can do.
 

A:Need help with an excel macro to delete row within a set range

Read other 10 answers
RELEVANCY SCORE 68

What is the best way to delete rows where both Column A & B are blank when the workbook is opened?

I have come up with this so far. Is there a better way?
Code:

Sub test()
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, 1) = "" And Cells(i, 2) = "" Then
Rows(i).Delete
End If
Next i
End Sub

 

A:Solved: Excel Macro and Delete Row

Read other 16 answers
RELEVANCY SCORE 68

I have to download spreadsheet on a weekly basis and I have to delete some column however the column that I need to delete always the same name but not located always in the same position (i.e. column tagline sometimes in column D sometime in Column F) Can someone help me create a macro that with delete the column using the name instead of the column Letter.

Thank you

Q
 

A:Excel Macro to delete column

Read other 7 answers
RELEVANCY SCORE 67.2

Hi All,

I have a spreadsheet containing 30 address blocks that I'm trying to clean up. A sample is attached.

All of the data is in column A. Each address block is 6 rows, like this:

Name
Company Name
Street Address
City/State/Zip
Phone number
Email Address

Between each 6 row block is a varying number of additional rows that might or might not contain extraneous information. I want to remove those extra rows leaving only the address blocks.

I'm thinking about counting the "x" number of rows between email address "@" symbols, and then just deleting X-6 rows. But I'm not sure how to accomplish this.

Any ideas how this might be accomplished, or any better suggestions to accomplish the goal?
 

A:Solved: Excel macro/vba to delete rows

"might or might not contain extraneous information"

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, "@") <> 0 Then
Cells(Cell.Row - 5, 1).Resize(6, 1).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell

If those are real names and addresses, you should remove your file.
 

Read other 2 answers
RELEVANCY SCORE 67.2

I find my Excel (ver 2003) won't record what I want it to do.
I paste in a range of data from another programme, at least 2 columns are partially numeric and each cell ends with a 'd' or a '*'. I want to remove the last character to make it purely numeric, so I need a macro that will take the cell my cursor is in, edit it and remove the last character, then move down a row - so i can do it again by running the macro again (I attach the macro to a button). Every attempt to record a macro records the cell I first start with, it doesn't edit the cell where my cursor starts, and it substitutes a specific value that happened to be in the cell where I started.
i.e. I had
23d
34d
56d
and I want
23
34
56
but the numbers may be any numbers plus a 'd'
Help appreciated, i can't fathom enough Visual Basic to rewrite the macro recorded.
Thanks for the help
 

A:Solved: Excel macro to delete last character

Read other 6 answers