Exception - Deleting Only Certain Cells

Feb 27, 2007

I am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following

If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If

And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.

View 2 Replies


ADVERTISEMENT

Making Exception For Locked Cells In VBA Codes

Jul 6, 2014

I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?

View 4 Replies View Related

Find With Exception

Oct 2, 2009

I have code that selects chart source data by searching the header row for dataTyp (in this case the dataTyp will be set to "IMP"). The target header cells look like "IMP 100Hz", "IMP 400Hz", "CAP 100Hz", "CAP 400Hz" etc. recently I inserted an averaging column at the end of each group of data columns. The headings for the new columns look like: "IMP AVG" "CAP AVG" etc. I need to exclude the ...AVG column from the source data selection. Code snip:

View 2 Replies View Related

Summing Column With Exception

Apr 11, 2009

I attached the extract. I am having the issue in the Total Workbook. Specifically, the Tax and Debit Column. If a company is on both workbooks (Insider Processor, Outsider Processor). The Tax and Debit should be charged once in the Total Workbook. But i don’t know how to get this happening

View 6 Replies View Related

Making An Exception Formula

May 17, 2006

I am trying to get 2 different formulas to run depending on the value of the 2 cells.
Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula.

I hope I did this right, if not please let me know and I will ask the question in a different manner.

View 7 Replies View Related

Add Exception To Sumif Formula

Oct 19, 2006

I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.

View 5 Replies View Related

Add Exception To Macro Displaying Pictures?

Feb 15, 2014

If Cell value in "C36" or "D36" is Yes, then a picture will be displayed (either Picture 1 or Picture 2). If Cell value is No, then the pictures won't be displayed.

The code I use:

[Code] .....

By default, all pictures on the Excel sheet are invisible.

My problem is, there are 3 pictures I'd like to display at all time because they are part of the document's decoration.

Therefore, I'd like to add an exception for the Picture A, Picture B, Picture C so they are not affected by the command "Me.Pictures.Visible = False".

View 3 Replies View Related

Exception To Forcing Text To Upper

Oct 20, 2008

I am using the below code to force a range of cells to change to Upper Case, what i am after is an exception to the rule for example the words Pick Up i don't want to force to upper case, is there VBA i can add to the below to achieve this?

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''
'Forces text to UPPER
''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("w6:w299")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0

End Sub

View 9 Replies View Related

Excel 2013 :: Automation Error Exception Occurred?

Mar 25, 2014

why a macro that works without a problem in Excel 2010 now crashes Excel 2013. The error produced says 'Automation Error Exception Occurred'. If I place a stop in the code and hold the F8 key down, the code runs perfectly (Excel 2013) but if I run it, it will crash. The code is copying a worksheet into a new workbook, adding 2 more worksheets, saving then emailing the workbook before closing. It goes through the routine about 7 times before crashing (sending only 7 email out). It's something to do with the email because I can bypass that code and it runs that part without a problem.

Our office just upgraded all the computers to Office 2013 and I've got to get this macro running again. I had to email 100 reports one-by-one today and I didn't like it.

View 6 Replies View Related

Index And Match To Check Data And Provide Exception Results

Mar 25, 2014

Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is

Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).

[Code] .......

Above is my item master

Data to check

[Code] ........

Example case: 1

Checking first row of data check

Article number: 2-50660147

Color code: 6

Size: M

Result: "Wrong size"

But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.

View 9 Replies View Related

Not Deleting Any Cells

Aug 15, 2007

The loop cycles through my sheet correctly but wont delete any cells.

View 10 Replies View Related

Deleting Visible Cells?

Apr 30, 2014

I have a data in which I have inserted a table with headers.

I then filter the type of data I want removed from the table using the drop down

Then goto special, visible cells only, press delete.

Now, the data is gone, but when I select all again to pull up the entire table, the data is gone, but rows are still there. So now I have tons of empty rows in between the rows of needed data. Any way of removing the rows in between as well as the data?

View 4 Replies View Related

Number Cells Deleting 0s

Jul 7, 2014

I need to store some numbers, all of which are prefixed by four or five zeros. When I format the cells as number, the zeros are erased. So, 0000012345 becomes 12345. The zeros are preserved when I store them as text, but I cannot do the functions that I need to do with the cells formatted as text.

How I can preserve the zeros in number format?

View 2 Replies View Related

Deleting The Name For A Group Of Cells

Feb 3, 2009

When you highlight a group of cells and give them a reference name- that name seems to be permanent. How do you delete one or add cells to the reference? Example attached book1.xls Cells A1-B3 are named "TheGroup".

View 2 Replies View Related

Deleting Specified Number Of Cells Using If

