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 RepliesI 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
How do I count the number of cells that have a value greater than 0 in a range of cells?
View 2 Replies View RelatedI want to create a macro that will find a text string called "Season Average" in the entire workbook and delete a range of cells above it 4 cells longs starting right above the text and then going left.
Example is if "Season Average" is in cell D11, than I want the cells D10, C10, B10 & A10 Deleted and the cells to shift up
This happens throughout the entire workbook and multiple times on each work sheet in different spots not consistently same cells.
I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.
View 2 Replies View RelatedI'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".
View 12 Replies View RelatedWorking on some code that will find the Column Name "CL" and delete the entire column. Here is what I have so far...it does not give me a error when it compiles, but for some reason the statement in red is being ignored. I tried switching Range with Columns, but that did not work.
Private sub coldelete ()
Dim Rng2 as Range
shUCLR = sheets("Unit Cost").usedrange.rows.count
Set Rng2 = Sheets("Unit Cost").Range("A1:BF1")
For Each d In Rng2
If d = "CL" Then
colnum = d.Column
colletter = Left(Sheets("Unit Cost").Cells(1, colnum).Address(False, False), (colnum)
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
View 9 Replies View RelatedI think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells
Sub Analysis()
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer
TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row
For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................
I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.
I've tried this =IF(('LM fundamentals'!C82>=1), ('LM fundamentals'!C83>=1), ('LM fundamentals'!C84>=1), ('LM fundamentals'!C85>=1), ('LM fundamentals'!C86>=1), 'Order Information'!$B$21!=(1* 'LMfundamentals'!$H$5!))
I even tried a vlookup, having given the cell range in Tab 1 a name, etc. But I have little skill with logic statements, apparently.
I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE
=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)
When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct
IF Func Arg.jpg
When I view the cell in the spreadsheet, system displays #VALUE!
When I view Evaluate Formula window, it seems Excel doesn't like the text.
IF Form Eval.jpg
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?
View 5 Replies View RelatedI'm trying to remove gif, jpg, and xls objects from a specific range and I can't seem to figure out how to do it. I can remove all objects from the sheet or only the gif and jpg files from a range. Here is the code I've been playing with:
Sub Clear_Sigs()
Dim Sh As Shape
With Worksheets("Sheet1")
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, .Range("A26:E32")) Is Nothing Then
If Sh.Type = msoPicture Then Sh.Delete
End If
Next Sh
End With
End Sub
I know this code says to delete only pictures and I've tried tweeking it to do all objects but it doesn't work for just the range I want. Any help is appreciated.
Thanks,
Amy
I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A). I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B. So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.
A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657
So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.
How can i delete a range of cell in another worksheet 2? for example, i have a data in B1:F1 in sheet 2 and i want to put a button in sheet 1 which will delete the data in B1:F1 in sheet 2.
View 1 Replies View RelatedAny easy way of finding out if deleting a particular sheet / range or selection will mess up any references elsewhere in the workbook?
View 1 Replies View RelatedI'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)
Sub CreateSheets()
'Written by Barrie Davidson
For Each c In Sheets("FilterList").Range("b2:b74")
Sheets.Add
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub
That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops.
For Each c In Sheets("FilterList").Range("b2:b74")
Application.DisplayAlerts = False
Worksheets(c.Value).Delete
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub
By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying.
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies View RelatedI have a column that needs to be text format, but it needs to include only digits (0-9), no letters, no special characters. Also all cells need to have 7 digits.
I am trying to do a couple of things:
1 : Hightlight the cells that contain Strings with Non-Digits characters
2 : Highlight the cells with less than 7 characters (I have the code, it is below)
I have tried many ways to get #1 but I am stuck.
Help please.
This highlights the cells with less than 7 characters:
Sub StringLength3() ' IT WORKS!
Dim strTest As String
Dim i As Integer
Dim Cell As Range
'Select Range
Worksheets("tres").Activate
Worksheets("tres").Range("A2", Range("a65536").End(xlUp)).Name = "RangeA"
Range("RangeA").Select
For Each Rangea In Selection
i = Len(Rangea)
If i 7 Then
Rangea.Cells.Interior.ColorIndex = 7
End If
Next Rangea
End Sub
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?
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?
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 RelatedI 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.
The delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.
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 RelatedIs 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 RelatedI 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
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.