Looking for some code to clear cells which are not highlighted
i.e. clear everything from row 2 downwards (Columns I:P) but exclude cells which are highlighted (not conditional formatting)
I have some cells which are highlighted in column A.
I need VB Code to copy the whole highlighted row in Sheet2 as shown. Once it is copied then clear the highlighted cells in column A of Sheet1.
I have two separate workbooks, the first is called Job test and is to be used as a template for quoting jobs, the second is called Fixtures and is a database of fixtures that are organized in table. I keep them separate as multiple jobs will use the Fixtures DB workbook and I want to be able to update it and add new fixtures in one area. In order to facilitate this I have a macro that opens the Fixtures DB workbook anytime that the Job test workbook is opened. In the Job test workbook I have multiple dropdowns that I hope to make dependent or cascading by means of filtering the Fixtures DB workbook. The issue I have run into is with the following code.
Code:
Private Sub CLightType_Change()'Filters LampType
Workbooks("Fixtures.xlsm").Sheets("CLampType").Activate
Workbooks("Fixtures.xlsm").Sheets("CLampType").Cells.Clear
Workbooks("Fixtures.xlsm").Sheets("Fixtures").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Workbooks("Job test.xlsm").Sheets("Criteria").Range("A1:A2"), CopyToRange _
:=Range("A1"), Unique:=False
[Code] ......
What this code hopes to achieve is that when I change the value in the LightType dropdown, the Fixtures DB workbook will automatically filter the data once to a different sheet(CLampType), then get only unique values for LampType in column O. I have set up a dynamic range for column O so as to populate my next combobox, LampType.
This should all be fairly simple and straightforward, however I am running into "Run-time error '1004': Clear method of Range class failed." when I try to execute the line to clear the worksheet, and also have an error when I try to filter the data via macro. The strange part is all of this can be done manually without a problem, and moreover I have tried recording the process and using the recorded version. Even stranger yet is that when I add an "on error resume next" before everything, the code works fine but keeps looping and acts finicky(I don't want to simply resort to this as a solution). I have also tried setting this macro up inside the Fixtures workbook instead and calling it from the combobox change, to no avail.
This seems to take much longer than I would have expected. Is there anything I can do to speed it up, or is this just something to live with?
View 13 Replies View RelatedI'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.
It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.
So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.
I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.
It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.
I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.
I am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this.
I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.
I am trying to code a command button to clear a range of cells if any of them contain a numeric value. For example. If any cells of cell range A1:C10 contain a numeric value then they would be cleared. Not all the cells in the range but only thouse contaiing numeric values. I have tried various methods with not-so-good results.
View 9 Replies View RelatedI have two rows of numbers, say D1 to F1 and D2 to F2. Row D2 to F2 will always have lower values.
The range below it is D3 to F100. I want the range to clear the contents of the cells in the range where:
a) the cell is > the x1 cell above it
OR b) the cell is < the x2 cell above it
If it is = or between the two cells, the value stays.
Example:
If D1 thru F1 is 4, 5, 7 and D2 thru F2 is 2, 4, 3, then:
D3 is 5, it is cleared
E3 is 4, it stays
F3 is 5, it stays
D4 is 2, it stays
E4 is 1, it is cleared
F4 is 9, it is cleared
Etc
Basically, the cell in the range looks up its own column, compares itself to x1 and x2 in that column. If it's greater than or less than, then it clears the contents, if not, the value in the cell stays.
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents
from another thread (which is excellent!)
- SHG's recommendation of using a named range, for example:
Range("Inputs").ClearContents
Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents
I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
I have the following code used to fill a listbox control .. and I added some conditions to the code in order to give special authorities to specific users depending on their position and unit? Authority, UserPositon and UserUnit are functions give the user's position, unit and his authority. Can we summarize the red highlighted parts of the code because I am going to use these parts in many other forms of my project?
View 3 Replies View RelatedVBA code that can highlight all columns except ones highlighted?
View 1 Replies View RelatedI have a worksheet (with if statements) that I throw numbers from a report into and it uses those if statements to calculate the number of items and money earned. The problem is that sometimes employees use different key words for their items so the formulas dont pick them up and this skews their results for the month. Also it's hard to see where someone messed so I'm trying to write a code that reads each row for a given selection and calculates the sum and if that sum is 0 then we can look for where the employee made an error. This is what I have:
Dim RngToSum As Range
Set RngToSum = Selection.Rows
For Each Row In RngToSum
If Application.WorksheetFunction.Sum(RngToSum) = "0" Then
MsgBox "Lets play find the error"
Selection.Rows.Interior.ColorIndex = 3 'red
Else Application.WorksheetFunction.Sum(RngToSum) 0 Then
MsgBox "Congrats there's no errors"
End If
End Sub
The problem with the code is that I want it to read every line and if there's no errors then have it say "congrats no errors" after ALL rows have been checked because right now it pulls a msgbox per row and I don't want to go through hundreds of no error msgboxes. However, if even just one line has an error then have a msgbox come up.
Also there's something weird where this code wont pick up an error for a row if i highlight two rows (one row without an error and one with) but if i highlight just rows of zeros then it'll pick it up.
I need my macro to select the range that I have highlighted in a worksheet.
For every run of the macro the range will be different.
Can use a highlighted range in a macro or a formula.
Just really want to be able to highlight a range of cells in column B and maybe click a button to launch a userform with a label on. The label would have the average of the highlighted cells.
Does this sound possible?
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
Is it possible to sum only cells that are highlighted "Blue"
View 2 Replies View RelatedI have manually highlighted a large magnitude of cells (I would have tried some sort of automation but there isn't really a pattern unfortunately). The cells that I did not highlight are useless to me, and I would like to get rid of them. Is there any way to delete all cells that are not highlighted on a given sheet?
View 2 Replies View Relatedcreating a macro, which can, for each name's values calculate average for non-highlighted cells in the value column.
Currently I have to manually do this for each row -- [=(B12+B14+B15+B16+B17)/5], in column S. hence I am seeking a macro approach, which would make it easier to take into account larger dataset.
I have a attached example file, with sample data, which shows row1, representing the actual columns of the data layout. AVERAGE.xlsx
The desired output I am looking for, is to be able to execute the averages in column S, for each name's value (non- highlighted cells) using a macro, if possible.
I know I've written a formula like this ages ago, but I can't seem to recall.
I want to write a formula that says to look at a table of cells, and to sum all cells that are highlighted a certain colour.
I have a question concerning highlighted cells. If I am using highlighted cells to show different headings and sections of a model, how could I change the color of a particular header and apply to all of them at once?
I.e. I have a header that is blue and sub headings that are yellow. I have this for 100 different tables. Is there a quick way to say, find cells similar to my blue header, and apply changes to all of them? I'd prefer not to ctrl + click each header to change the color.
I use this code to select highlighted cells within a column
Code:
For Each CELL In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If CELL.Interior.ColorIndex xlNone Then
CELL.Select
End If
Next
the thing is that it is working from bottom to top, is there a way to make it work from top to bottom?
I have a column with data that through code certain cells are highlighted. I want to count the number of cells that are a certain color, such as how many are red,how many are yellow,etc..
View 5 Replies View Relatedi want to take the count for different colour cells in the excel. ex, if green colour in any particular cell, it has to take only green colour count & same like yellow & so on...
View 5 Replies View RelatedWould like to write an IF statement where it reads if any cells in a column are highlighted and gives the sum of those highlighted while ignoring the ones unhighlighted.
Is there any way to do this or an alternate method that would possibly work?
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up...
J F M Ap M....
acct 1
acct 2
acct 3
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
[code]......
I have a dataset with over a 1000 rows. I just pulled one row in my dataset as an example. My formula above finds the last cell with a value in the 2 row, finds the year associated with it in the first row, and pulls the year from the first row. What I am trying to find the last value highlighted red and pulling the year associated with it in the first row.
I have data in columns that I want to create charts for. However, this data is dynamic and the number of cells where there is data varies. The rest of the cells that don't have numbers have 0s, however if I applied a macro to the whole column all of the zeros would show up in the resulting charts. How can I create a macro where I can highlight just the cells (the ones with non-zero numbers) that I want a chart to be generated for. Or is there a way to ignore zeros/blanks completely in the macro?
Example: here are two columns with data. I want to make a macro that creates a chart by just highlighting the actual numbers and ignoring the zeros.
Mexico
0.171896
USA
0.132952
Germany
0.113626
Colombia
0.096198
[code]....
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....
J F M Ap M....
acct 1
acct 2
acct 3
I want to change (via conditional formatting) the background colour of cell H64 and K72 when I select / highlighted / activate (i.e. just left click in the cell) cell C66.
I don't seem to be able to find in the formulae something to indicate if C66 is selected / highlighted /activated.