Mar 29, 2009

I have a column with "1" and "0". I'd like to have something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row.

Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc.

View 4 Replies View Related

Deleting And Inserting Cells

Apr 14, 2009

The delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.

View 11 Replies View Related

Deleting Cells With Userform

Dec 13, 2006

i have worksheet with peoples details in it... i have a userform with a combobox and a delete command button in it with the persons name in the combobox... and i cannot figure out how to delete the persons details with the combo box...

View 9 Replies View Related

Deleting Empty Cells ...?

Sep 22, 2007

Is there a way to delete empty cells, without having to do each one individually. I have 10,000 numbers with about 500 empty cells. I did a search, but found nothing...

View 9 Replies View Related

Deleting Rows With Cells Containing EL Or LE

Sep 1, 2008

I am trying to a method to delete rows that contain EL or LE in a specific cell in a set column as this will reduce my file down by nearly a third. Currently by doing it using a autofilter it kills my computer as there is so much data. Is there an easy way to reduce this?

Trade ID456465ELColumn B456456LE Column B8454321LEColumn B456456ELColumn B5464564LColumn B4564546LEColumn B

View 9 Replies View Related

Deleting Range Of Cells Using VBA

Jun 12, 2009

I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:

Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Windows(myFile3).Activate
Cells.Select.Range ("A78:K153")
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False

View 9 Replies View Related

Deleting Cells With Certain Values

Jun 29, 2007

I know this is an easy question....what syntax would you use if you want a macro to search Column A for every cell with specific text, and then clear that cell? I can't seem to get the syntax right to get it to do what I want.

View 2 Replies View Related

Deleting Entire Row That Has Cells With Certain Strings?

Mar 16, 2013

I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.

VB:
Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub

View 3 Replies View Related

Dictionary - Deleting Cells With Some Conditions

Jul 13, 2013

I have a VBA code which checks the value of two cells in "Sheet2", and when these values are in the fourth column of "Sheet4", then dictionary is saved and after that VBA delete Entire Rows in "Sheet4" which consists these values.

Please find the code below:

VB:
Sub dictionary1()
Application.ScreenUpdating = False

Dim dico1 As Scripting.Dictionary
Set dico1 = New Dictionary

[Code] .....

Now I wonder how to add more conditions like for example: "Delete all rows in "Sheet4" which have for instance in the 6th column text "Hello" AND which have in the 7th column value greater than 10". How can I change the existing code?

This is of course just an example, but the underlying question is how can I add to these dictionaries more conditions. Of course, we can still use "Sheet2" to add some value which we want dictionary to store.

I want to use for this only dictionaries because spreadsheet is large and filtering doesn't work at all...

View 7 Replies View Related

Use If / Then Statement - Deleting Range Of Cells

Jun 11, 2014

I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.

View 4 Replies View Related

Highlighting Or Deleting Specific Cells

May 16, 2009

I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc

Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).

What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether

View 8 Replies View Related

Deleting Extra Space For A Lot Of Cells

Jul 10, 2013

I just have a grid with a bunch of numbers listed and they all have one extra space at the end of the number. I have another tab with the same numbers but they do not have that extra space. I am using a vlookup formula and it is not recognizing the numbers because of the extra space. Is there some way to get rid of the extra space with a formula instead of going through job by job?

View 5 Replies View Related

Protect Cells But Allow Row And Column Deleting?

Nov 15, 2013

Can I protect cells so that people can't change them, but at the same time they are able to delete rows and columns?

If I remove the locking and use hide only then the cell why go empty if someone double cliks it.

View 8 Replies View Related

Deleting Content Of Unlocked Cells?

Mar 21, 2014

I have a spreadsheet that has mostly locked cells but randomly placed unlocked cells.

The sheet is completed, often saved and printed. We then go back into it and use it for another customer calculation and need to ensure all previous entries are removed. With the locked cells you can't simply highlight an area to clear so I am wondering if there is a way to clear the unlocked cells in one step other than entering on each on to clear.

Would there be any way that if we cleared the first cell the others would clear?

View 4 Replies View Related

Looping Through And Deleting Rows If Cells Contain A Value

Apr 8, 2014

I have a normally easy task that is causing me some grief. In column f of my data I either have a number or this "____________". My goal is to delete the entire row if that line is present but my code is not finding that value in my range.

I am wondering is this some format value or something, but it shows up in the formula line as a line. Doesn't appear to be an underline, but I could be wrong. Below is my code.

[Code] ........

View 9 Replies View Related

Deleting Comments In Blank Cells

May 6, 2009

Is there a way to delete comments in a selected range of blank cells?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved